Re: Tuning a Query

From: Nuno Souto <nsouto_at_optushome.com.au.nospam>
Date: Wed, 21 Nov 2001 13:32:41 GMT
Message-ID: <3bfba779.15367194@news>

Sanjay Mishra doodled thusly:

>Number of records in the tables:
>
>TABLE_C : 168

vvvvvvvvvvvvvvvvvvv

>TABLE_B : 2935956
>TABLE_A : 17615736

^^^^^^^^^^^^^^^^^^^

above would be prime candidates to hint in the statement. To guarantee that they are using indexes in the join condition if nothing else. But, read on.

vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv

>TABLE_O : 3019096(prior to INSERT)
>
>Number of records expected to be inserted: 1467978

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

this is worrying because of:

>TABLE_O : (c1, c2, MEASURE_ID, BUCKET_ID)

this.
We're talking about adding 50% more rows to an index that is a concat of four columns. That won't be fast, when the 50% is 1.5Mrows. Unless you have some serious metal!

Tried to drop the TABLE_O index? Or do you need it to check dups coming in from this SQL?

For that matter, how long does it take to return rows if you omit the INSERT part and use only the SELECT?

Now, another interesting bit:
>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
>

Go back to your initial SQL, the relevant areas:

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 )

Notice that TABLE_B should be the driving table? Why? Well, it's the only one with a restricting predicate that is not a join condition:

AND TABLE_B.MEASURE_ID = 'FORECAST' which is BTW, not a leading column in the index. So, this table will always be full scanned. Prime candidate for leading table, then use the indexes to join to everything else.

I'd start to hint as follows (after of course trying first with all sorts of extended ANALYZE!):

/*+ ORDERED */ And then change the FROM clause to read like this:

TABLE_B, TABLE_A, TABLE_C That usually does the trick in these cases of mixed columns being used to join and restrict.

Depending on your ORACLE version, even this may not be enough. You may need to change init.ora parameters (or session parameters) to force it to not full scan.

Just a few ideas to try out.

Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam Received on Wed Nov 21 2001 - 07:32:41 CST