Re: Tuning a Query

1)How selective is TABLE_B.MEASURE_ID? And many rows has 'FORECAST'? You may try to create an index on TABLE_B.MEASURE_ID 2)How many rows of Table_A.c1? if it is selective, you may want to force usung the PK on TABLE_B. You can accomplish it by using the hint.
3)How many rows of Table_B.c3? if it is selective, you may want to force usung the PK on TABLE_C. You can accomplish it by using the hint.

Let me know if it works for you. You can send email to me, I am not a frequent user here

Sean Bu

oraskm_at_yahoo.com (Sanjay Mishra) wrote in message news:<eca56b4a.0111202313.742528f2_at_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 - 08:29:27 CST