-
SQL Tuning
Hi All,
I have a resource intensive query which runs very frequently from the application server.Following is the query and it's execution plan. Could you please help me to tune this further or alternate execution plans which is not a resource intensive..
SELECT facility, claimno, patlname, patfname, patmname, to_char(admitdt,
'mm/dd/yyyy'), to_char(dischdt, 'mm/dd/yyyy'), patssn, ins1policy,
ins2policy, ins3policy, ins4policy, ins5policy, ins6policy, claimacctbal,
insstate
FROM d_claim
WHERE facility IN (SELECT facility
FROM c_facilitygroup
WHERE facgrpnum = 3500)
AND upper(patfname) LIKE '%JANEL%'
AND upper(patlname) LIKE '%ELMORE%'
=============================================
EXECUTION PLAN:
=============================================
Optimizer Mode Used:
COST ALL ROWS (optimizer: CHOOSE)
Total Cost:
4,784
Execution Steps:
Step # Step Name
6 SELECT STATEMENT
5 NESTED LOOPS
2 SORT [UNIQUE]
1 CMS.C_FACILITYGROUP_PRI INDEX [RANGE SCAN]
4 PARTITION RANGE [ITERATOR]
3 CMS.D_CLAIM TABLE ACCESS [FULL]
Step # Description Est. Cost Est. Rows
Returned
Est. KBytes
Returned
1 This plan step retrieves one or more ROWIDs in ascending order by scanning
the B*-tree index C_FACILITYGROUP_PRI.
2 2 0.016
2 This plan step accepts a row set (its only child) and sorts it in order to identify
and eliminate duplicates.
-- -- --
3 This plan step retrieves all rows from table D_CLAIM. 2,391 49 4.354
4 This plan step determines the approach to iterating over partitions of a
range-partitioned table.
5 This plan step joins two sets of rows by iterating over the driving, or outer, row
set (the first child of the join) and, for each row, carrying out the steps of the
inner row set (the second child). Corresponding pairs of rows are tested
against the join condition specified in the query's WHERE clause.
4,784 82 7.928
6 This plan step designates this statement as a SELECT statement. 4,784 -- --
Many thanks in advance
PNRDBA
-
Try using EXISTS instead of IN
Badrinath
There is always a better way to do the things.
-
Code:
SELECT
DC.FACILITY,
DC.CLAIMNO,
DC.PATLNAME,
DC.PATFNAME,
DC.PATMNAME,
TO_CHAR(DC.ADMITDT,
'MM/DD/YYYY'),
TO_CHAR(DC.DISCHDT,
'MM/DD/YYYY'),
DC.PATSSN,
DC.INS1POLICY,
DC.INS2POLICY,
DC.INS3POLICY,
DC.INS4POLICY,
DC.INS5POLICY,
DC.INS6POLICY,
DC.CLAIMACCTBAL,
DC.INSSTATE
FROM
D_CLAIM DC ,
C_FACILITYGROUP CFG
WHERE
CFG.FACGRPNUM = 3500 AND
DC.FACILITY = CFG.FACILITY AND
UPPER(DC.PATFNAME) LIKE '%JANEL%' AND
UPPER(DC.PATLNAME) LIKE '%ELMORE%'
And index for DC table on Facility, UPPER(PATFNAME), UPPER(PATLNAME).
Now, while the UPPER parts of the index would not help in this particular query, I'm hoping that you do not always do 'contains' functionality and hopefully do more 'starts with' or 'equals' functionality, where that part of the index would be more useful.
- Chris
-
Hi Chris,
Thanks for your reply. But DC had already index on Facility and its a primary key too, positioned #1. PATLNAME and PATFNAME are also having indexes. Is there any other way round to get the lower cost...
Thanks a lot
PNRDBA
-
Is that index a function-based index as I specified? Again, it won't help the currect query, but it will likely help others. In general, 'contains' functionality should be avoided or restricted where possible.
And what do you mean by 'facility is a PK too'? It might be *part* of the PK in the DC table, but that's the best I see. Can you please provide the relevant indexes, because your plan shows that an index is not being used. If Facility_ID were indexed, then my version of the statement would use it? Can you also provide the actual plan using my statement?
- Chris
-
Chris,
Here is the actual plan with your version.You are correct, FACILITY is a composite primary key with CLAIMNO.
SELECT STATEMENT Cost = 4784
NESTED LOOPS
INDEX RANGE SCAN C_FACILITYGROUP_PRI
PARTITION RANGE ITERATOR
TABLE ACCESS FULL D_CLAIM
Here are the indexes:
CLM_HCFA
D_CLAIM_PRI
INDX_D_CLAIMACCT
INDX_D_CLAIMASSIGNEEUSERID
INDX_D_CLAIMCLNTSYS
INDX_D_CLAIMFCCODE
INDX_D_CLAIMINSCODE
INDX_D_CLAIMLFUSERID
INDX_D_CLAIMOWNERUSERID
INDX_D_CLAIMPROJECTCODE
INDX_D_CLAIMSERVICECODE
Thanks
PNRDBA
-
Originally posted by pnrdba
Chris,
Here is the actual plan with your version.You are correct, FACILITY is a composite primary key with CLAIMNO.
Thanks
'CLAIMNO' is this COL the leading COL in the composite PK Index?
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
No Abhay,
FACILITY is the leading COL in PK.
Thanks
PNRDBA
-
Originally posted by pnrdba
No Abhay,
FACILITY is the leading COL in PK.
Thanks
Either, CARD of this COL is too low and table contents are too high...& u r using BTREE index.
Or
CLAIMNO has High CARD compared to FACILITY & Number of rows in D_CLAIM are too many compared to C_FACILITYGROUP.
TRY indexing only on COL FACILITY & choose appropriate index depending on CARD.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Thanks abhay,
I'll try for that....
PNRDBA
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|