SQL Tuning
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: SQL Tuning

  1. #1
    Join Date
    Apr 2002
    Posts
    291

    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

  2. #2
    Join Date
    Jan 2001
    Posts
    642
    Try using EXISTS instead of IN

    Badrinath
    There is always a better way to do the things.

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  4. #4
    Join Date
    Apr 2002
    Posts
    291
    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

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  6. #6
    Join Date
    Apr 2002
    Posts
    291
    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

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  8. #8
    Join Date
    Apr 2002
    Posts
    291
    No Abhay,
    FACILITY is the leading COL in PK.

    Thanks
    PNRDBA

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  10. #10
    Join Date
    Apr 2002
    Posts
    291
    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
  •  



Click Here to Expand Forum to Full Width