Re: Newbie: Query optimization

On Tue, 4 Jun 2002, jonwaterhouse_at_gov.nf.ca wrote:

> I'm completely new to optimization. The SELECT statement below (which
> is actually part of a cursor definition), is run completely
> differently on 9i (which I have on my own computer for testing
> purposes), and 8i, which is where the real data is. Table definitions
> _and data_ are the same. Is there a hint I can add that will get 8i to
> choose the 9i plan (which looks more efficient and seems to run
> faster)? Thanks,
>
> Jon
>
> SELECT s.fil filno,datestart,dateend,s.noals,s.nocls,s.famsts
> FROM rawsource s,casecomp c,casefile f
> where s.fil=f.fil and c.fil = f.fil and c.datestart = f.compstart AND
> (s.noals=c.noals
> and s.nocls=c.nocls and s.famsts=c.famsts)
>
> Chosen by 9i:
> SELECT STATEMENT Optimizer=CHOOSE (Cost=751 Card=391 Bytes=34017)
> HASH JOIN (Cost=751 Card=391 Bytes=34017)
> HASH JOIN (Cost=111 Card=32366 Bytes=1132810)
> INDEX (FAST FULL SCAN) OF IX_FILE_COMPSTART (NON-UNIQUE) (Cost=4
> Card=32407 Bytes=356477)
> TABLE ACCESS (FULL) OF CASECOMP (Cost=24 Card=32407
> Bytes=777768) TABLE ACCESS (FULL) OF RAWSOURCE (Cost=210
> Card=112637 Bytes=5857124)
>
> Chosen by 8i:
> SELECT STATEMENT Optimizer=CHOOSE (Cost=13601 Card=56 Bytes=4704)
> HASH JOIN (Cost=13601 Card=56 Bytes=4704)
> MERGE JOIN (Cost=12930 Card=4849 Bytes=344279)
> SORT (JOIN) (Cost=1098 Card=36524 Bytes=693956)
> TABLE ACCESS (FULL) OF CASECOMP (Cost=98 Card=36524
> Bytes=693956)
> SORT (JOIN) (Cost=11832 Card=132755 Bytes=6903260)
> TABLE ACCESS (FULL) OF RAWSOURCE (Cost=996 Card=132755
> Bytes=6903260)
> INDEX (FAST FULL SCAN) OF IX_FIL_COMPSTART (NON-UNIQUE) (Cost=4
> Card=42334 Bytes=550342)

/*+
use_hash(f)
index(f IX_FILE_COMPSTART)
*/

would probably do it.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.