-
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)
-
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 12:26 PM.
Reason: Add state to condition
-
Have you compared the performance using the index with performance when using a full table scan?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|