| Help with problem query [message #488177] |
Wed, 05 January 2011 08:59  |
indrajit2002
Messages: 53
Registered: November 2007
Location: INDIA
|
Member |
|
|
Hi,
I have a query that gives output after running for over 1 hour.We need to tune the query. Please help on whether any hint will be useful.Here is the query,
SELECT
A.ICD_CODE AS ICD_CODE,
A.ICD_DESC AS ICD_DESC,
B.COMPL_ICD_CODE AS COMPL_ICD_CODE,
B.COMPL_GRP_TXT AS COMPL_GRP_TXT,
C.PROC_TYPE AS PROC_TYPE ,
C.I_O_IND AS I_O_IND,
C.DISC_MON AS QUARTER ,
B.PAT_KEY AS PAT_KEY ,
D.COMPL_TYPE_TXT AS COMPL_TYPE_TXT ,
C.PROV_ID AS PROV_ID ,
A.SPECIALTY AS SPECIALTY
FROM
EES_ICD_9_CODE A ,
EES_CLINICAL_COMPL_DATA1 B,
EES_CLINICAL_DATA1 C ,
EES_CLINCL_COMPL_ICD D
WHERE A.ICD_CODE= B.ICD_CODE
AND B.ICD_CODE= C.ICD_CODE
AND A.ICD_CODE=D.ICD_9_CD
AND B.PAT_KEY=C.PAT_KEY
AND B.COMPL_GRP_TXT<>'Reoperations'
AND D.COMPL_TYPE_TXT<>'Intra-operative Misadventure'
AND A.SPECIALTY='GYN'
And here is the explain plan
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11107 | 1431K| 18802 |
|* 1 | TABLE ACCESS BY INDEX ROWID | EES_CLINCL_COMPL_ICD | 1 | 21 | 1 |
| 2 | NESTED LOOPS | | 11107 | 1431K| 18802 |
| 3 | NESTED LOOPS | | 10814 | 1172K| 18586 |
| 4 | NESTED LOOPS | | 878K| 67M| 1007 |
|* 5 | TABLE ACCESS FULL | EES_ICD_9_CODE | 21 | 903 | 2 |
|* 6 | TABLE ACCESS BY INDEX ROWID | EES_CLINICAL_COMPL_DATA1 | 42873 | 1590K| 1007 |
| 7 | BITMAP CONVERSION TO ROWIDS| | | | |
|* 8 | BITMAP INDEX SINGLE VALUE | IND_ICDCD | | | |
|* 9 | TABLE ACCESS BY INDEX ROWID | EES_CLINICAL_DATA1 | 1 | 30 | 1 |
|* 10 | INDEX RANGE SCAN | IND_ICD_CD | 16800 | | 1 |
|* 11 | INDEX RANGE SCAN | ICD_CODE_INDEX_2 | 1 | | 1 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("D"."COMPL_TYPE_TXT"<>'Intra-operative Misadventure')
5 - filter("A"."SPECIALTY"='GYN')
6 - filter("B"."COMPL_GRP_TXT"<>'Reoperations')
8 - access("A"."ICD_CODE"="B"."ICD_CODE")
9 - filter("B"."PAT_KEY"="C"."PAT_KEY")
10 - access("B"."ICD_CODE"="C"."ICD_CODE")
11 - access("A"."ICD_CODE"="D"."ICD_9_CD")
Note: cpu costing is off
30 rows selected.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Help with problem query [message #488396 is a reply to message #488185] |
Fri, 07 January 2011 01:44  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
On table EES_CLINICAL_DATA1, I am betting that PAT_KEY is highly selective and ICD_CODE is not very selective (Explain Plan reckons there are 17K rows per value!).
Yet it chooses to use the non-selective index. Either:
- PAT_KEY is not indexed, or
- The index on PAT_KEY is not analyzed.
Make sure an index exists with PAT_KEY as the 1st column. Ideally, make ICD_CODE the second column.
As an alternative approach...
It looks as though your filter criteria are not very selective. Would you say that the 3 filter predicates filter less then 90% of all rows (i.e. more than 10% remain)?
AND B.COMPL_GRP_TXT<>'Reoperations'
AND D.COMPL_TYPE_TXT<>'Intra-operative Misadventure'
AND A.SPECIALTY='GYN'
If so, a hash join approach might be preferable. Try the following for comparison:
SELECT /*+ ordered use_hash(a b c d) */
A.ICD_CODE AS ICD_CODE,
A.ICD_DESC AS ICD_DESC,
B.COMPL_ICD_CODE AS COMPL_ICD_CODE,
B.COMPL_GRP_TXT AS COMPL_GRP_TXT,
C.PROC_TYPE AS PROC_TYPE ,
C.I_O_IND AS I_O_IND,
C.DISC_MON AS QUARTER ,
B.PAT_KEY AS PAT_KEY ,
D.COMPL_TYPE_TXT AS COMPL_TYPE_TXT ,
C.PROV_ID AS PROV_ID ,
A.SPECIALTY AS SPECIALTY
FROM
EES_ICD_9_CODE A ,
EES_CLINICAL_COMPL_DATA1 B,
EES_CLINICAL_DATA1 C ,
EES_CLINCL_COMPL_ICD D
WHERE A.ICD_CODE= B.ICD_CODE
AND B.ICD_CODE= C.ICD_CODE
AND A.ICD_CODE=D.ICD_9_CD
AND B.PAT_KEY=C.PAT_KEY
AND B.COMPL_GRP_TXT<>'Reoperations'
AND D.COMPL_TYPE_TXT<>'Intra-operative Misadventure'
AND A.SPECIALTY='GYN'
If your filter predicates really are just leaving with a tiny proportion of available rows, you need to find out why an index on C.PAT_KEY is not being used.
Ross Leishman
|
|
|
|
| Re: Help with problem query [message #488518 is a reply to message #488396] |
Sat, 08 January 2011 10:01 |
indrajit2002
Messages: 53
Registered: November 2007
Location: INDIA
|
Member |
|
|
Hi Ross,
Sorry for replying late but your suggestion has really helped me a lot. I executed the query with the hint that you have provided and it took only 41 seconds to run. It was really unbeleivable.
Thanks a lot for all your help.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
