使用索引的誤區之一：沒有使用復合索引的前導列導致查詢不使用索引
2024-07-21 02:11:41
供稿：網友
國內最大的酷站演示中心!
使用索引的誤區之一：沒有使用復合索引的前導列導致查詢不使用索引
在oracle中，我們經常以為建立了索引，sql查詢的時候就會如我們所希望的那樣使用索引，事實上，oracle只會在一定條件下使用索引，這里我們總結數第一點：oracle會在條件中包含了前導列時使用索引，即查詢條件中必須使用索引中的第一個列，請看下面的例子
sql> select * from tab;
tname tabtype clusterid
------------------------------ ------- ----------
bonus table
dept table
dummy table
emp table
salgrade table
建立一個聯合索引（注意復合索引的索引列順序）
sql> create index emp_id1 on emp(empno,ename,deptno);
index created
建立一個單鍵索引
sql> create index emp_id2 on emp(sal);
index created
sql> select table_name,index_name from user_indexes
2 where table_name='emp';
table_name index_name
------------------------------ ------------------------------
emp emp_id1
emp emp_id2
sql> select * from user_ind_columns
2 /
index_name table_name column_name column_position column_length char_length descend
------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------------- ------------- ----------- -------
emp_id1 emp empno 1 22 0 asc
emp_id1 emp ename 2 10 10 asc
emp_id1 emp deptno 3 22 0 asc
emp_id2 emp sal 1 22 0 asc
下面的查詢由于沒有使用到復合索引的前導列，所以沒有使用索引
select job, empno from emp where ename='rich';
plan_table_output
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| id | operation | name | rows | bytes | cost |
--------------------------------------------------------------------
| 0 | select statement | | | | |
|* 1 | table access full | emp | | | |
--------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - filter("emp"."ename"='rich')
note: rule based optimization
14 rows selected
下面的查詢也由于沒有使用到復合索引的前導列，所以沒有使用索引
select job, empno from emp where deptno=30;
plan_table_output
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| id | operation | name | rows | bytes | cost |
--------------------------------------------------------------------
| 0 | select statement | | | | |
|* 1 | table access full | emp | | | |
--------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - filter("emp"."deptno"=30)
note: rule based optimization
14 rows selected
下面的查詢使用了復合索引中的前導列，所以查詢走索引了
select job, empno from emp where empno=7777;
plan_table_output
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost |
---------------------------------------------------------------------------
| 0 | select statement | | | | |
| 1 | table access by index rowid| emp | | | |
|* 2 | index range scan | emp_id1 | | | |
---------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
2 - access("emp"."empno"=7777)
note: rule based optimization
15 rows selected
下面的查詢使用了復合索引中的第一列和第二列，所以查詢走索引了
select job, empno from emp where empno=7777 and ename='rich';
plan_table_output
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost |
---------------------------------------------------------------------------
| 0 | select statement | | | | |
| 1 | table access by index rowid| emp | | | |
|* 2 | index range scan | emp_id1 | | | |
---------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
2 - access("emp"."empno"=7777 and "emp"."ename"='rich')
note: rule based optimization
15 rows selected
使用了復合索引的全部列，所以走索引了，另外由于選了了索引中沒有包含的列（job），
所以進行索引全表掃描得到滿足條件的rowid后，還要到表中檢索相應的行
select job, empno from emp where empno=7777 and ename='rich' and deptno=30;
plan_table_output
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost |
---------------------------------------------------------------------------
| 0 | select statement | | | | |
| 1 | table access by index rowid| emp | | | |
|* 2 | index range scan | emp_id1 | | | |
---------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
2 - access("emp"."empno"=7777 and "emp"."ename"='rich' and "emp"."dep
tno"=30)
note: rule based optimization
16 rows selected
使用了復合索引的全部列，所以走索引了，而且由于所有選擇的列都包含在索引中，所以僅僅進行了索引范圍掃描
select empno from emp where empno=7777 and ename='rich' and deptno=30;
plan_table_output
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| id | operation | name | rows | bytes | cost |
--------------------------------------------------------------------
| 0 | select statement | | | | |
|* 1 | index range scan | emp_id1 | | | |
--------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - access("emp"."empno"=7777 and "emp"."ename"='rich' and "em
p"."deptno"=30)
note: rule based optimization
15 rows selected
