index_join提示

RDBMS 12.2.0.1

Index_join
index_join把同一个表上的索引合并在一起，在查询的时候，只需要访问这些索引就可以了，省略了回表这个动作。

参考文档：

Optimizer Access Paths (oracle.com)

8.3.7 Index Join Scans

An index join scan is a hash join of multiple indexes that together return all columns requested by a query. The database does not need to access the table because all data is retrieved from the indexes.

This section contains the following topics:

8.3.7.1 When the Optimizer Considers Index Join Scans

In some cases, avoiding table access is the most cost efficient option.

The optimizer considers an index join in the following circumstances:

• A hash join of multiple indexes retrieves all data requested by the query, without requiring table access.

• The cost of retrieving rows from the table is higher than reading the indexes without retrieving rows from the table. An index join is often expensive. For example, when scanning two indexes and joining them, it is often less costly to choose the most selective index, and then probe the table.

You can specify an index join with the INDEX_JOIN(table_name) hint.

See Also:

Oracle Database SQL Language Reference

8.3.7.2 How Index Join Scans Work

An index join involves scanning multiple indexes, and then using a hash join on the rowids obtained from these scans to return the rows.

In an index join scan, table access is always avoided. For example, the process for joining two indexes on a single table is as follows:

1. Scan the first index to retrieve rowids.

2. Scan the second index to retrieve rowids.

3. Perform a hash join by rowid to obtain the rows.

8.3.7.3 Index Join Scans: Example

--创建测试用的索引(略，使用hr schema中默认的索引 ) 

-- 不使用hint index_join的执行计划，可以发现，回表了employees.

select 
employee_id,last_name 
from employees
where employee_id = 7750
and department_id =1 ;

select * from table(dbms_xplan.display);

[email protected]> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1833546154

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

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

   1 - filter("DEPARTMENT_ID"=1)
   2 - access("EMPLOYEE_ID"=7750)

15 rows selected.

[email protected]> 

-- 使用hint index_join,可以发现，数据直接在index中就可以找到，没有访问任何表 

select /*+ index_join(employees emp_emp_id_pk emp_department_ix) */
employee_id,last_name 
from employees
where employee_id = 7750
and department_id =1 ;

[email protected]> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3824254325

--------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                   |     1 |    15 |     2   (0)| 00:00:01 |
|*  1 |  VIEW                  | index$_join$_001  |     1 |    15 |     2   (0)| 00:00:01 |
|*  2 |   HASH JOIN            |                   |       |       |            |          |
|*  3 |    HASH JOIN           |                   |       |       |            |          |
|*  4 |     INDEX RANGE SCAN   | EMP_EMP_ID_PK     |     1 |    15 |     0   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN   | EMP_DEPARTMENT_IX |     1 |    15 |     1   (0)| 00:00:01 |
|   6 |    INDEX FAST FULL SCAN| EMP_NAME_IX       |     1 |    15 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   1 - filter("EMPLOYEE_ID"=7750 AND "DEPARTMENT_ID"=1)
   2 - access(ROWID=ROWID)
   3 - access(ROWID=ROWID)
   4 - access("EMPLOYEE_ID"=7750)
   5 - access("DEPARTMENT_ID"=1)

22 rows selected.

[email protected]> 

END