複合索引中前導列對sql查詢的影響
[oracle@ora10g ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 21 14:35:56 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn scott/tiger
Connected.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
在三個列上建立複合索引
SQL> create index emp_index on emp(EMPNO,ENAME,DEPTNO);
Index created.
SQL> set autotrace on explain;
透過下面的測試來分析sql查詢在不同謂詞的情況下是否會使用索引
測試1
SQL> select * from emp where EMPNO=7900;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
測試2
SQL> select * from emp where EMPNO=7788 and ENAME='SCOTT';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='SCOTT')
2 - access("EMPNO"=7788)
測試3
SQL> select * from emp where EMPNO=7566 and ENAME='MANAGER' and DEPTNO=20;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='MANAGER' AND "DEPTNO"=20)
2 - access("EMPNO"=7566)
測試4
SQL> select * from emp where ENAME='BLAKE';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 37 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='BLAKE')
測試5
SQL> select * from emp where ENAME='ADAMS' and DEPTNO=20;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 37 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='ADAMS' AND "DEPTNO"=20)
測試6
SQL> select * from emp where DEPTNO=30 and ENAME='TURNER';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 37 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='TURNER' AND "DEPTNO"=30)
測試7
SQL> select * from emp where DEPTNO=10 and EMPNO=7934;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82 1300 10
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=10)
2 - access("EMPNO"=7934)
測試8
SQL> select * from emp where EMPNO=7521 or EMPNO=7839;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7839 KING PRESIDENT 17-NOV-81 5000 10
Execution Plan
----------------------------------------------------------
Plan hash value: 339106968
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 74 | 2 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 74 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_INDEX | 2 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPNO"=7521 OR "EMPNO"=7839)
測試9
SQL> select * from emp where EMPNO in(7844,7902);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
Execution Plan
----------------------------------------------------------
Plan hash value: 339106968
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 74 | 2 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 74 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_INDEX | 2 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPNO"=7844 OR "EMPNO"=7902)
結論：有上述測試可以看出，如果sql查詢的謂詞中沒有複合索引中的前導列（EMPNO），則查詢不走索引。可見建立複合索引時前導列的選擇至關重要。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25373498/viewspace-1306086/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 組合索引的前導列與查詢——ORACLE索引Oracle
- 淺談SQL Server中統計對於查詢的影響SQLServer
- SQL查詢結果集對注入的影響及利用SQL
- 查詢中讓優化器使用複合索引優化索引
- oracle 索引升降序及排序條件 對查詢計劃的影響Oracle索引排序
- 【Oracle】-【COMMIT對索引的影響】-從trace看COMMIT對索引的影響OracleMIT索引
- MySQL：查詢欄位數量多少對查詢效率的影響MySql
- shrink 操作對索引的影響索引
- Update操作對索引的影響索引
- 資料列not null對索引影響一例Null索引
- Arraysize的設定以及對查詢效能的影響
- 對列進行連線操作會影響索引的使用索引
- SQLT 最佳化SQL 用複合索引代替單列索引的案例SQL索引
- 並行查詢對於響應時間的影響實驗並行
- oracle分割槽表的常規操作導致對索引的影響Oracle索引
- SQL SERVER中什麼情況會導致索引查詢變成索引掃描SQLServer索引
- 查詢索引 常用SQL索引SQL
- dex最佳化對Arouter查詢路徑的影響
- Postgresql MVCC架構對從庫長查詢的影響SQLMVC架構
- Oracle多列統計資訊與直方圖對有關聯多列查詢影響Oracle直方圖
- shrink 與rebuild對索引高度的影響對比Rebuild索引
- Elasticsearch複合查詢——boosting查詢Elasticsearch
- Sql Server之旅——第十站 看看DML操作對索引的影響SQLServer索引
- 【Oracle】-【ROWNUM與索引】-索引對ROWNUM檢索的影響Oracle索引
- 兩列複合索引中(交換索引中列的順序)，選索引的原則是?因為索引名字ascii碼小？索引ASCII
- SQL 複雜查詢SQL
- SQL複雜查詢SQL
- 新增欄位對SQL的影響SQL
- MySQL 合併查詢join 查詢出的不同列合併到一個表中MySql
- delete語句對索引的影響之分析delete索引
- 索引對直接路徑載入的影響索引
- 關於聯合索引，範圍查詢，時間列索引的幾個問題索引
- SQL聯合查詢中的關鍵語法SQL
- 表資料的儲存對索引的影響索引
- 分割槽表的不同操作對索引的影響索引
- 查詢當前SQL Server的版本SQLServer
- SQL查詢列（欄位）重複值及操作--整理SQL
- SQL Server對組合查詢結果排序方法SQLServer排序