| The differential between Index hint and no hint [message #634013] |
Mon, 02 March 2015 20:46  |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Good morning,
Today, I have got a case make me wonder. The SQL cause I/O scan, when I break them in lines, I saw something especial, some sub-query chosen by index hint.
sql> select /*+ index(um IDX_pos_product_cat_um2)*/ pc.id
, nvl(um.base_um_product_cat_id, pc.id) bpc_id
, nvl(um.base_um_rate, 1) br
from CLOUD_369.pos_product_cat pc
,CLOUD_369.pos_product_cat_um um
where pc.id=um.link_um_product_cat_id(+)
See at the sql statistics
Execution Plan
----------------------------------------------------------
Plan hash value: 1316796469
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27990 | 519K| 2264 (1)| 00:00:28 |
|* 1 | HASH JOIN OUTER | | 27990 | 519K| 2264 (1)| 00:00:28 |
| 2 | INDEX FAST FULL SCAN | PK_POS_PRODUCT_CAT | 27990 | 136K| 16 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| POS_PRODUCT_CAT_UM | 27416 | 374K| 2246 (1)| 00:00:27 |
| 4 | INDEX FULL SCAN | IDX_POS_PRODUCT_CAT_UM2 | 27416 | | 63 (2)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("PC"."ID"="UM"."LINK_UM_PRODUCT_CAT_ID"(+))
and when I remove the hint, I saw
sql> select pc.id
2 , nvl(um.base_um_product_cat_id, pc.id) bpc_id
3 , nvl(um.base_um_rate, 1) br
4 from CLOUD_369.pos_product_cat pc
5 ,CLOUD_369.pos_product_cat_um um
6 where pc.id=um.link_um_product_cat_id(+)
7 /
Execution Plan
----------------------------------------------------------
Plan hash value: 3010009056
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27990 | 519K| 74 (5)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 27990 | 519K| 74 (5)| 00:00:01 |
| 2 | INDEX FAST FULL SCAN| PK_POS_PRODUCT_CAT | 27990 | 136K| 16 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | POS_PRODUCT_CAT_UM | 27416 | 374K| 56 (2)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("PC"."ID"="UM"."LINK_UM_PRODUCT_CAT_ID"(+))
2 differents of COST, one with hint cause 2264 and the other by 74. The execution time was 0.28s vs 0.1s.
May you explain to me?
Thank you!
|
|
|
|
|
|
|
|
| Re: The differential between Index hint and no hint [message #634026 is a reply to message #634013] |
Tue, 03 March 2015 01:46  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
In your first plan, you have an INDEX FULL SCAN followed by (as you work up the plan) TABLE ACCESS BY INDEX ROWID of POS_PRODUCT_CAT_UM. This is an iterative process: for every key retrieved by the index scan, Oracle must find the matching row. It has to do this 27416 times. In the second plan, you simply scan the whole table in one operation. Since you want every row POS_PRODUCT_CAT_UM, the table scan is far more efficient than flying all over the place with thousands of index lookups.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
