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

lfree發表於2013-09-17
Index
[20130916]12c Indexing Extended Data Types and index.txt




參考以上鍊接，做一些測試：

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;
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部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-772856/，如需轉載，請註明出處，否則將追究法律責任。

相關文章