SQL> set autot trace exp stat;
SQL> select /*+index_join(t idx_id,idx_biz_date)*/t.id,t.biz_date
2 from scott.t_group1 t
3 where t.biz_date='2' and t.id=1600;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 4285586066
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 5 | 45 | 2358 (4)| 00:
00:29 |
|* 1 | VIEW | index$_join$_001 | 5 | 45 | 2358 (4)| 00:
00:29 |
|* 2 | HASH JOIN | | | | |
|
|* 3 | INDEX RANGE SCAN| IDX_ID | 5 | 45 | 3 (0)| 00:
00:01 |
|* 4 | INDEX RANGE SCAN| IDX_BIZ_DATE | 5 | 45 | 2339 (3)| 00:
00:29 |
--------------------------------------------------------------------------------
-------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."ID"=1600 AND "T"."BIZ_DATE"='2')
2 - access(ROWID=ROWID)
3 - access("T"."ID"=1600)
4 - access("T"."BIZ_DATE"='2')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2059 consistent gets
2053 physical reads
0 redo size
329 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> select t.id,t.biz_date
2 from scott.t_group1 t
3 where t.biz_date='2' and t.id=1600;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1686682138
--------------------------------------------------------------------------------
--------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
me |
--------------------------------------------------------------------------------
--------
| 0 | SELECT STATEMENT | | 5 | 45 | 21 (0)| 00
:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T_GROUP1 | 5 | 45 | 21 (0)| 00
:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ID | 17 | | 3 (0)| 00
:00:01 |
--------------------------------------------------------------------------------
--------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."BIZ_DATE"='2')
2 - access("T"."ID"=1600)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
329 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> select t.id,t.biz_date
2 from scott.t_group1 t
3 where t.biz_date='2' and t.id=1600;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1686682138
--------------------------------------------------------------------------------
--------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
me |
--------------------------------------------------------------------------------
--------
| 0 | SELECT STATEMENT | | 5 | 45 | 22 (0)| 00
:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T_GROUP1 | 5 | 45 | 22 (0)| 00
:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ID | 18 | | 3 (0)| 00
:00:01 |
--------------------------------------------------------------------------------
--------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."BIZ_DATE"='2')
2 - access("T"."ID"=1600)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
329 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> select /*+index_join(t idx_id,idx_biz_date)*/t.id,t.biz_date
2 from scott.t_group1 t
3 where t.biz_date='1111' and t.id=1600;
Execution Plan
----------------------------------------------------------
Plan hash value: 4285586066
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 1 | 9 | 21 (5)| 00:
00:01 |
|* 1 | VIEW | index$_join$_001 | 1 | 9 | 21 (5)| 00:
00:01 |
|* 2 | HASH JOIN | | | | |
|
|* 3 | INDEX RANGE SCAN| IDX_ID | 1 | 9 | 3 (0)| 00:
00:01 |
|* 4 | INDEX RANGE SCAN| IDX_BIZ_DATE | 1 | 9 | 17 (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."ID"=1600 AND "T"."BIZ_DATE"='1111')
2 - access(ROWID=ROWID)
3 - access("T"."ID"=1600)
4 - access("T"."BIZ_DATE"='1111')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
1 physical reads
0 redo size
468 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ full(t)*/ id,biz_date from scott.t_group1 t where t.id=1600 and t.biz_date='1111';
Execution Plan
----------------------------------------------------------
Plan hash value: 4029886076
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 5577 (3)| 00:01:07 |
|* 1 | TABLE ACCESS FULL| T_GROUP1 | 1 | 9 | 5577 (3)| 00:01:07 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."ID"=1600 AND "T"."BIZ_DATE"='1111')
【小结】 select t.id,t.biz_date from scott.t_group1 t where t.biz_date='1111' and t.id=1600;
1.全表扫描的COST为5577
2.单索引访问(TABLE ACCESS BY INDEX ROWID的代价为索引代价的7倍)
|* 1 | TABLE ACCESS BY INDEX ROWID| T_GROUP1 cost: 22
|* 2 | INDEX RANGE SCAN | IDX_ID cost: 3
3.INDEX_JOIN（INDEX_JOIN的代价很小）
|* 1 | VIEW | index$_join$_001 |cost: 21
|* 2 | HASH JOIN
|* 3 | INDEX RANGE SCAN| IDX_ID |cost: 3
|* 4 | INDEX RANGE SCAN| IDX_BIZ_DATE |cost: 17
4.INDEX_JOIN的使用场景小结
1）每个索引的COST要远远小于全表扫描
2）执行连接的两个索引单独执行（INDEX ... SCAN）的COST要相近。只有这样才能使INDEX_JION的效率高于单个索引扫描后再通过ROWID访问表。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7901922/viewspace-1060027/ ，如需转载，请注明出处，否则将追究法律责任。
