-
How to optimize performance for this table scan for given query .
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
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
|