Please help me tuning the query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Please help me tuning the query

  1. #1
    Join Date
    Feb 2001
    Posts
    34

    Please help me tuning the query

    The below query needs to be tuned for better performance. The tables has pk and a index . If anybody have any ideas about any more indexing ,etc which may help the query to run faster please let me know.



    SELECT tjc_id
    FROM zip4_tjc
    WHERE state_name = 'STATE'
    AND zip_start <= 61601
    AND (zip_end >= 61601 OR zip_end IS NULL)
    AND zip4_start <= 2429
    AND (zip4_end >= 2429 OR zip4_end IS NULL);

    The index is create on state_name,zip_start,zip_end,zip4_start and zip4_end.

    The explain plan is given below

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=770 Card=1 Bytes=28)
    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'SSTI_ZIP4_TJC' (Cost=770
    Card=1 Bytes=28)

    2 1 INDEX (RANGE SCAN) OF 'IND_SSTI_ZIP4_TJC' (NON-UNIQUE) (
    Cost=682 Card=302)

  2. #2
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    The index could be making things worse rather than better. With the conditions you've got and the fact that you are doing range scans rather than equality conditions, the only bit of the index which may be used is:

    WHERE state_name = 'STATE'
    AND zip_start <= 61601

    Is this a large proportion of the table (e.g. 50%+) ? If so, the index would be make the query worse rather than better. Make sure you have analyzed your table fully. Do an "analyze table ... for all columns" to get the histogram data to avoid a skewed index being used.
    Last edited by Scorby; 01-10-2006 at 11:26 AM. Reason: Add state to condition

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Have you compared the performance using the index with performance when using a full table scan?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Jan 2006
    Posts
    1
    Hi!

    Could you try rebuilding the index using tjc_id?
    Put in in end.
    I mean:
    Create index xpto on zip4_tjc (...,...,...,tjc_id).
    This way you wouldn't need to go back to the table, you'll have all the information you need on the index.

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