| Force Subquery To Use index [message #469588] |
Thu, 05 August 2010 04:25  |
Vackar
Messages: 81
Registered: October 2007
|
Member |
|
|
Hi All,
I have the following query:
Select
tag0.TAG_VALUE pid, tag1.TAG_VALUE, tag2.TAG_VALUE From TAGGER.TAGGABLE_RESOURCE r
, TAGGER.TAG tag0
, TAGGER.TAG tag1
, TAGGER.TAG tag2
where 1=1
AND tag0.TAG_TYPE in (4602, 5228)
AND tag1.TAG_TYPE in (4612, 5225)
AND tag2.TAG_TYPE in (4613, 5226)
AND r.ID = tag0.TAGGABLE_RESOURCE_ID
AND r.ID = tag2.TAGGABLE_RESOURCE_ID
AND r.ID = tag1.TAGGABLE_RESOURCE_ID;
This runs in about 400ms. Now I replace this:
AND tag0.TAG_TYPE in (4602, 5228)
AND tag1.TAG_TYPE in (4612, 5225)
AND tag2.TAG_TYPE in (4613, 5226)
with this:
AND tag0.TAG_TYPE in (select COLUMN_VALUE from ( select * from table( TAGGER.GET_IDS_OF_SIMILAR_TAG_TYPES('Patient ID') ) x1 ))
AND tag1.TAG_TYPE in (select COLUMN_VALUE from ( select * from table( TAGGER.GET_IDS_OF_SIMILAR_TAG_TYPES('Patients Sex') )x2 ))
AND tag2.TAG_TYPE in (select COLUMN_VALUE from ( select * from table( TAGGER.GET_IDS_OF_SIMILAR_TAG_TYPES('Patients Birth Date') ) x3 ))
So instead of hard coding the IDs there is a function that looks them up. The function itself is reporting that it runs in 0ms. But when I run the new query:
Select
tag0.TAG_VALUE pid, tag1.TAG_VALUE, tag2.TAG_VALUE From TAGGER.TAGGABLE_RESOURCE r
, TAGGER.TAG tag0
, TAGGER.TAG tag1
, TAGGER.TAG tag2
where 1=1
AND tag0.TAG_TYPE in (select COLUMN_VALUE from ( select * from table( TAGGER.GET_IDS_OF_SIMILAR_TAG_TYPES('Patient ID') ) x1 ))
AND tag1.TAG_TYPE in (select COLUMN_VALUE from ( select * from table( TAGGER.GET_IDS_OF_SIMILAR_TAG_TYPES('Patients Sex') )x2 ))
AND tag2.TAG_TYPE in (select COLUMN_VALUE from ( select * from table( TAGGER.GET_IDS_OF_SIMILAR_TAG_TYPES('Patients Birth Date') ) x3 ))
AND r.ID = tag0.TAGGABLE_RESOURCE_ID
AND r.ID = tag2.TAGGABLE_RESOURCE_ID
AND r.ID = tag1.TAGGABLE_RESOURCE_ID
it takes around 6s to run. I have looked at the explain plans it it seems as though the function based approach is triggering a full table scan of 'TAG'.
I have tried it with query hints to use index, but it doesn't change the execution plan, or the query time.
Does anyone have any advice?
The explain plan for the quick query is:
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 1031492929
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 341 | 26257 | 2980 (1)| 00:00:36 |
|* 1 | HASH JOIN | | 341 | 26257 | 2980 (1)| 00:00:36 |
|* 2 | HASH JOIN | | 1692 | 89676 | 1987 (1)| 00:00:24 |
| 3 | INLIST ITERATOR | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | TAG | 8392 | 196K| 993 (1)| 00:00:12 |
|* 5 | INDEX RANGE SCAN | IDX_FK_TAG_TYPE | 8392 | | 20 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 8392 | 237K| 994 (1)| 00:00:12 |
| 7 | INLIST ITERATOR | | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| TAG | 8392 | 196K| 993 (1)| 00:00:12 |
|* 9 | INDEX RANGE SCAN | IDX_FK_TAG_TYPE | 8392 | | 20 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | TABLE1_PK | 1 | 5 | 0 (0)| 00:00:01 |
| 11 | INLIST ITERATOR | | | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | TAG | 8392 | 196K| 993 (1)| 00:00:12 |
|* 13 | INDEX RANGE SCAN | IDX_FK_TAG_TYPE | 8392 | | 20 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("R"."ID"="TAG2"."TAGGABLE_RESOURCE_ID")
2 - access("R"."ID"="TAG1"."TAGGABLE_RESOURCE_ID")
5 - access("TAG1"."TAG_TYPE"=4612 OR "TAG1"."TAG_TYPE"=5225)
9 - access("TAG0"."TAG_TYPE"=4602 OR "TAG0"."TAG_TYPE"=5228)
10 - access("TAG0"."TAGGABLE_RESOURCE_ID"="R"."ID")
13 - access("TAG2"."TAG_TYPE"=4613 OR "TAG2"."TAG_TYPE"=5226)
And the slow one is:
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2741657371
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Te
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20M| 1602M|
|* 1 | HASH JOIN RIGHT SEMI | | 20M| 1602M|
| 2 | COLLECTION ITERATOR PICKLER FETCH | GET_IDS_OF_SIMILAR_TAG_TYPES | | |
|* 3 | HASH JOIN | | 7491M| 565G|
| 4 | TABLE ACCESS FULL | TAG | 7678K| 175M|
|* 5 | HASH JOIN | | 53M| 2934M|
| 6 | TABLE ACCESS FULL | TAG | 7678K| 175M|
|* 7 | HASH JOIN | | 143M| 4530M|
| 8 | INDEX FAST FULL SCAN | TABLE1_PK | 46989 | 229K|
|* 9 | HASH JOIN | | 169M| 4526M|
| 10 | MERGE JOIN CARTESIAN | | 8168 | 32672 |
| 11 | SORT UNIQUE | | | |
| 12 | COLLECTION ITERATOR PICKLER FETCH | GET_IDS_OF_SIMILAR_TAG_TYPES | | |
| 13 | BUFFER SORT | | 8168 | 16336 |
| 14 | SORT UNIQUE | | | |
| 15 | COLLECTION ITERATOR PICKLER FETCH| GET_IDS_OF_SIMILAR_TAG_TYPES | | |
| 16 | TABLE ACCESS FULL | TAG | 7678K| 175M|
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TAG0"."TAG_TYPE"=VALUE(KOKBF$))
3 - access("TAG0"."TAGGABLE_RESOURCE_ID"="R"."ID")
5 - access("R"."ID"="TAG2"."TAGGABLE_RESOURCE_ID" AND "TAG2"."TAG_TYPE"=VALUE(KOKBF$))
7 - access("R"."ID"="TAG1"."TAGGABLE_RESOURCE_ID")
9 - access("TAG1"."TAG_TYPE"=VALUE(KOKBF$))
|
|
|
|
| Re: Force Subquery To Use index [message #469591 is a reply to message #469588] |
Thu, 05 August 2010 04:33  |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Trouble is oracle has no way of estimating how many rows are going to be returned by the function. you can:
1) use the cardinality hint to help it
2) Rewrite the query to use straight sql instead of the functions.
|
|
|
|
|
|
| Re: Force Subquery To Use index [message #469602 is a reply to message #469595] |
Thu, 05 August 2010 05:17 |
Vackar
Messages: 81
Registered: October 2007
|
Member |
|
|
By running the query without the functions (moving function logic into main query) I was able to get the query to under a second.
It makes the query look less elegant, but it's being generated at the app layer so I'm not worring too much about that.
Thanks for the tips CookieMonster.
|
|
|
|
| Re: Force Subquery To Use index [message #469608 is a reply to message #469602] |
Thu, 05 August 2010 05:28 |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Always be wary of using custom functions in where clauses - they have a habit of causing performance problems.
Either for the reason stated above, or the more usual case of the function being run for every row in a table rather than every row you want returned.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
