and_equal最多可以指定5個index！
If the WHERE clauses uses columns of many single-column indexes, then Oracle executes the statement by performing a range scan on each index to retrieve the rowids of the rows that satisfy each condition. Oracle then merges the sets of rowids to obtain a set of rowids of rows that satisfy all conditions. Oracle then accesses the table using these rowids.
Oracle can merge up to five indexes. If the WHERE clause uses columns of more than five single-column indexes, then Oracle merges five of them, accesses the table by rowid, and then tests the resulting rows to determine whether they satisfy the remaining conditions before returning them.
SQL> create table t1(a int , b int ,c int , d int ,e int ,f int);
表已建立。
SQL> insert into t1 values(1,1,1,1,1,1);
已建立 1 行。
SQL> insert into t1 values(2,2,2,2,2,2);
已建立 1 行。
SQL> commit;
提交完成。
SQL> create index idx_a on t1(a);
索引已建立。
SQL> create index idx_b on t1(b);
索引已建立。
SQL> create index idx_c on t1(c);
索引已建立。
SQL> create index idx_d on t1(d);
索引已建立。
SQL> create index idx_e on t1(e);
索引已建立。
SQL> create index idx_f on t1(f);
索引已建立。
SQL> commit;
提交完成。
SQL> set autotrace on
SQL> select *from t1 where a=1 and b=1 and c=1 and d=1 and e=1 and f=1;
A B C D E F
---------- ---------- ---------- ---------- ---------- ----------
1 1 1 1 1 1
執行計劃
----------------------------------------------------------
Plan hash value: 2435036509
--------------------------------------------------------------------------------
-----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-----
| 0 | SELECT STATEMENT | | 1 | 78 | 2 (0)| 00:00
:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 78 | 2 (0)| 00:00
:01 |
|* 2 | INDEX RANGE SCAN | IDX_A | 1 | | 1 (0)| 00:00
:01 |
--------------------------------------------------------------------------------
-----
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"=1 AND "C"=1 AND "D"=1 AND "E"=1 AND "F"=1)
2 - access("A"=1)
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
5 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
682 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--rule下oracle自動使用and_equal對單列index進行了合併
SQL> select /*+ rule */ *from t1 where a=1 and b=1 and c=1 and d=1 and e=1 and f
=1;
A B C D E F
---------- ---------- ---------- ---------- ---------- ----------
1 1 1 1 1 1
執行計劃
----------------------------------------------------------
Plan hash value: 3163251013
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 |
| 2 | AND-EQUAL | |
|* 3 | INDEX RANGE SCAN | IDX_A |
|* 4 | INDEX RANGE SCAN | IDX_B |
|* 5 | INDEX RANGE SCAN | IDX_C |
|* 6 | INDEX RANGE SCAN | IDX_D |
|* 7 | INDEX RANGE SCAN | IDX_E |
---------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("F"=1)
3 - access("A"=1)
4 - access("B"=1)
5 - access("C"=1)
6 - access("D"=1)
7 - access("E"=1)
Note
-----
- rule based optimizer used (consider using cbo)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
682 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--6個index提示不起作用了
SQL> select /*+ and_equal(t1 idx_a idx_b idx_c idx_d idx_e idx_f) */ *from t1 wh
ere a=1 and b=1 and c=1 and d=1 and e=1 and f=1;
A B C D E F
---------- ---------- ---------- ---------- ---------- ----------
1 1 1 1 1 1
執行計劃
----------------------------------------------------------
Plan hash value: 2435036509
--------------------------------------------------------------------------------
-----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-----
| 0 | SELECT STATEMENT | | 1 | 78 | 2 (0)| 00:00
:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 78 | 2 (0)| 00:00
:01 |
|* 2 | INDEX RANGE SCAN | IDX_A | 1 | | 1 (0)| 00:00
:01 |
--------------------------------------------------------------------------------
-----
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"=1 AND "C"=1 AND "D"=1 AND "E"=1 AND "F"=1)
2 - access("A"=1)
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
4 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
682 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--說明了5個index
SQL> select /*+ and_equal(t1 idx_a idx_b idx_c idx_d idx_e) */ *from t1 where a=
1 and b=1 and c=1 and d=1 and e=1 and f=1;
A B C D E F
---------- ---------- ---------- ---------- ---------- ----------
1 1 1 1 1 1
執行計劃
----------------------------------------------------------
Plan hash value: 3163251013
--------------------------------------------------------------------------------
-----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-----
| 0 | SELECT STATEMENT | | 1 | 78 | 5 (0)| 00:00
:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 78 | 5 (0)| 00:00
:01 |
| 2 | AND-EQUAL | | | | |
|
|* 3 | INDEX RANGE SCAN | IDX_A | 1 | | 1 (0)| 00:00
:01 |
|* 4 | INDEX RANGE SCAN | IDX_B | 1 | | 1 (0)| 00:00
:01 |
|* 5 | INDEX RANGE SCAN | IDX_C | 1 | | 1 (0)| 00:00
:01 |
|* 6 | INDEX RANGE SCAN | IDX_D | 1 | | 1 (0)| 00:00
:01 |
|* 7 | INDEX RANGE SCAN | IDX_E | 1 | | 1 (0)| 00:00
:01 |
--------------------------------------------------------------------------------
-----
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=1 AND "B"=1 AND "C"=1 AND "D"=1 AND "E"=1 AND "F"=1)
3 - access("A"=1)
4 - access("B"=1)
5 - access("C"=1)
6 - access("D"=1)
7 - access("E"=1)
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
4 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
682 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/19602/viewspace-1003238/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [PT]and_equal、index_join&index_combine(zz from wanghai)IndexAI
- 被問懵了：一個程式最多可以建立多少個執行緒？執行緒
- Flutter | WReorderList 一個可以指定兩個item互換位置的元件Flutter元件
- There are 5 methods of index lookupIndex
- 兩個最多可以提高千倍效率的Go語言程式碼小技巧Go
- 日本5年後哪個行業接收外國勞動者最多？行業
- 10g可以通過命令使index unusable！Index
- 微信小程式可以指定到特定客服嗎？微信小程式
- Linux中最多有多少程式？一個程式最多有多少執行緒？Linux執行緒
- 最多能建立多少個 TCP 連線？TCP
- 如果INDEX表空間滿了，資料是否可以插入Index
- 點選可以平滑定位到網頁指定位置網頁
- 5G技術可以改變的20個行業行業
- 5個可以幫你優化App的優秀網站優化APP網站
- 這 5 個前端元件庫，可以讓你放棄 jQuery UI前端元件jQueryUI
- nginx訪問預設index.html首頁跳轉指定頁面NginxIndexHTML
- oracle blob_clob列的表如何指定sement_name及index nameOracleIndex
- 10 個開發新人提及最多的 GitHub RepoGithub
- 您可以信賴的5個AI解決方案提供商AI
- 分享5款小工具，每個都可以免費使用
- 分享5款後臺收到的推薦最多的軟體
- 什麼是 SAP UI5 Application IndexUIAPPIndex
- 一個資料庫最多可有多少個例項，多少個日誌組資料庫
- 獲取一個字串中出現最多的字元和他的個數字串字元
- [譯] 5 個可以立刻在你的 Ionic App 中用上的動畫包APP動畫
- 選擇合適的伺服器，可以遵循這5個原則伺服器
- 一個sql最多可擁有多少子游標SQL
- 設計一個函式，傳入一個數字n，若n可以被3整除的返回1，可以被5整除返回2，可以被7整除返回3函式
- 庫克明日為希拉蕊舉辦籌款活動 每人最多捐5萬
- 建立index 指定parallel,但是impdp時候匯入卻不是我建立的語句?IndexParallel
- Oracle index 使用的一個總結OracleIndex
- GitHub 上 25 個 Python 學習資源，你最多知道五個GithubPython
- Vue 中為何不可以使用Index 作為Dom的key?VueIndex
- KEEP INDEX | DROP INDEXIndex
- extjs下載地址--最多例項--最多doc文件JS
- 教你14個iPhone省電技巧，最多可節省20%！iPhone
- 元素滾動到指定位置以後可以實現固定效果
- 友邦：健康保險公司稱Apple Watch最多可以使人們的活動量增加35%APP