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.

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.