[20130916]12c Indexing Extended Data Types and index.txt

[20130916]12c Indexing Extended Data Types and index.txt

http://richardfoote.wordpress.com/2013/09/12/12c-indexing-extended-data-types-part-i-a-big-hurt/

参考以上链接，做一些测试：

1.测试环境: 

SCOTT@test01p> @ver
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
SCOTT@test01p> create table bowie (id number, text varchar2(32000));
Table created.
SCOTT@test01p> create index bowie_text_i on bowie(text);
create index bowie_text_i on bowie(text)
                             *
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded

--超长字段无法在上建议索引。

12c提供standard_hash函数，可以实现其上建立函数索引。

2.插入一些数据，便于测试： 

SCOTT@test01p> insert into bowie (id, text) values (1, lpad('a',1110,'a'));
1 row created.
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> select length(text) from bowie;
LENGTH(TEXT)
------------
        1110

SCOTT@test01p> insert into bowie (id, text) select 2, text||text||text||text||text||text||text||text||text||text from bowie;
1 row created.
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> select length(text) from bowie;
LENGTH(TEXT)
------------
        1110
       11100
SCOTT@test01p> insert into bowie (id, text) select rownum+2, to_char(rownum)||'BOWIE' from dual connect by level
99998 rows created.
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'BOWIE', method_opt=>'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.
3.建立函数索引:
SCOTT@test01p> create index bowie_hash_text_i on bowie(standard_hash(text));
Index created.
SCOTT@test01p> select index_name, num_rows, leaf_blocks from dba_indexes where index_name = 'BOWIE_HASH_TEXT_I';
INDEX_NAME           NUM_ROWS LEAF_BLOCKS
------------------ ---------- -----------
BOWIE_HASH_TEXT_I      100000         447

4.查询看看情况： 

SCOTT@test01p> column text format a100
SCOTT@test01p> select * from bowie where text = '42BOWIE';
        ID TEXT
---------- ---------------------------------------------------
        44 42BOWIE
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  3uz6tby2rv7bh, child number 1
-------------------------------------
select * from bowie where text = '42BOWIE'
Plan hash value: 1900956348
---------------------------------------------------------------------------------------
| Id  | Operation                           | Name              | E-Rows | Cost (%CPU)|
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |        |     3 (100)|
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE             |      1 |     3   (0)|
|*  2 |   INDEX RANGE SCAN                  | BOWIE_HASH_TEXT_I |      1 |     2   (0)|
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter((INTERNAL_FUNCTION("TEXT") AND
              INTERNAL_FUNCTION("TEXT")='42BOWIE'))
   2 - access("BOWIE"."SYS_NC00003$"=HEXTORAW('A2C98939EDB479BC3EB0CDC560DDCD15
              75D47F62'))

--可以发现可以使用这个函数索引。

5.但是这种情况存在一些限制，做like 或者between时，不能使用该函数索引：

SCOTT@test01p> select * from bowie where text like 'aaaaaaaaaaaaaaaaaaaaaa%';
...
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID  01fn3bq946un9, child number 0
-------------------------------------
select * from bowie where text like 'aaaaaaaaaaaaaaaaaaaaaa%'
Plan hash value: 1845943507
---------------------------------------------------------
| Id  | Operation         | Name  | E-Rows | Cost (%CPU)|
---------------------------------------------------------
|   0 | SELECT STATEMENT  |       |        |   208 (100)|
|*  1 |  TABLE ACCESS FULL| BOWIE |      1 |   208   (2)|
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter((INTERNAL_FUNCTION("TEXT") AND INTERNAL_FUNCTION("TEXT")
              LIKE 'aaaaaaaaaaaaaaaaaaaaaa%'))
SCOTT@test01p> select * from bowie where text between '4299BOWIE' and '42BOWIE';
        ID TEXT
---------- ---------------------------------------------------------------------
        44 42BOWIE
       431 429BOWIE
      4301 4299BOWIE
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  1uk9ud7fq8fdx, child number 0
-------------------------------------
select * from bowie where text between '4299BOWIE' and '42BOWIE'
Plan hash value: 1845943507
---------------------------------------------------------
| Id  | Operation         | Name  | E-Rows | Cost (%CPU)|
---------------------------------------------------------
|   0 | SELECT STATEMENT  |       |        |   208 (100)|
|*  1 |  TABLE ACCESS FULL| BOWIE |      2 |   208   (2)|
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter((INTERNAL_FUNCTION("TEXT") AND
              INTERNAL_FUNCTION("TEXT")
              INTERNAL_FUNCTION("TEXT")>='4299BOWIE'))


SCOTT@test01p> select * from bowie where text > 'zzz';


no rows selected


SCOTT@test01p> @dpc '' ''


PLAN_TABLE_OUTPUT


---------------------------------------------------------------


SQL_ID  39wfprrkz66td, child number 0


-------------------------------------


select * from bowie where text > 'zzz'


Plan hash value: 1845943507


---------------------------------------------------------


| Id  | Operation         | Name  | E-Rows | Cost (%CPU)|


---------------------------------------------------------


|   0 | SELECT STATEMENT  |       |        |   208 (100)|


|*  1 |  TABLE ACCESS FULL| BOWIE |      1 |   208   (2)|


---------------------------------------------------------


Predicate Information (identified by operation id):


---------------------------------------------------


   1 - filter((INTERNAL_FUNCTION("TEXT") AND


              INTERNAL_FUNCTION("TEXT")>'zzz'))


6.很明显，无法在该列上建议唯一约束。

SCOTT@test01p> alter table bowie add constraint bowie_text_unq unique (text);


alter table bowie add constraint bowie_text_unq unique (text)


*


ERROR at line 1:


ORA-01450: maximum key length (6398) exceeded


--建立以上约束，需要在该列上建立索引，超长无法建立。同样可以变通的方法建立：


SCOTT@test01p> drop index bowie_hash_text_i;


Index dropped.


SCOTT@test01p> alter table bowie add (text_hash as (standard_hash(text)));


Table altered.


SCOTT@test01p> alter table bowie add constraint bowie_text_unq unique (text_hash);


Table altered.


SCOTT@test01p> insert into bowie (id, text) values (1000001, '42BOWIE');


insert into bowie (id, text) values (1000001, '42BOWIE')


*


ERROR at line 1:


ORA-00001: unique constraint (SCOTT.BOWIE_TEXT_UNQ) violated


--再重复以上查询：

SCOTT@test01p> select * from bowie where text = '42BOWIE';


        ID TEXT     TEXT_HASH


---------- -------- ----------------------------------------


        44 42BOWIE  A2C98939EDB479BC3EB0CDC560DDCD1575D47F62


SCOTT@test01p> @dpc '' ''


PLAN_TABLE_OUTPUT


--------------------------------------------------------------------------------


SQL_ID  3uz6tby2rv7bh, child number 1


-------------------------------------


select * from bowie where text = '42BOWIE'


Plan hash value: 2691947611


----------------------------------------------------------------------------


| Id  | Operation                   | Name           | E-Rows | Cost (%CPU)|


----------------------------------------------------------------------------


|   0 | SELECT STATEMENT            |                |        |     2 (100)|


|*  1 |  TABLE ACCESS BY INDEX ROWID| BOWIE          |      1 |     2   (0)|


|*  2 |   INDEX UNIQUE SCAN         | BOWIE_TEXT_UNQ |      1 |     1   (0)|


----------------------------------------------------------------------------


Predicate Information (identified by operation id):


---------------------------------------------------


   1 - filter((INTERNAL_FUNCTION("TEXT") AND


              INTERNAL_FUNCTION("TEXT")='42BOWIE'))


   2 - access("BOWIE"."TEXT_HASH"=HEXTORAW('A2C98939EDB479BC3EB0CDC560DD


              CD1575D47F62'))


--但是如果做范围查询，结果如何应该同上是选择全表扫描。

SCOTT@test01p> select * from bowie where text between '429BOWIE' and '42BOWIE';


        ID TEXT      TEXT_HASH


---------- --------- ----------------------------------------


        44 42BOWIE   A2C98939EDB479BC3EB0CDC560DDCD1575D47F62


       431 429BOWIE  A7E2B59E1429DB4964225E7A98A19998BC3D2AFD


SCOTT@test01p> @dpc '' ''


PLAN_TABLE_OUTPUT


-------------------------------------


SQL_ID  143xd3cu22ja1, child number 0


-------------------------------------


select * from bowie where text between '429BOWIE' and '42BOWIE'


Plan hash value: 1845943507


---------------------------------------------------------


| Id  | Operation         | Name  | E-Rows | Cost (%CPU)|


---------------------------------------------------------


|   0 | SELECT STATEMENT  |       |        |   208 (100)|


|*  1 |  TABLE ACCESS FULL| BOWIE |      2 |   208   (2)|


---------------------------------------------------------


Predicate Information (identified by operation id):


---------------------------------------------------


   1 - filter((INTERNAL_FUNCTION("TEXT") AND


              INTERNAL_FUNCTION("TEXT")


              INTERNAL_FUNCTION("TEXT")>='429BOWIE'))


7.如果做范围查询如何显示呢？期待作者的第2部分new extended columns in Part II.
自己也想一下，实际上作者的例子，text组成前面数字+BOWIE。前面5位具有很好的选择性。通过函数substr建立函数应该也可以，
自己测试看看。


SCOTT@test01p> create index i_bowie_text_substr_1_5 on bowie (substr(text,1,5));


Index created.


SCOTT@test01p> @dpc '' ''


PLAN_TABLE_OUTPUT


-------------------------------------


SQL_ID  143xd3cu22ja1, child number 0


-------------------------------------


select * from bowie where text between '429BOWIE' and '42BOWIE'


Plan hash value: 1199225668


---------------------------------------------------------------------------------------------


| Id  | Operation                           | Name                    | E-Rows | Cost (%CPU)|


---------------------------------------------------------------------------------------------


|   0 | SELECT STATEMENT                    |                         |        |    92 (100)|


|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE                   |      2 |    92   (0)|


|*  2 |   INDEX RANGE SCAN                  | I_BOWIE_TEXT_SUBSTR_1_5 |    450 |     3   (0)|


---------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):


---------------------------------------------------


   1 - filter((INTERNAL_FUNCTION("TEXT") AND INTERNAL_FUNCTION("TEXT")


              AND INTERNAL_FUNCTION("TEXT")>='429BOWIE'))


   2 - access("BOWIE"."SYS_NC00004$">='429BO' AND "BOWIE"."SYS_NC00004$"

--可以发现使用我建立的索引，看看使用大于的情况呢？


SCOTT@test01p> select * from bowie where text > 'zzz';


no rows selected


SCOTT@test01p> @dpc '' ''


PLAN_TABLE_OUTPUT


-------------------------------------


SQL_ID  39wfprrkz66td, child number 0


-------------------------------------


select * from bowie where text > 'zzz'


Plan hash value: 1199225668


---------------------------------------------------------------------------------------------


| Id  | Operation                           | Name                    | E-Rows | Cost (%CPU)|


---------------------------------------------------------------------------------------------


|   0 | SELECT STATEMENT                    |                         |        |   181 (100)|


|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE                   |      1 |   181   (0)|


|*  2 |   INDEX RANGE SCAN                  | I_BOWIE_TEXT_SUBSTR_1_5 |    900 |     4   (0)|


---------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):


---------------------------------------------------


   1 - filter((INTERNAL_FUNCTION("TEXT") AND INTERNAL_FUNCTION("TEXT")>'zzz'))


   2 - access("BOWIE"."SYS_NC00004$">='zzz')


--依旧可以使用我建立的函数索引，但是使用like情况如何呢？


SCOTT@test01p> @dpc '' ''


PLAN_TABLE_OUTPUT


-------------------------------------


SQL_ID  1mq1xczjrz3uw, child number 0


-------------------------------------


select * from bowie where text like 'aaaaaaaaaaaaaaaaaaaaaa%'


Plan hash value: 1845943507


---------------------------------------------------------


| Id  | Operation         | Name  | E-Rows | Cost (%CPU)|


---------------------------------------------------------


|   0 | SELECT STATEMENT  |       |        |   208 (100)|


|*  1 |  TABLE ACCESS FULL| BOWIE |      1 |   208   (2)|


---------------------------------------------------------


Predicate Information (identified by operation id):


---------------------------------------------------


   1 - filter((INTERNAL_FUNCTION("TEXT") AND INTERNAL_FUNCTION("TEXT")


              LIKE 'aaaaaaaaaaaaaaaaaaaaaa%'))


--like 无效，不知道作者还有什么好方法，期待作者的第2部分，也许有更好的例子。
--改写为范围查询也许是一个替换like的方法，但是不适合'%aaaa%'的情况。


SCOTT@test01p>  select * from bowie where text between 'aaaaaaaaaaaaaaaaaaaaaa' and 'aaaaaaaaaaaaaaaaaaaaaa'||chr(255);


SCOTT@test01p> @dpc '' ''


PLAN_TABLE_OUTPUT


-------------------------------------


SQL_ID  bkunhv8x64k0a, child number 1


-------------------------------------


 select * from bowie where text between 'aaaaaaaaaaaaaaaaaaaaaa' and


'aaaaaaaaaaaaaaaaaaaaaa'||chr(255)


Plan hash value: 1199225668


---------------------------------------------------------------------------------------------


| Id  | Operation                           | Name                    | E-Rows | Cost (%CPU)|


---------------------------------------------------------------------------------------------


|   0 | SELECT STATEMENT                    |                         |        |     2 (100)|


|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE                   |      1 |     2   (0)|


|*  2 |   INDEX RANGE SCAN                  | I_BOWIE_TEXT_SUBSTR_1_5 |      2 |     1   (0)|


---------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):


---------------------------------------------------


   1 - filter((INTERNAL_FUNCTION("TEXT") AND


              INTERNAL_FUNCTION("TEXT")>='aaaaaaaaaaaaaaaaaaaaaa' AND


              INTERNAL_FUNCTION("TEXT")


   2 - access("BOWIE"."SYS_NC00004$"='aaaaa')


--总结：
--BTW:如果字符字段很长，使用substr函数取前面选择性很强的几位，建立函数索引，有时候不失为一个方法，这样可以减少索引大小，
--这种方法在10G，11G使用同样有效。
--12C extended columns中提供的standard_hash函数，作为等值查询，不失为一个很好的选择。
				


				
时间： 2024-07-29 11:50:55

		
		


		


	

	
	

		


		
[20130916]12c Indexing Extended Data Types and index.txt的相关文章


								

		

			

                【12c】扩展数据类型（Extended Data Types）-- MAX_STRING_SIZE
			

		

		

									

				[12c]扩展数据类型(Extended Data Types)-- MAX_STRING_SIZE 在12c中,与早期版本相比,诸如VARCHAR2, NAVARCHAR2以及 RAW这些数据类型的大小会从4K以及2K字节扩展至32K字节.只要可能,扩展字符的大小会降低对LOB 数据类型的使用.为了启用扩展字符大小,你必须将MAX_STRING_SIZE的初始数据库参数设置为EXTENDED. 要使用扩展字符类型需要执行以下过程:  1.关闭数据库  2.以升级模式重启数据库 3.更改参数:			

		

	

				

		

			

                [20131116]12c的EXTENDED VARCHAR2与CLOB字段.txt
			

		

		

									

				[20131116]12c的EXTENDED VARCHAR2与CLOB字段.txt oracle 12c以前如果字符串长度超过4000,必须使用blob或者clob类型.12c开始支持超过4000的字符串长度,提高一些应用的灵活性,达到32K,避免一些字段定义为clob,blob类型,提高处理能力. 要支持这个特性,要执行 @?/rdbms/admin/utl32k.sql升级步骤.如果存在pdb库,还必须升级PDB$SEED,以及PDB数据库.参考链接:http://space.itpub.			

		

	

				

		

			

                [20131116]12c的EXTENDED VARCHAR2与隐含参数_scalar_type_lob_storage_threshold.txt
			

		

		

									

				[20131116]12c的EXTENDED VARCHAR2与隐含参数_scalar_type_lob_storage_threshold.txt 参考链接:http://space.itpub.net/267265/viewspace-776806/ google查询了一些资料: 发现:SYS@test01p> @hide _scalar_type_lob_storage_threshold;NAME                                     DESCRIPTI			

		

	

				

		

			

                [20131116]12c的EXTENDED VARCHAR2与_scalar_type_lob_storage_threshold之2.txt
			

		

		

									

				[20131116]12c的EXTENDED VARCHAR2与_scalar_type_lob_storage_threshold之2.txt 参考链接:http://space.itpub.net/267265/viewspace-776807/ google查询了一些资料: 发现:SYS@test01p> @hide _scalar_type_lob_storage_threshold;NAME                                     DESCRIPTION			

		

	

				

		

			

                关于powerdesigner中的data types说明
			

		

		

									

				原文:关于powerdesigner中的data types说明 这一堆的数据类型看着真是头大,弄个表格对照一下. Numeric data types Standard datatype DBMS-specific physical data type Content Length Integer int / INTEGER 32-bit integer - Short Integer smallint / SMALLINT 16-bit integer - Long Integer int			

		

	

				

		

			

                [20130918]12C表统计信息的在线收集.txt
			

		

		

									

				[20130918]12C表统计信息的在线收集.txt 在12c以前,如果使用ctas建立表,并不会自动收集表的统计信息,12c开始CTAS和IAS操作会收集统计信息.但是不收集直方图信息.以及索引统计. 摘要:twp-bp-for-stats-gather-12c-1967354.pdfBest Practices for Gathering Optimizer Statistics with Oracle Database 12c P16-17页: Online statistics gat			

		

	

				

		

			

                [20160220]12c视图一些字段显示128列.txt
			

		

		

									

				[20160220]12c视图一些字段显示128列.txt --今天在家里做一些测试时,无意中发现视图显示字段名index_name占用很大的宽度,仔细检查才发现视图里面的显示长度已经是128列. SYS@test> @ver1 PORT_STRING          VERSION     BANNER                                                                        CON_ID -----------------			

		

	

				

		

			

                [20130915]12c新特性 varchar2支持32K长度.txt
			

		

		

									

				[20130915]12c新特性 varchar2支持32K长度.txt oracle 12c以前如果字符串长度超过4000,必须使用blob或者clob类型.12c开始支持超过4000的字符串长度,提高一些应用的灵活性,达到32K,避免一些字段定义为clob,blob类型,提高处理能力. 但是12c默认的方式不支持大于4000的字符串长度,必须经过一些步骤升级完成,自己测试如下: SYS@test> @ver BANNER                                    			

		

	

				

		

			

                [20130803]ORACLE 12C TOP N SQL实现分页功能.txt
			

		

		

									

				[20130803]ORACLE 12C TOP N SQL实现分页功能.txt 参考链接:http://www.xifenfei.com/4257.html SQL> @verBANNER                                                                               CON_ID----------------------------------------------------------------------