2012年4月10日 周二

8:30到公司8:3012:00新闻13:0013:30case13:30学习

总结一下oracle的hint

在调优中最常用的两种hint就是 access hint 和 join hint，先看一下access hint

• FULL

• CLUSTER

• HASH

• INDEX and NO_INDEX

• INDEX_ASC and INDEX_DESC

• INDEX_COMBINE and INDEX_JOIN

• INDEX_JOIN

• INDEX_FFS and NO_INDEX_FFS

• INDEX_SS and NO_INDEX_SS

• INDEX_SS_ASC and INDEX_SS_DESC

这些列出来的HINT中

FULL用于全表扫描

INDEX用于采用INDEX扫描

SQL> explain plan for select /*+ INDEX(objects SYS_C0087859)*/ object_id from kramer.objects;SQL> select * from table(dbms_xplan.display);-----------------------------------------------------------------------------| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------|   0 | SELECT STATEMENT  |         | 55265 |   701K|   182   (4)| 00:00:03 ||   1 |  TABLE ACCESS FULL| OBJECTS | 55265 |   701K|   182   (4)| 00:00:03 |-----------------------------------------------------------------------------

注意，上面的例子虽然使用了 hint 但是仍然采用了全表扫描，这是因为要使用的索引列中有NULL值。  NULL值在索引中不会存储，所以oracle认为索引扫描不能实现SQL语句的逻辑需求，所以忽略了这个hint。 下面我们把索引列设为NOT NULL，这样就可以成功使用索引

SQL> alter table kramer.objects modify object_id not null;SQL> explain plan for select /*+ INDEX(objects SYS_C0087859)*/ object_id from kramer.objects;---------------------------------------------------------------------------------| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------|   0 | SELECT STATEMENT |              | 55265 |   701K|   133   (4)| 00:00:02 ||   1 |  INDEX FULL SCAN | SYS_C0087859 | 55265 |   701K|   133   (4)| 00:00:02 |---------------------------------------------------------------------------------

下面是使用了这个hint后查询优化器的行为。

• If the INDEX hint specifies a single available index, then the database performs a scan on this index. The optimizer does not consider a full table scan or a scan of another index on the table.

  
  

• For a hint on a combination of multiple indexes, Oracle recommends using INDEX_COMBINE rather than INDEX, because it is a more versatile hint. If the INDEX hint specifies a list of available indexes, then the optimizer considers the cost of a scan on each index in the list and then performs the index scan with the lowest cost. The database can also choose to scan multiple indexes from this list and merge the results, if such an access path has the lowest cost. The database does not consider a full table scan or a scan on an index not listed in the hint.

  如果在hint中列出了多个索引，最好使用INDEX_COMBINE 这个hint。 如果使用的是hint 加多个索引的列表，查询优化器会根据自己的计算的最低开销选取其中一个或者若干个index来访问，但不会考虑全表扫描或者没有在列表中提到的index。

• If the INDEX hint specifies no indexes, then the optimizer considers the cost of a scan on each available index on the table and then performs the index scan with the lowest cost. The database can also choose to scan multiple indexes and merge the results, if such an access path has the lowest cost. The optimizer does not consider a full table scan.

  在没指定index的情况下，就相当于在列表里提到了要访问表的所有index。

NO_INDEX用于不采用指定index扫描

SQL> explain plan for select /*+ no_index(objects) */ object_id from kramer.objects where object_id=222;SQL> select * from table(dbms_xplan.display);-----------------------------------------------------------------------------| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------|   0 | SELECT STATEMENT  |         |   652 |  8476 |   184   (5)| 00:00:03 ||*  1 |  TABLE ACCESS FULL| OBJECTS |   652 |  8476 |   184   (5)| 00:00:03 |-----------------------------------------------------------------------------

下面是采取了这个hint后查询优化器的思路

• If this hint specifies a single available index, then the optimizer does not consider a scan on this index. Other indexes not specified are still considered.

• If this hint specifies a list of available indexes, then the optimizer does not consider a scan on any of the specified indexes. Other indexes not specified in the list are still considered.

• If this hint specifies no indexes, then the optimizer does not consider a scan on any index on the table. This behavior is the same as a NO_INDEX hint that specifies a list of all available indexes for the table.

  
  

  
  

INDEX_ASC INDEX_DESC

用于采取 升序 扫描索引，例如下面的例子

SQL> explain plan for select /*+ index_asc(objects SYS_C0087859)*/ object_id from kramer.objects order by object_id desc;Explained.SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------Plan hash value: 3091698887-----------------------------------------------------------------------------------------| Id  | Operation        | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT |              | 55265 |   701K|       |   413   (5)| 00:00:05 ||   1 |  SORT ORDER BY   |              | 55265 |   701K|  2184K|   413   (5)| 00:00:05 ||   2 |   INDEX FULL SCAN| SYS_C0087859 | 55265 |   701K|       |   133   (4)| 00:00:02 |-----------------------------------------------------------------------------------------

本来在语句中指定了 order by objecrt_id desc ， 为了避免排序操作，oracle会采取降序扫描索引的方式来访问表，但是我们指定了hint，所以采取的是升序。 index_desc的例子跟这个一样。

INDEX_COMBINE

这个HINT花了我好长时间，看它的官方说明如下：

The INDEX_COMBINE hint instructs the optimizer to use a bitmap access path for the table. If indexspec is omitted from the INDEX_COMBINE hint, then the optimizer uses whatever Boolean combination of indexes has the best cost estimate for the table. If you specify indexspec, then the optimizer tries to use some Boolean combination of the specified indexes. Each parameter serves the same purpose as in "INDEX Hint". For example:

它是说，如果指定了这个hint，oracle可能会把一些索引转化成位图索引，然后把这些位图索引进行位操作，以缩小结果集。也就是说有时候你建立的索引是B-Tree索引，但是oracle仍然会把他们转化成位图索引然后来操作，为了理解这一过程，专门去查了位图索引的工作方式以及这样转化的意义。下面总结一下：

位图索引工作方式：参见concept bitmap index

B-Tree转化成位图索引的意义：位图索引在进行count，and，or等操作时效率较高，所以有时查询优化器会把B-tree扫描到的rowid 转化成一个位图索引，然后对其进行count，and，or等操作。

下面看使用这个hint的例子：

http://www.itpub.net/thread-1601847-1-1.html

INDEX_JOINE

这个hint的目的是让oracle不必返回表中取数据，如果index中有的话。例如：

SQL>  create table test as select object_id,object_type,status from dba_objects;Table created.SQL> create index tobject_id on test(object_id);Index created.SQL>  create index tobject_type on test(object_type);Index created.SQL> explain plan for   select  object_id,object_type from test where object_id=15847 and object_type='JAVA CLASS';------------------------------------------------------------------------------------------| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |            |     3 |    72 |     2   (0)| 00:00:01 ||*  1 |  TABLE ACCESS BY INDEX ROWID| TEST       |     3 |    72 |     2   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | TOBJECT_ID |     3 |       |     1   (0)| 00:00:01 |------------------------------------------------------------------------------------------

上面没有使用hint，所以oracle的做法是在tobject_id这个索引中获得rowid然后根据rowid去表中提取数据。但是如果我们使用了index_joine，那么oracle会访问tobject_id和tobject_type这两个索引，然后把获取的数据连接返回，这样就不用返回表取数据了。

SQL> explain plan for select /*+ index_join(test tobject_id tobject_type) */  object_id,object_type from test where object_id=15847 and object_type='JAVA CLASS';--------------------------------------------------------------------------------------------------| Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT              |                  |     3 |    72 |     4  (25)| 00:00:01 ||*  1 |  VIEW                         | index$_join$_001 |     3 |    72 |     4  (25)| 00:00:01 ||*  2 |   HASH JOIN                   |                  |       |       |            |          ||*  3 |    INDEX RANGE SCAN           | TOBJECT_ID       |     3 |    72 |     1   (0)| 00:00:01 ||   4 |    BITMAP CONVERSION TO ROWIDS|                  |     3 |    72 |     2   (0)| 00:00:01 ||*  5 |     BITMAP INDEX SINGLE VALUE | TOBJECT_TYPE     |       |       |            |          |

INDEX_FFS

该hint指定采用 index fast full scan

SQL> explain plan for select /*+ INDEX_FFS(TEST TOBJECT_ID) */  object_id from test;Explained.SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 2112390145-----------------------------------------------------------------------------------| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------------|   0 | SELECT STATEMENT     |            | 56227 |   274K|    32  (10)| 00:00:01 ||   1 |  INDEX FAST FULL SCAN| TOBJECT_ID | 56227 |   274K|    32  (10)| 00:00:01 |-----------------------------------------------------------------------------------8 rows selected.

关于这个hint要注意的一点是，和index hint一样，如果object_id 包含null，那么这个hint就有可能不被使用， 因为在一列的hint上不会存储null。

INDEX_SS

An index skip scan uses logical subindexes of a composite index. The database "skips" through a single index as if it were searching separate indexes. Skip scanning is beneficial if there are few distinct values in the leading column of a composite index and many distinct values in the nonleading key of the index.

The database may choose an index skip scan when the leading column of the composite index is not specified in a query predicate. For example, assume that you run the following query for a customer in the sh.customers table:

SELECT * FROM sh.customers WHERE cust_email = '[e-mail]'; 

The customers table has a column cust_gender whose values are either M or F. Assume that a composite index exists on the columns (cust_gender, cust_email). Example 3-1 shows a portion of the index entries.

Example 3-1 Composite Index Entries

F,[e-mail],rowid F,[e-mail],rowid F,[e-mail],rowid F,[e-mail],rowid F,[e-mail],rowid F,[e-mail],rowid M,[e-mail],rowid M,[e-mail],rowid 

The database can use a skip scan of this index even though cust_gender is not specified in the WHERE clause.

In a skip scan, the number of logical subindexes is determined by the number of distinct values in the leading column. InExample 3-1, the leading column has two possible values. The database logically splits the index into one subindex with the key F and a second subindex with the key M.

When searching for the record for the customer whose email is [e-mail], the database searches the subindex with the value F first and then searches the subindex with the value M. Conceptually, the database processes the query as follows:

SELECT * FROM sh.customers WHERE cust_gender = 'F'    AND cust_email = '[e-mail]' UNION ALL SELECT * FROM sh.customers WHERE cust_gender = 'M'   AND cust_email = '[e-mail]';结论：1、ORACLE太智能了吧。。。这都能分析。。。2、如果WHERE语句的过滤列，没有单独的索引，也并不表示数据库就一定不会使用索引扫描。疑问：什么情况下会自动使用这种索引扫描呢？就是说，主索引列的可能的取值情况，少到什么程度，数据库才会采用这种“索引跳跃扫描”呢？