Tuning a Query

From: Sanjay Mishra <oraskm_at_yahoo.com>
Date: 20 Nov 2001 23:13:11 -0800
Message-ID: <eca56b4a.0111202313.742528f2@posting.google.com>

I am having difficulty in tuning the following query. It takes more than one hour to insert 1.4 million rows.

INSERT INTO TABLE_O (c1, c2, c3, c4, c5, c6) SELECT
TABLE_A.c1 , TABLE_A.c2 , TABLE_B.MEASURE_ID , TABLE_C.BUCKET_ID , TABLE_A.QUANTITY , TABLE_A.partition_id FROM
TABLE_C , TABLE_B , TABLE_A
WHERE TABLE_A.c2 = TABLE_B.c2

AND TABLE_A.c1  = TABLE_B.c1 
AND TABLE_B.c3  = TABLE_C.c3 
AND TABLE_B.MEASURE_ID  = 'FORECAST' 
AND TABLE_C.START_DATE  = TABLE_A.START_DATE 
AND TABLE_C.END_DATE  = TABLE_A.END_DATE 
AND TABLE_C.BUCKET_ID  = ( SELECT 

(bs.BUCKET_ID )

FROM
TABLE_C bs , TABLE_D tp
WHERE tp.FC_CURRENT < bs.END_DATE
AND tp.FC_CURRENT >= bs.START_DATE )

The OPTIMIZER_MODE = CHOOSE. I have analyzed all the tables using COMPUTE. Number of records in the tables:

TABLE_C : 168
TABLE_B : 2935956
TABLE_A : 17615736
TABLE_D : 1
TABLE_O : 3019096(prior to INSERT)

Number of records expected to be inserted: 1467978

Each of these tables (except TABLE_D) has just one index, the primary key, and they are:

TABLE_C : (c3, BUCKET_ID)
TABLE_B : (c1, c2, MEASURE_ID)
TABLE_A : (c1, c2, START_DATE)
TABLE_D : No primary key. No index.
TABLE_O : (c1, c2, MEASURE_ID, BUCKET_ID)

The explain plan is:
INSERT STATEMENT Cost = 7381
  FILTER
    HASH JOIN

      NESTED LOOPS
        TABLE ACCESS FULL TABLE_C
        TABLE ACCESS FULL TABLE_A
      TABLE ACCESS FULL TABLE_B
    NESTED LOOPS
      TABLE ACCESS FULL TABLE_D 
      TABLE ACCESS FULL TABLE_C
      

I am surprised to see that all the tables are being accessed in FULL TABLE SCANS. I tried to use a hint to use the index on the largest table, i.e., TABLE_A. The new explain plan is:

INSERT STATEMENT Cost = 1196
  FILTER
    HASH JOIN

      NESTED LOOPS
        TABLE ACCESS FULL TABLE_C
        TABLE ACCESS BY INDEX ROWID TABLE_A
          INDEX FULL SCAN PK_TABLE_A
      TABLE ACCESS FULL TABLE_B
    NESTED LOOPS
      TABLE ACCESS FULL TABLE_D
      TABLE ACCESS FULL TABLE_C      

Even though the cost has reduced significantly, there was hardly any improvement in the running time of the query.

I have tried using different ordering of the tables in the FROM clause,
and different ordering of conditions in the WHERE clause, but of no avail.
(I believe that these ordering doesn't matter in CBO, anyway).

I tried using ORDERED and FIRST_ROWS hints as well, but of no avail.

Any suggestion to improve the performance of the query is highly appreciated.

Thanks, Received on Wed Nov 21 2001 - 01:13:11 CST