ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用索引的误区之一:没有使用复合索引的前导列导致查询不使用索引

使用索引的误区之一:没有使用复合索引的前导列导致查询不使用索引

原创 Linux操作系统 作者:qingsongduan 时间:2011-05-27 09:16:15 0 删除 编辑
使用索引的误区之一:没有使用复合索引的前导列导致查询不使用索引
使用索引的误区之一:没有使用复合索引的前导列导致查询不使用索引
  
  使用索引的误区之一:没有使用复合索引的前导列导致查询不使用索引 使用索引的误区之一:没有使用复合索引的前导列导致查询不使用索引在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
使用索引的误区之一:没有使用复合索引的前导列导致查询不使用索引 使用索引的误区之一:没有使用复合索引的前导列导致查询不使用索引 使用索引的误区之一:没有使用复合索引的前导列导致查询不使用索引在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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21728529/viewspace-696398/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2011-05-27

  • 博文量
    5
  • 访问量
    3549