SQL Tuning

[pnrdba]

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

[badrinathn]

Try using EXISTS instead of IN

Badrinath

[chrisrlong]

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

[pnrdba]

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

[chrisrlong]

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

[pnrdba]

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

[abhaysk]

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?

[pnrdba]

No Abhay,
FACILITY is the leading COL in PK.

Thanks

[abhaysk]

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.

[pnrdba]

Thanks abhay,
I'll try for that....