[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)

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

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)

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


SCOTT@test01p> insert into bowie (id, text) select rownum+2, to_char(rownum)||'BOWIE' from dual connect by level<=99998;

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")<='42BOWIE' AND

              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")<='42BOWIE' AND

              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")<='42BOWIE'

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

   2 - access("BOWIE"."SYS_NC00004$">='429BO' AND "BOWIE"."SYS_NC00004$"<='42BOW')

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

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")<='aaaaaaaaaaaaaaaaaaaaaa'))

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

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

ITPub博客

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

最新文章