使用索引的误区之一:没有使用复合索引的前导列导致查询不使用索引
使用索引的误区之一:没有使用复合索引的前导列导致查询不使用索引
在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
posted on 2007-08-02 15:30 Mr__BRIGHT 阅读(612) 评论(0) 编辑 收藏 举报
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 智能桌面机器人:用.NET IoT库控制舵机并多方法播放表情
· Linux glibc自带哈希表的用例及性能测试
· 深入理解 Mybatis 分库分表执行原理
· 如何打造一个高并发系统?
· .NET Core GC压缩(compact_phase)底层原理浅谈
· 手把手教你在本地部署DeepSeek R1,搭建web-ui ,建议收藏!
· 新年开篇:在本地部署DeepSeek大模型实现联网增强的AI应用
· Janus Pro:DeepSeek 开源革新,多模态 AI 的未来
· 互联网不景气了那就玩玩嵌入式吧,用纯.NET开发并制作一个智能桌面机器人(三):用.NET IoT库
· 【非技术】说说2024年我都干了些啥