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)