結合インデックスのテスト
テストの背景:生産データベースの会員表と製品表の中で3つのCLOBフィールド(これらのフィールドは主に会員と製品の説明情報を保存するために使用する)が存在して、データ量の増加に従って、全表スキャンの時、I/Oは深刻なことを待って、そこで分表を思い付きます!
質問:この2つの大きなテーブルに加えられたインデックスは全部で35個あり、そのうち会員テーブルのインデックス数は17個、製品テーブルのインデックス数は18個(LOBフィールドを含まない35個のインデックス)であり、これらのインデックスの大部分は組合せインデックスであり、関数インデックスが存在する(関数インデックスはいずれも組合せインデックスの先頭列でdescソートされる)
インデックス最適化テスト:テストライブラリで最適化テストを行い、元の会員表をtestで置き換え、ユーザーschemaをhrで置き換え、データベースバージョンは11.2.0.3 32 bit on widows 2003 enterprise edition
一:テーブルの作成、プライマリ・キー制約の追加、インデックスの組合せ1
二:表の統計情報を収集して、statusフィールドの空の値に対して値を割り当てて、statusフィールドは会員の状態で、1は正常な状態で、その他の値はすべて異常な状態で、だから値の分布は傾きます
3:テスト証明結果セットが小さい場合、where文にプリアンブル列が含まれている場合、組合せインデックスが使用されます.
四:コンポジットインデックス2を構築し(コンポジットインデックス2のフィールドはコンポジットインデックス1と同じで、プリアンブル列がコールされているだけ)、registersourceフィールドの空の値に値を割り当て、テーブルの統計情報を収集する.(このフィールドはユーザの登録ソースを格納するために使用され、値分布も傾斜している場合があります)
五:registersourceフィールドのみがクエリーされ、結果セットが小さい場合、実行計画は組合せインデックス2を採用する
六:傾斜組合せ試験
テストの結果、cboは結果セットのサイズに基づいて最適な実行計画を決定することを示した.
7:コンポジット・インデックスとその他のフィールドの組み合わせの使用(テストの結果、where句にコンポジット・インデックスの先頭列が含まれ、結果セットが小さい場合、実行計画はコンポジット・インデックスを選択することを示します)
八:関数インデックステスト、orderno、membership.status、featuredの4つのフィールドは組み合わせインデックス3を構築し、ordernoフィールドはdescソートを行う.前の4つのフィールドに対して通常の組合せインデックス4を作成します.この方法は、関数インデックスを使用する必要があるかどうかをテストすることを意味します.
テスト結果から,関数インデックスを使用しない方が効果的であるとともに,これらの関数インデックスを削除することで,今後のshrink table操作が容易になる.
九:プリアンブル列を使用しない場合、コンビネーションインデックスは正常に使用できますか?前の関数インデックスのテスト中、where条件にはプリアンブル列は含まれていませんが、結果セットをソートするときにプリアンブル列を使用して、計画選択の組合せインデックスを実行するため、次のテストを行います.
テスト結果から見ると、11.2.0.3バージョンのデータベースは、クエリー文に組合せインデックスのフィールドが含まれ、結果セットが小さい場合、実行計画で組合せインデックスが選択されます.
10:10.2.0.1バージョンのoracleで同様のテストを行い、where句にコンビネーションインデックスの先頭列が表示されないことを発見した場合、実行計画はコンビネーションインデックスを選択しません.
(9番目と10番目のテストはちょっと疑問ですが、後でテストを続けます、refhttp://t.askmaclean.com/viewthread.php?tid=1384&pid=7258&page=1&extra=page%3D1)
以上のように、1:オンラインデータベースの組合せインデックスを最適化することができ、傾斜フィールドに対してプリアンブル列の組合せインデックスを作成することができ、非傾斜フィールドを使用してプリアンブル列を作成することができ、または同じインデックスを確立し、プリアンブル列を調整することができる.
2:対応する関数インデックスの取り消し
3:不要な結合インデックスを削除し、where句によく現れるフィールドに対して結合インデックスを作成します(たとえば、クエリ1にwhere句がA、B、C、Dの4つのフィールドが表示され、クエリ2にwhere句がA、B、Cの3つのフィールドが表示され、このクエリ文がよく表示されます.このエントリの下で、A、B、C、Dフィールドに対して結合インデックスを作成するだけで、2つのインデックスを作成する必要はありません).
4:インデックスのホットスポットの問題に注意して、前の3のシーンは同時に深刻な情況の下で、ホットスポットが現れるのが速いことを招く可能性があって、そのため需要によって適切に調整する必要があります
質問:この2つの大きなテーブルに加えられたインデックスは全部で35個あり、そのうち会員テーブルのインデックス数は17個、製品テーブルのインデックス数は18個(LOBフィールドを含まない35個のインデックス)であり、これらのインデックスの大部分は組合せインデックスであり、関数インデックスが存在する(関数インデックスはいずれも組合せインデックスの先頭列でdescソートされる)
インデックス最適化テスト:テストライブラリで最適化テストを行い、元の会員表をtestで置き換え、ユーザーschemaをhrで置き換え、データベースバージョンは11.2.0.3 32 bit on widows 2003 enterprise edition
一:テーブルの作成、プライマリ・キー制約の追加、インデックスの組合せ1
- SQL> create table t1 as select * from test;
-
- SQL> alter table t1 add constraint pk_t1_id primary key (id);
- Table altered.
-
- SQL> create index idx_t1_mul1 on t1(status,registersource);
- Index created.
二:表の統計情報を収集して、statusフィールドの空の値に対して値を割り当てて、statusフィールドは会員の状態で、1は正常な状態で、その他の値はすべて異常な状態で、だから値の分布は傾きます
- SQL> update t1 set status=0 where status is null;
- 4 rows updated.
-
- SQL> commit;
- Commit complete.
-
- SQL> exec dbms_stats.gather_table_stats(‘HR’,'T1',CASCADE=>true);
- PL/SQL procedure successfully completed.
-
- SQL> select status,count(*) from t1 group by status;
-
- STATUS COUNT(*)
- ---------- ----------
- 1 595612
- 2 1230
- 4 10
- 5 1
- 3 2746
- 0 26825
- 6 rows selected.
3:テスト証明結果セットが小さい場合、where文にプリアンブル列が含まれている場合、組合せインデックスが使用されます.
- SQL> select id,version from t1 where status=0;
-
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1745128362
- -------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
- -------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 26939 | 289K| 6051 (1)|00:01:13 |
- | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 26939 | 289K| 6051 (1)|00:01:13 |
- |* 2 | INDEX RANGE SCAN | IDX_T1_MUL1 | 26939 | | 93 (0)|00:00:02 |
- -------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("STATUS"=0)
四:コンポジットインデックス2を構築し(コンポジットインデックス2のフィールドはコンポジットインデックス1と同じで、プリアンブル列がコールされているだけ)、registersourceフィールドの空の値に値を割り当て、テーブルの統計情報を収集する.(このフィールドはユーザの登録ソースを格納するために使用され、値分布も傾斜している場合があります)
- SQL> create index idx_t1_mul2 on t1(registersource,status);
- Index created.
-
- SQL> select registersource,count(*) from t1 group by registersource;
-
- REGIST COUNT(*)
- ------ ----------
- 1 156
- 4
- 3 1689
- 6 4
- 0 23487
- 2 601084
- 6 rows selected.
-
- SQL> update t1 set registersource=6 where registersource is null;
- 4 rows updated
-
- SQL>commit；
- Commit complete.
-
- SQL> exec dbms_stats.gather_table_stats(‘HR’,'T1',CASCADE=>true);
- PL/SQL procedure successfully completed.
五:registersourceフィールドのみがクエリーされ、結果セットが小さい場合、実行計画は組合せインデックス2を採用する
- SQL> select * from t1 where registersource='1'; // registersource varchar2 ，
-
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 2744963562
- -------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
- -------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 227 | 287K| 49 (0)|00:00:01 |
- | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 227 | 287K| 49 (0)|00:00:01 |
- |* 2 | INDEX RANGE SCAN | IDX_T1_MUL2 | 227 | | 3 (0)|00:00:01 |
- ------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("REGISTERSOURCE"='1')
六:傾斜組合せ試験
- 1：
- SQL> select * from t1 where status=0 and registersource=0;
-
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1745128362
- -------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
- -------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1057 | 1337K| 280 (0)|00:00:04 |
- | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1057 | 1337K| 280 (0)|00:00:04 |
- |* 2 | INDEX RANGE SCAN | IDX_T1_MUL1 | 1057 | | 68 (0)|00:00:01 |
- -------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("STATUS"=0)
- filter(TO_NUMBER("REGISTERSOURCE")=0)
-
- SQL> select * from t1 where registersource=0 and status=0;
-
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1745128362
- -------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
- -------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1057 | 1337K| 280 (0)|00:00:04 |
- | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1057 | 1337K| 280 (0)|00:00:04 |
- |* 2 | INDEX RANGE SCAN | IDX_T1_MUL1 | 1057 | | 68 (0)|00:00:01 |
- -------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("STATUS"=0)
- filter(TO_NUMBER("REGISTERSOURCE")=0)
-
- 2:
- SQL> select * from t1 where status=0 and registersource=2;
-
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1745128362
- -------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
- -------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 28531 | 35M| 5776 (1)|00:01:10 |
- | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 28531 | 35M| 5776 (1)|00:01:10 |
- |* 2 | INDEX RANGE SCAN | IDX_T1_MUL1 | 28531 | | 68 (0)|00:00:01 |
- -------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("STATUS"=0)
- filter(TO_NUMBER("REGISTERSOURCE")=2)
-
- SQL> select * from t1 where registersource=2 and status=0;
-
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1745128362
- -------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 28531 | 35M| 5776 (1)|00:01:10 |
- | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 28531 | 35M| 5776 (1)|00:01:10 |
- |* 2 | INDEX RANGE SCAN | IDX_T1_MUL1 | 28531 | | 68 (0)|00:00:01 |
- -------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("STATUS"=0)
- filter(TO_NUMBER("REGISTERSOURCE")=2)
-
- 3:
- SQL> select * from t1 where status=1 and registersource=0;
-
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1106331959
- -------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
- -------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 21105 | 26M| 4659 (1)|00:00:56 |
- | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 21105 | 26M| 4659 (1)|00:00:56 |
- |* 2 | INDEX SKIP SCAN | IDX_T1_MUL2 | 21105 | | 437 (0)|00:00:06 |
- ------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("STATUS"=1)
- filter(TO_NUMBER("REGISTERSOURCE")=0 AND "STATUS"=1)
-
- SQL> select * from t1 where registersource=0 and status=1;
-
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1106331959
- -------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
- -------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 21105 | 26M| 4659 (1)|00:00:56 |
- | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 21105 | 26M| 4659 (1)|00:00:56 |
- |* 2 | INDEX SKIP SCAN | IDX_T1_MUL2 | 21105 | | 437 (0)|00:00:06 |
- -------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("STATUS"=1)
- filter(TO_NUMBER("REGISTERSOURCE")=0 AND "STATUS"=1)
-
- 4:
- SQL> select * from t1 where status=1 and registersource=2;
-
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 3617692013
- --------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 569K| 703M| 32570 (1)| 00:06:31 |
- |* 1 | TABLE ACCESS FULL| T1 | 569K| 703M| 32570 (1)| 00:06:31 |
- --------------------------------------------------------------------------
-
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter("STATUS"=1 AND TO_NUMBER("REGISTERSOURCE")=2)
-
- SQL> select * from t1 where registersource=2 and status=1;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 3617692013
- --------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 569K| 703M| 32570 (1)| 00:06:31 |
- |* 1 | TABLE ACCESS FULL| T1 | 569K| 703M| 32570 (1)| 00:06:31 |
- --------------------------------------------------------------------------
-
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter("STATUS"=1 AND TO_NUMBER("REGISTERSOURCE")=2)
テストの結果、cboは結果セットのサイズに基づいて最適な実行計画を決定することを示した.
7:コンポジット・インデックスとその他のフィールドの組み合わせの使用(テストの結果、where句にコンポジット・インデックスの先頭列が含まれ、結果セットが小さい場合、実行計画はコンポジット・インデックスを選択することを示します)
- SQL> select * from t1 where status=0 and createddate < sysdate;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1745128362
- -------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 29698 | 36M| 6009 (1)|00:01:13 |
- |* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 29698 | 36M| 6009 (1)|00:01:13 |
- |* 2 | INDEX RANGE SCAN | IDX_T1_MUL1 | 29698 | | 68 (0)|00:00:01 |
- -------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
-
- 1 - filter("CREATEDDATE"<SYSDATE@!)
- 2 - access("STATUS"=0)
-
- SQL> select * from t1 where registersource='0' and createddate < sysdate;
-
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 2744963562
- -------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 22302 | 27M| 4514 (1)|00:00:55 |
- |* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 22302 | 27M| 4514 (1)|00:00:55 |
- |* 2 | INDEX RANGE SCAN | IDX_T1_MUL2 | 22302 | | 52 (0)|00:00:01 |
- -------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter("CREATEDDATE"<SYSDATE@!)
- 2 - access("REGISTERSOURCE"='0')
八:関数インデックステスト、orderno、membership.status、featuredの4つのフィールドは組み合わせインデックス3を構築し、ordernoフィールドはdescソートを行う.前の4つのフィールドに対して通常の組合せインデックス4を作成します.この方法は、関数インデックスを使用する必要があるかどうかをテストすることを意味します.
- SQL> create index idx_t1_mul3 on t1(orderno desc,membership,status,featured);
- Index created.
-
- SQL> select * from t1 where membership=1 and status=0 order by orderno;
- no rows selected
-
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 687259109
- -------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
- -------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 5940 | 7517K| 3009 (1)|00:00:37 |
- | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 5940 | 7517K| 3009 (1)|00:00:37 |
- |* 2 | INDEX FULL SCAN DESCENDING| IDX_T1_MUL3 | 5940 | | 1825 (1)|00:00:22 |
- -------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
-
- 2 - access("MEMBERSHIP"=1 AND "STATUS"=0)
- filter("STATUS"=0 AND "MEMBERSHIP"=1)
-
- SQL> select * from t1 where membership=1 and status=0 order by orderno desc;
- no rows selected
-
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 607735922
- -------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
- -------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 5940 | 7517K| 3009 (1)|00:00:37 |
- | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 5940 | 7517K| 3009 (1)|00:00:37 |
- |* 2 | INDEX FULL SCAN | IDX_T1_MUL3 | 5940 | | 1825 (1)|00:00:22 |
- -------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
-
- 2 - access("MEMBERSHIP"=1 AND "STATUS"=0)
- filter("STATUS"=0 AND "MEMBERSHIP"=1)
-
- SQL> drop index idx_t1_mul3;
- Index dropped.
-
- SQL> create index idx_t1_mul4 on t1(orderno,membership,status,featured);
- Index created.
-
- SQL> select * from t1 where membership=1 and status=0 order by orderno;
- no rows selected
-
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 3155127807
- -------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
- -------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 5940 | 7517K| 1923 (1)|00:00:24 |
- | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 5940 | 7517K| 1923 (1)|00:00:24 |
- |* 2 | INDEX SKIP SCAN | IDX_T1_MUL4 | 5940 | | 739 (0)|00:00:09 |
- -------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("MEMBERSHIP"=1 AND "STATUS"=0)
- filter("STATUS"=0 AND "MEMBERSHIP"=1)
-
- SQL> select * from t1 where membership=1 and status=0 order by orderno desc;
- no rows selected
-
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 893632694
- -------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
- -------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 5940 | 7517K| 1923 (1)|00:00:24 |
- | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 5940 | 7517K| 1923 (1)|00:00:24 |
- |* 2 | INDEX SKIP SCAN DESCENDING| IDX_T1_MUL4 | 5940 | | 739 (0)|00:00:09 |
- -------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("MEMBERSHIP"=1 AND "STATUS"=0)
- filter("STATUS"=0 AND "MEMBERSHIP"=1)
テスト結果から,関数インデックスを使用しない方が効果的であるとともに,これらの関数インデックスを削除することで,今後のshrink table操作が容易になる.
九:プリアンブル列を使用しない場合、コンビネーションインデックスは正常に使用できますか?前の関数インデックスのテスト中、where条件にはプリアンブル列は含まれていませんが、結果セットをソートするときにプリアンブル列を使用して、計画選択の組合せインデックスを実行するため、次のテストを行います.
- SQL> create index idx_t1_mul5 on t1(password,signinid);
- Index created.
-
- SQL> select * from (select signinid,count(*) from t1 group by signinid) where rownum<10;
-
- SIGNINID COUNT(*)
- ------------------------------------------------------------ ----------
- 000000yu 1
- 0000410265269 1
- 00006789 1
- 00009746 1
- 000113 1
- 0001mwm 1
- 0002081 1
- 000317 1
- 00032156688 1
-
- 9 rows selected.
-
- SQL> select * from t1 where signinid='000000yu';
-
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 2246799375
- -------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
- -------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 1296 | 3706 (1)|00:00:45 |
- | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1296 | 3706 (1)|00:00:45 |
- |* 2 | INDEX SKIP SCAN | IDX_T1_MUL5 | 1 | | 3705 (1)|00:00:45 |
- -------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
-
- 2 - access("SIGNINID"='000000yu')
- filter("SIGNINID"='000000yu')
-
- SQL> select * from t1 where createddate < sysdate and signinid='000000yu';
-
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 2246799375
- -------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
- -------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 1296 | 3706 (1)|00:00:45 |
- |* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1296 | 3706 (1)|00:00:45 |
- |* 2 | INDEX SKIP SCAN | IDX_T1_MUL5 | 1 | | 3705 (1)|00:00:45 |
- -------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
-
- 1 - filter("CREATEDDATE"<SYSDATE@!)
- 2 - access("SIGNINID"='000000yu')
- filter("SIGNINID"='000000yu')
テスト結果から見ると、11.2.0.3バージョンのデータベースは、クエリー文に組合せインデックスのフィールドが含まれ、結果セットが小さい場合、実行計画で組合せインデックスが選択されます.
10:10.2.0.1バージョンのoracleで同様のテストを行い、where句にコンビネーションインデックスの先頭列が表示されないことを発見した場合、実行計画はコンビネーションインデックスを選択しません.
(9番目と10番目のテストはちょっと疑問ですが、後でテストを続けます、refhttp://t.askmaclean.com/viewthread.php?tid=1384&pid=7258&page=1&extra=page%3D1)
- SQL> create user test identified by test;
- User created.
-
- SQL> grant connect,resource,select_catalog_role to test;
- Grant succeeded.
-
- SQL> conn test/test
- Connected.
-
- SQL> create table t1 as select * from dba_objects;
- Table created.
-
- SQL> create table t1 as select * from dba_objects;
- Table created.
-
- SQL> desc t1;
- Name Null? Type
- ----------------------------------------- -------- ----------------------------
- OWNER VARCHAR2(30)
- OBJECT_NAME VARCHAR2(128)
- SUBOBJECT_NAME VARCHAR2(30)
- OBJECT_ID NUMBER
- DATA_OBJECT_ID NUMBER
- OBJECT_TYPE VARCHAR2(19)
- CREATED DATE
- LAST_DDL_TIME DATE
- TIMESTAMP VARCHAR2(19)
- STATUS VARCHAR2(7)
- TEMPORARY VARCHAR2(1)
- GENERATED VARCHAR2(1)
- SECONDARY VARCHAR2(1)
-
- SQL> create index i_t1_mul1 on t1(object_id,object_type);
- Index created.
-
- SQL> set autot traceonly exp
- SQL> select * from t1 where object_id < 100;
-
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1186876071
- -----------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -----------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 98 | 17346 | 4 (0)| 00:00:01 |
- | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 98 | 17346 | 4 (0)| 00:00:01 |
- |* 2 | INDEX RANGE SCAN | I_T1_MUL1 | 98 | | 2 (0)| 00:00:01 |
- -----------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("OBJECT_ID"<100)
-
- Note
- -----
- - dynamic sampling used for this statement
-
-
- SQL> select * from t1 where object_type='INDEX';
-
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 3617692013
- --------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 531 | 93987 | 159 (2)| 00:00:02 |
- |* 1 | TABLE ACCESS FULL| T1 | 531 | 93987 | 159 (2)| 00:00:02 |
- --------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter("OBJECT_TYPE"='INDEX')
-
- Note
- -----
- - dynamic sampling used for this statement
-
-
- SQL> set autot off
- SQL> select count(*) from t1;
-
- COUNT(*)
- ----------
- 50380
-
- SQL> select object_type,count(*) from t1 group by object_type;
-
- OBJECT_TYPE COUNT(*)
- ------------------- ----------
- CONSUMER GROUP 5
- INDEX PARTITION 276
- SEQUENCE 143
- QUEUE 27
- SCHEDULE 1
- TABLE PARTITION 128
- RULE 4
- JAVA DATA 306
- PROCEDURE 85
- OPERATOR 57
- LOB PARTITION 1
-
- OBJECT_TYPE COUNT(*)
- ------------------- ----------
- WINDOW 2
- LOB 566
- PACKAGE 848
- PACKAGE BODY 791
- LIBRARY 150
- RULE SET 19
- PROGRAM 12
- TYPE BODY 173
- CONTEXT 5
- JAVA RESOURCE 770
- XML SCHEMA 26
-
- OBJECT_TYPE COUNT(*)
- ------------------- ----------
- TRIGGER 171
- JOB CLASS 2
- UNDEFINED 6
- DIRECTORY 9
- DIMENSION 5
- MATERIALIZED VIEW 2
- TABLE 1636
- INDEX 1800
- SYNONYM 20026
- VIEW 3671
- FUNCTION 270
-
- OBJECT_TYPE COUNT(*)
- ------------------- ----------
- WINDOW GROUP 1
- JAVA CLASS 16417
- INDEXTYPE 10
- CLUSTER 10
- TYPE 1926
- RESOURCE PLAN 3
- EVALUATION CONTEXT 14
- JOB 6
- 41 rows selected.
-
- SQL> set autot traceonly exp
- SQL> select /*+index(i_t1_mul1)*/ * from t1 where object_type='INDEX';
-
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 3617692013
- --------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 531 | 93987 | 159 (2)| 00:00:02 |
- |* 1 | TABLE ACCESS FULL| T1 | 531 | 93987 | 159 (2)| 00:00:02 |
- --------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter("OBJECT_TYPE"='INDEX')
-
- Note
- -----
- - dynamic sampling used for this statement
以上のように、1:オンラインデータベースの組合せインデックスを最適化することができ、傾斜フィールドに対してプリアンブル列の組合せインデックスを作成することができ、非傾斜フィールドを使用してプリアンブル列を作成することができ、または同じインデックスを確立し、プリアンブル列を調整することができる.
2:対応する関数インデックスの取り消し
3:不要な結合インデックスを削除し、where句によく現れるフィールドに対して結合インデックスを作成します(たとえば、クエリ1にwhere句がA、B、C、Dの4つのフィールドが表示され、クエリ2にwhere句がA、B、Cの3つのフィールドが表示され、このクエリ文がよく表示されます.このエントリの下で、A、B、C、Dフィールドに対して結合インデックスを作成するだけで、2つのインデックスを作成する必要はありません).
4:インデックスのホットスポットの問題に注意して、前の3のシーンは同時に深刻な情況の下で、ホットスポットが現れるのが速いことを招く可能性があって、そのため需要によって適切に調整する必要があります