12-索引的使用以及使用提示

==================

1、总是使用索引的第一列

如果索引建立在多个列上，只有在他的第一个列被where子句引用时，优化器才会选择使用该索引

SQL> create table an3 as select * from an1
  2  union all select * from an2;

表已创建。

已用时间:  00: 00: 05.81
SQL> select count(*) from an3;

  COUNT(*)
----------
    340001

已用时间:  00: 00: 00.28
SQL> create index an2_all on an3(id,name);

索引已创建。

已用时间:  00: 00: 12.32
SQL> set autotrace on
SQL> select count(*) from an3 where name='anbaisheng';

  COUNT(*)
----------
    170000

已用时间:  00: 00: 00.09

执行计划
----------------------------------------------------------
Plan hash value: 789678643

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|*  2 |   TABLE ACCESS FULL| AN3  |
-----------------------------------

没有使用索引

SQL> select count(*) from an3 where id>=10000;

  COUNT(*)
----------
    170017

已用时间:  00: 00: 00.20

执行计划
----------------------------------------------------------
Plan hash value: 3412816813

-------------------------------------
| Id  | Operation         | Name    |
-------------------------------------
|   0 | SELECT STATEMENT  |         |
|   1 |  SORT AGGREGATE   |         |
|*  2 |   INDEX RANGE SCAN| AN2_ALL |
-------------------------------------

SQL> select count(*) from an3 where id>=10000 and name='anbaisheng';

  COUNT(*)
----------
        17

已用时间:  00: 00: 00.12

执行计划
----------------------------------------------------------
Plan hash value: 3412816813

-------------------------------------
| Id  | Operation         | Name    |
-------------------------------------
|   0 | SELECT STATEMENT  |         |
|   1 |  SORT AGGREGATE   |         |
|*  2 |   INDEX RANGE SCAN| AN2_ALL |
-------------------------------------

<结论>:
对于复合索引来说，如果只引用单个字段的话，查询时不包括第一位置的字段的话是不会使用索引的
如果是多个字段的查询，只要是包括第一个字段，索引就会被使用

----------------------

2、oracle内部操作

当执行查询时，oracle采用了内部的操作，下表显示了几种重要的内部操作

ORDER BY
SORT ORDER BY

UNION
UNION-ALL

MINUS
MINUS

INTERSECT
INTERSECT

DISTINCT,MINUS,INTERSECT,UNION
SORT UNIQUE

MIN,MAX,COUNT
SORT AGGREGATE

GROUP BY
SORT GROUP BY

ROWNUM
COUNT or COUNT STOPKEY

Queries involving Joins
SORT JOIN,MERGE JOIN,NESTED LOOPS

CONNECT BY
CONNECT BY

------------------------

3、如果有可能，尽量使用union all代替union

因为union要过滤掉重复的数据，多了一次运算
所以在不影响应用的情况下尽量使用

------------------------

4、使用提示(hints)

对于表的访问，可以使用两种hints

full和rowid

full hints告诉oracle使用全表扫描的方式访问指定表

SQL> select count(*) from an1 where name='anbaisheng';

  COUNT(*)
----------
     10000

已用时间:  00: 00: 00.04

执行计划
----------------------------------------------------------
Plan hash value: 3907877743

--------------------------------------------
| Id  | Operation         | Name           |
--------------------------------------------
|   0 | SELECT STATEMENT  |                |
|   1 |  SORT AGGREGATE   |                |
|*  2 |   INDEX RANGE SCAN| INDEX_AN1_NAME |
--------------------------------------------

SQL> select /*+ full(an1) */ count(*) from an1 where name='anbaisheng';

  COUNT(*)
----------
     10000

已用时间:  00: 00: 00.11

执行计划
----------------------------------------------------------
Plan hash value: 2832067379

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    27 |    14   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    27 |            |          |
|*  2 |   TABLE ACCESS FULL| AN1  | 10000 |   263K|    14   (0)| 00:00:01 |
---------------------------------------------------------------------------

通常, 你需要采用TABLE ACCESS BY ROWID的方式特别是当访问大表的时候,
使用这种方式, 你需要知道ROIWD的值或者使用索引.

如果一个大表没有被设定为缓存(CACHED)表而你希望它的数据在查询结束是仍然停留

在SGA中,你就可以使用CACHE hint 来告诉优化器把数据保留在SGA中.
通常CACHE hint 和 FULL hint 一起使用.

SQL> select /*+ INDEX(INDEX_AN1_NAME) */ count(*) from an1 where name='anbaisheng';

在不使用hint的情况下, 以上的查询应该也会使用索引
然而,如果该索引的重复值过多而你的优化器是CBO, 优化器就可能忽略索引.
在这种情况下, 你可以用INDEX hint强制ORACLE使用该索引.

ORACLE hints 还包括

ALL_ROWS
FIRST_ROWS
RULE
USE_NL
USE_MERGE
USE_HASH
等等