How to optimize performance for this table scan for given query .
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

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

  1. #1
    Join Date
    Jun 2013
    Posts
    1

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    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.
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width