Oracle如何避免使用索引

如何避免使用索引
一般而言,人们更倾向于寻找一些方法,让成本优化器使用所创建的索引,而不是让它忽略现有的索引。不过,有些时候,你想要做的恰恰是:让优化器不使用索引。

1 在任何情况下都不使用某个索引
例如,在处理某个选择性不强的索引时,有可能希望不使用该索引。这是因为如果某个索引没有足够的选择性,扫描整个基础表有时会更有效。

如果SQL语句包含一个结构,如包括某个索引列的where子句,优化器可能会使用该列上的索引。为了阻止这种情况的发生,可以在查询语句中指定no_index提示,让优化器不得使用某个索引。例如:

SQL> select /*+ no_index(emp dept_idx) */ * from emp where department_id=10;


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    69 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    69 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPARTMENT_ID"=10)

这个例子列出了想要让优化器忽略的具体索引。如果在表上有其他索引,优化器仍然会考虑使用那些索引。

除了指定单个索引,也可以列出一组优化器必须忽略的索引。如果只是指定no_index提示,而没有列出任何索引,优化器将忽略你指定的表的所有索引。与在本章后面将会介绍的index提示一样,no_index提示适用于B树索引,基于函数的索引,聚簇索引和域索引。

2 只避免快速扫描
no_index_ffs提示可以用来指示优化器避免对某个索引执行索引快速全扫描。请注意,与index提示的情况一样,必须在指定这个提示的同时指定特定的索引名字。例如:

SQL> select /*+ no_index_ffs(test test_i1) */   c from test;

同样,也可以使用no_index_ss提示来告诉优化器排队表中具体索引的跳跃式扫描。

3 强制表扫描
另一种避免使用索引的方法是从相反的方向解决问题,即明确要求执行表扫描。使用full提示指示优化器选择全表扫描而不是索引扫描。下面是如何指定full提示的例子:

SQL> select /*+ full(emp ) */ * from emp where department_id=10;

Oracle全索引扫描

全索引扫描
全索引扫描是读取给定索引中所有条目的操作。从这个意义上说,全索引扫描类似于全表扫描。与首先做全表扫描,然后再对数据排序相比,全索引扫描是一种很好的替代方法。Oracle数据库在下面任何一种情况下都可能使用全索引扫描。
.查询需要排序合并连接(sort merge join):查询所引用的所有列必须都在索引中存在,且前导索引列的顺序也必须与查询中指定的列顺序相同。
.查询包含order by子句:子句中的所有列必须都在该索引中存在。
.查询包含group by子句:索引和group by子句必须包含相同的列,但不要求它们的顺序一定相同。

下面的例子显示数据库如何利用全索引扫描操作检索数据,而无需执行排序操作。因为索引已经排序,所以使用全索引扫描可以不必执行排序操作。全索引扫描读取单个个数据块,而不执行多块读取操作。

SQL> select * from test where c<30000 order by c;

29999 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2983339933

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         | 29999 |   292K|    26   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    | 29999 |   292K|    26   (0)| 00:00:01 |
|*  2 |   INDEX FULL  SCAN          | TEST_I1 | 29999 |       |    14   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C"<30000)

在此查询中,数据库首先执行全索引扫描,然后通过索引rowid操作执行表访问。这是因为查询请求了除索引列外的其他列(select * from …)。然而,如果查询只请求了索引列并使用了order by子句,数据库将跳过表访问,只通过访问索引得到数据,无需读取表中的值。

索引快速全扫描
当索引本身包含查询中指定的所有列时,Oracle数据库执行索引快速全扫描代替全表扫描。请注意,在下面的例子中,检索数据只用了索引快速全扫描操作,且根本没有访问表本身:

SQL> select   c from test where c<10000;

9999 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3298034341

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |  9999 | 49995 |   123   (1)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| TEST_I1 |  9999 | 49995 |   123   (1)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C"<10000)

请注意,不像全索引扫描,索引快速全扫描使用多块读操作来读取索引。因此,既因为多块的I/O,又因为这种类型的扫描可以像全表扫描那样并行运行,所以这种类型的扫描往往更快。

Oracle索引跳跃式扫描

索引跳跃式扫描
当一个查询未在where子句的谓词中指定复合索引的前导列,查询“跳过”该列时,就会发生索引跳跃式扫描。数据库将一个复合索引拆分成多个逻辑子索引。复合索引前导列中的不同值越少,组成复合索引的其他键的不同值越多,索引跳跃式扫描的性能就越好。例如,如果前导列有三个不同值,数据库将把复合索引拆分成三个逻辑子索引,并在其中搜索索引非前导列的值。

在下面的例子中,数据库使用了复合索引test_i1,它是在列(b,c)上创建的。查询指定的条件是”c<10″。查询的where子句没有使用复合索引的前导列(b),从而跳过该列。

SQL> select * from test where c<10;

         A B                   C
---------- ---------- ----------
         0 F                   1
         0 F                   2
         0 F                   3
         0 F                   4
         0 F                   5
         0 F                   6
         0 F                   7
         0 F                   8
         0 F                   9

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 650436733

-----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |     9 |    90 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST    |     9 |    90 |     1   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN                   | TEST_I1 |     9 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C"<10)
       filter("C"<10)

数据库把复合索引拆分成多少逻辑子索引,取决于前导列的不同值数量。在这个例子中,复合索引的前导列b只有极少数不同的值:

SQL> select distinct b from test;

B
----------
M
F

正如输出结果所显示的,复合索引的前导列只有两个不同的值。数据库把复合索引(b,c)拆分成两个子索引,第一个子索引的键值是”M”,而第二个子索引的键值是”2″。数据库检索第一个子索引,然后检索第二个子索引。另外,索引的非前导列c有200000个不同的值(与表中的行数据相同)。在这样的情况下,由于复合索引前导列中的不同值很少,数据库将采用复合索引跳跃式扫描,而不是像在以前版本的数据库(也就是说,Oracle 9i以前的版本)中那样执行全表扫描。

Oracle索引范围扫描

索引范围扫描
当数据库需要访问具有高度选择性的数据时,它执行索引范围扫描。数据库按升序返回索引列的值。如果所有行的索引列都是相同的,则按rowid的顺序输出。

当处理以下类型的条件时,优化器会选择索引范围扫描,其中col1是某个索引的前导列:

col1=:b1
col1<:b1 col1>:b1

此外,包含索引前导列的上述三种条件的任何”与 (and)组合,也将导致索引范围扫描。

索引数据以升序存储。如果数据库需要返回降序的数据时,比如需要首先传回最新的数据,或需要检索小于某一具体的数据时,它使用降序索引范围扫描。

SQL> select * from emp where department_id<20;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE    JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ------------ ---------- ---------- -------------- ---------- -------------
        200 Jennifer             Whalen                    JWHALEN                   515.123.4444         17-SEP-03    AD_ASST          4400                       101            10


Execution Plan
----------------------------------------------------------
Plan hash value: 1909286381

------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |    11 |   759 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP      |    11 |   759 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | DEPT_IDX |    11 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPARTMENT_ID"<20)

从底部开始阅读,解释计划表明,优化器首先执行索引范围扫描,并使用从索引范围扫描获得的rowid执行表访问(table access by index rowid batched操作)。在指定了“大于”条件,如”department_id>100″时,数据库也可能使用索引范围扫描,如下面的例子所示:

SQL> select * from emp where department_id>100;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE    JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ------------ ---------- ---------- -------------- ---------- -------------
        205 Shelley              Higgins                   SHIGGINS                  515.123.8080         07-JUN-02    AC_MGR          12008                       101           110
        206 William              Gietz                     WGIETZ                    515.123.8181         07-JUN-02    AC_ACCOUNT       8300                       205           110


Execution Plan
----------------------------------------------------------
Plan hash value: 1909286381

------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |    11 |   759 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP      |    11 |   759 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | DEPT_IDX |    11 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPARTMENT_ID">100)

当查询使用between操作符时,数据库也会执行索引范围扫描,如下面的例子所示:

SQL> select * from emp where department_id between 100 and 110;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE    JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ------------ ---------- ---------- -------------- ---------- -------------
        108 Nancy                Greenberg                 NGREENBE                  515.124.4569         17-AUG-02    FI_MGR          12008                       101           100
        109 Daniel               Faviet                    DFAVIET                   515.124.4169         16-AUG-02    FI_ACCOUNT       9000                       108           100
        110 John                 Chen                      JCHEN                     515.124.4269         28-SEP-05    FI_ACCOUNT       8200                       108           100
        111 Ismael               Sciarra                   ISCIARRA                  515.124.4369         30-SEP-05    FI_ACCOUNT       7700                       108           100
        112 Jose Manuel          Urman                     JMURMAN                   515.124.4469         07-MAR-06    FI_ACCOUNT       7800                       108           100
        113 Luis                 Popp                      LPOPP                     515.124.4567         07-DEC-07    FI_ACCOUNT       6900                       108           100
        205 Shelley              Higgins                   SHIGGINS                  515.123.8080         07-JUN-02    AC_MGR          12008                       101           110
        206 William              Gietz                     WGIETZ                    515.123.8181         07-JUN-02    AC_ACCOUNT       8300                       205           110

8 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1909286381

------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |    20 |  1380 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP      |    20 |  1380 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | DEPT_IDX |    20 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPARTMENT_ID">=100 AND "DEPARTMENT_ID"< =110)

请注意,虽然在指定条件"where department_id <20"及"where department_id>100"时,数据库使用索引范围扫描,但如果把查询谓词修改为"where department_id<1200",它就会转而执行全表扫描。原因很简单:在指定条件为"department_id<20"或"department_id>9000"的条件时,相比条件"department_id<1200",数据库需要扫描的值要少得多。很可能有大量的值都满足条件"department_id<120",因此在指定这个条件时,数据库将执行全表扫描来检索数据,如下面的示例所示:

SQL> select * from emp where department_id<1200;

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1107 |  7383 |    13   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |  1107 |  7383 |    13   (0)| 00:00:01 |
--------------------------------------------------------------------------

   2 - access("DEPARTMENT_ID"<1200)

以下是另一个例子,显示了当查询谓词需要搜索表中绝大部分行时,数据库为何倾向于执行全表扫描,条件”department_id>30″显示比”department_id<1200″需要扫描更多的数据。

SQL> select * from emp where department_id>30;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2078 |  7383 |    23   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |  2078 |  7383 |    23   (0)| 00:00:01 |
--------------------------------------------------------------------------

  2 - access("DEPARTMENT_ID">30)

索引降序范围扫描(index range scan descending)操作与索引范围扫描非常类似,不同的是,使用索引降序范围扫描时数据库引擎以降序读取结果。优化器之所以做出这样的选择,其中一个原因可能是避免将来再排序。在指定order by desc子句而索引又能满足该子句时,基于成本的优化器将使用索引降序范围扫描操作,从而避免降序操作。索引降序范围扫描操作向后读取索引,以避免按正常次序(升序)读取,然后再执行降序排序操作。

SQL> select * from emp where department_id<20 order by department_id desc;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE    JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ------------ ---------- ---------- -------------- ---------- -------------
        200 Jennifer             Whalen                    JWHALEN                   515.123.4444         17-SEP-03    AD_ASST          4400                       101            10


Execution Plan
----------------------------------------------------------
Plan hash value: 3507836879

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |    11 |   759 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | EMP      |    11 |   759 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN DESCENDING| DEPT_IDX |    11 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPARTMENT_ID"<20)

Oracle索引唯一扫描

索引唯一扫描
如果查询只需要从表中获取单个行,数据库就执行索引唯一扫描。当查询只包含某个唯一索引中的列时,数据库使用索引唯一扫描。当查询在主键约束列上指定了相等条件时,数据库也使用索引唯一扫描。

在下面的例子中,在列employee_id上有主键约束,因此查询通过谓词”where employee_id=200″保证只访问一行。

SQL> select * from emp where employee_id=200;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE    JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ------------ ---------- ---------- -------------- ---------- -------------
        200 Jennifer             Whalen                    JWHALEN                   515.123.4444         17-SEP-03    AD_ASST          4400                       101            10


Execution Plan
----------------------------------------------------------
Plan hash value: 4024650034

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    69 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    69 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPLOYEE_ID"=200)

在这个例子中,数据库使用列employee_id上的主键(emp_pk)。注意,在指定了某个唯一索引的所有列时,数据库也很可能执行索引唯一扫描。

Proudly powered by WordPress | Indrajeet by Sus Hill.