How to optimize performance for this table scan for given query .

[puneetkala]

Hi ,
We have a table which have following columns

CREATE TABLE "STERLINGUSER"."LABS_DC_CARRIER_TRANSIT_TIME"
(
"DC_CARRIER_TRANSIT_TIME_KEY" CHAR(24 BYTE) DEFAULT ' ' NOT NULL ENABLE,
"TENANT_ID" CHAR(24 BYTE) DEFAULT ' ' NOT NULL ENABLE,
"DISTRIBUTOR_ID" CHAR(24 BYTE) DEFAULT ' ' NOT NULL ENABLE,
"CARRIER_METHOD_ID" VARCHAR2(40 BYTE) DEFAULT ' ' NOT NULL ENABLE,
"START_ZIP" VARCHAR2(35 BYTE) DEFAULT ' ' NOT NULL ENABLE,
"END_ZIP" VARCHAR2(35 BYTE) DEFAULT ' ' NOT NULL ENABLE,
"DAYS_IN_TRANSIT" NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE,

)



Where DC_CARRIER_TRANSIT_TIME_KEY is primary key for the table. The data in the table looks like
Tablesnap.jpg
We have around 8 million records in this table . and we query this table using range based query which is like this

SELECT LABS_DC_CARRIER_TRANSIT_TIME.* FROM LABS_DC_CARRIER_TRANSIT_TIME LABS_DC_CARRIER_TRANSIT_TIME WHERE ( ( ( LABS_DC_CARRIER_TRANSIT_TIME.TENANT_ID = 'FLIPKART.COM' ) AND ( LABS_DC_CARRIER_TRANSIT_TIME.START_ZIP <= '98101' ) AND ( LABS_DC_CARRIER_TRANSIT_TIME.END_ZIP >= '98101' ) ) )


and presently this query is taking around 700 mili seconds. Please suggest what indexs we should put on this table and how we can optimize the performance for this range scan. TENANT_ID is same presently for all 8 million records.

Thanks

[gandolf989]

You might want to do a hash partition on the tenant_id table, with a local index on start_zip, end_zip.
You can also make sure that you have an index that filters based on tenant_id first followed by
start_zip and end_zip. This should cause Oracle to do a range scan for the part of the index where
tenant_id = :1 and start_zip <= :2 and end_zip >= :3. The key to tuning a query with equality and
greater than and less than is to make the range scan look at as few rows as possible.

CREATE INDEX LABS_DC_CARRIER_TRANSIT_TIME_007 ON LABS_DC_CARRIER_TRANSIT_TIME ( TENANT_ID, START_ZIP, END_ZIP)

If you have a specific list of tenant_id's, you can also try list partitioning which could create a partition
for each tenant_id, thus querying for a particular would only look at one partition, with only that
tenant_id's data.