index_join hint

index join可以连接任意两个索引(没有任何限制,如列数量,索引唯一性,以及谓词等),以实现完全不需要访问表就得到结果集的查询。每个索引都提供结果集的 一个完整或部分列的集合以及对应的rowid。Oracle接着对这两个数据集进行散列连接-使用rowid作为连接条件。连接得到的结果就是所需的结果集。对Oracle来讲, 可以对两个索引做散列连接, 自然也可以对三个索引进行连接, 一直到所有出现在select list的列都已经取到,从而得到最后需要的结果集。

env 11.2.0.4

SQL> drop table t purge;

Table dropped.

SQL> create table t as select * from dba_objects where 1 = 0;

Table created.

SQL>
SQL>
SQL> insert into t select * From dba_objects;

87021 rows created.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> create index t_name on t(object_name);

Index created.

SQL> create index t_owner on t(owner);

Index created.

SQL>
---gather statistics---
SQL> begin
  2     dbms_stats.gather_table_stats(
  3             user,
  4             'T',
  5             cascade => true,
  6             estimate_percent => null,
  7             method_opt => 'for all columns size 1'
  8     );
  9  end;
 10  /
SQL> select owner,object_name from t where owner='HR' and object_name='EMPLOYEES';
Execution Plan
----------------------------------------------------------
Plan hash value: 1610237053

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    31 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T      |     1 |    31 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_NAME |     2 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter("OWNER"='HR')
   2 - access("OBJECT_NAME"='EMPLOYEES')


PL/SQL procedure successfully completed.

--add hint INDEX_JOIN---
SQL> select /*+ index_join(t)*/ owner,object_name from t where owner='HR' and object_name='EMPLOYEES';

--OR---
select /*+ index_join(t t_owner t_name)*/ owner,object_name from t where owner='HR' and object_name='EMPLOYEES';
Execution Plan
----------------------------------------------------------
Plan hash value: 918796820

---------------------------------------------------------------------------------------
| Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  |     1 |    31 |     8   (0)| 00:00:01 |
|*  1 |  VIEW              | index$_join$_001 |     1 |    31 |     8   (0)| 00:00:01 |
|*  2 |   HASH JOIN        |                  |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| T_NAME           |     1 |    31 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN| T_OWNER          |     1 |    31 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   1 - filter("OBJECT_NAME"='EMPLOYEES' AND "OWNER"='HR')
   2 - access(ROWID=ROWID)
   3 - access("OBJECT_NAME"='EMPLOYEES')
   4 - access("OWNER"='HR')

-- 转换写法,可以控制join方式和顺序
--默认的join计划
SQL> select owner, object_name
  2    from (select /*+ no_merge*/
  3           rowid
  4            from t
  5           where owner = 'HR') v1,
  6         (select /*+ no_merge*/
  7           rowid
  8            from t
  9           where object_name = 'EMPLOYEES') v2,
 10         t t
 11   where v1.rowid = v2.rowid
 12     and v1.rowid = t.rowid;

OWNER                                                                                      OBJECT_NAME
------------------------------------------------------------------------------------------ ----------------------------------------------------------------
HR                                                                                         EMPLOYEES


Execution Plan
----------------------------------------------------------
Plan hash value: 2615164229

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     2 |   134 |    10   (0)| 00:00:01 |
|   1 |  NESTED LOOPS               |         |     2 |   134 |    10   (0)| 00:00:01 |
|*  2 |   HASH JOIN                 |         |     2 |    48 |     8   (0)| 00:00:01 |
|   3 |    VIEW                     |         |     2 |    24 |     3   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN        | T_NAME  |     2 |    74 |     3   (0)| 00:00:01 |
|   5 |    VIEW                     |         |  1978 | 23736 |     5   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN        | T_OWNER |  1978 | 35604 |     5   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY USER ROWID| T       |     1 |    43 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   2 - access("V1".ROWID="V2".ROWID)
   4 - access("OBJECT_NAME"='EMPLOYEES')
   6 - access("OWNER"='HR')

-- add some hint
SQL> select /*+ leading(t v1 v2)
  2         use_hash(t)
  3         use_hash(v1)
  4         */
  5  owner, object_name
  6    from (select /*+ no_merge*/
  7           rowid
  8            from t
  9           where owner = 'HR') v1,
 10         (select /*+ no_merge*/
 11           rowid
 12            from t
 13           where object_name = 'EMPLOYEES') v2,
 14         t t
 15   where v1.rowid = v2.rowid
 16     and v1.rowid = t.rowid;

OWNER                                                                                      OBJECT_NAME
------------------------------------------------------------------------------------------ ----------------------------------------------------------------
HR                                                                                         EMPLOYEES


Execution Plan
----------------------------------------------------------
Plan hash value: 3105060218

---------------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     2 |   134 |       |   581   (1)| 00:00:07 |
|*  1 |  HASH JOIN          |         |     2 |   134 |       |   581   (1)| 00:00:07 |
|   2 |   VIEW              |         |     2 |    24 |       |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN | T_NAME  |     2 |    74 |       |     3   (0)| 00:00:01 |
|*  4 |   HASH JOIN         |         |  1978 |   106K|  4680K|   577   (1)| 00:00:07 |
|   5 |    TABLE ACCESS FULL| T       | 87021 |  3654K|       |   342   (1)| 00:00:05 |
|   6 |    VIEW             |         |  1978 | 23736 |       |     5   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN| T_OWNER |  1978 | 35604 |       |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   1 - access("V1".ROWID="V2".ROWID)
   3 - access("OBJECT_NAME"='EMPLOYEES')
   4 - access("V1".ROWID="T".ROWID)
   7 - access("OWNER"='HR')
此条目发表在CBO, Hint分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s