Newbie: Query optimization

From: Jon Waterhouse <jonwaterhouse_at_gov.nf.ca>
Date: Tue, 4 Jun 2002 10:03:46 -0230
Message-ID: <3cfcb43d@news.mhogaming.com>

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)

CREATE TABLE CASEFILE (

  COMDAT     DATE,
  FIL        NUMBER (6)    NOT NULL,

  LOCSTART DATE,
  COMPSTART DATE,
  CONSTRAINT PK_CASEFILE
  PRIMARY KEY ( FIL ) ; CREATE INDEX IX_FIL_COMPSTART ON
  CASEFILE(FIL, COMPSTART)
  TABLESPACE USER_DATA PCTFREE 10 STORAGE(INITIAL 10240 NEXT 645120 PCTINCREASE 50 )
;
CREATE TABLE CASECOMP (

  FIL        NUMBER        NOT NULL,
  DATESTART  DATE          NOT NULL,
  DATEEND    DATE,
  NOALS      NUMBER,
  NOCLS      NUMBER,
  FAMSTS     NUMBER,

  PRIMARY KEY ( FIL, DATESTART ) ; ALTER TABLE CASECOMP ADD CONSTRAINT FK_CASECOMP_CASEFILE  FOREIGN KEY (FIL)
  REFERENCES JONWATERHOUSE.CASEFILE (FIL) ; CREATE TABLE RAWSOURCE (

  CONTDAY1  NUMBER (5),
  CONTDAY2  NUMBER (5),
  CONTDT    NUMBER (5),
  SUSPEND   NUMBER (3),
  COMDAT    NUMBER (9),
  SUSDATE   NUMBER (8),
  REODATE   NUMBER (8),
  REASUS    NUMBER (5),
  KIVDAT    NUMBER (5),
  LTAPU     NUMBER (5),
  STAPU     NUMBER (5),
  FIL       NUMBER (8),
  WELDIS    NUMBER (5),
  SETTNUM   NUMBER (5),
  CAS       CHAR (3),
  ELECDIST  NUMBER (5),
  NOALS     NUMBER (3),
  NOCLS     NUMBER (3),
  FAMSTS    NUMBER (3),
  ACCOM     NUMBER (3),
  BENH      CHAR (2),
  EDUH      NUMBER (3),
  OCCH      NUMBER (3),
  EMPH      NUMBER (3),
  EDUS      NUMBER (3),
  OCCS      NUMBER (3),
  EMPS      NUMBER (3),
  BRTHH     NUMBER (9),
  SEXH      CHAR (1),
  BENS      CHAR (2),
  BRTHS     NUMBER (9),
  SEXS      CHAR (1),
  BEN3      CHAR (2),
  SEXB3     CHAR (1),
  BRTHB3    NUMBER (9),
  NOBENCH   NUMBER (5),
  MONPAY    NUMBER (7,2),
  TOTPMTS   NUMBER (7,2),
  TOTINC    NUMBER (7,2),
  KID0_4    NUMBER (3),
  KID5_9    NUMBER (3),
  KID10_12  NUMBER (3),

  KID13_14 NUMBER (3),
  KID_15P NUMBER (3),
  POSTCODE CHAR (6),

  SINH      NUMBER (9),
  SINS      NUMBER (9),
  AGECH01   NUMBER (3),

  SEXCH01 CHAR (1),
  BDCH01 NUMBER (9),
  AGECH02 NUMBER (3),
  SEXCH02 CHAR (1),
  BDCH02 NUMBER (9),
  AGECH03 NUMBER (3),
  SEXCH03 CHAR (1),
  BDCH03 NUMBER (9),
  AGECH04 NUMBER (3),
  SEXCH04 CHAR (1),
  BDCH04 NUMBER (9),
  AGECH05 NUMBER (3),
  SEXCH05 CHAR (1),
  BDCH05 NUMBER (9),
  AGECH06 NUMBER (3),
  SEXCH06 CHAR (1),
  BDCH06 NUMBER (9),
  AGECH07 NUMBER (3),
  SEXCH07 CHAR (1),
  BDCH07 NUMBER (9),
  AGECH08 NUMBER (3),
  SEXCH08 CHAR (1),
  BDCH08 NUMBER (9),
  AGECH09 NUMBER (3),
  SEXCH09 CHAR (1),
  BDCH09 NUMBER (9),
  AGECH10 NUMBER (3),
  SEXCH10 CHAR (1),
  BDCH10 NUMBER (9),
  AGECH11 NUMBER (3),
  SEXCH11 CHAR (1),
  BDCH11 NUMBER (9),
  AGECH12 NUMBER (3),
  SEXCH12 CHAR (1),

  BDCH12    NUMBER (9),
  TOTDEDIN  NUMBER (7,2),
  ARCODE1   NUMBER (3),
  AROPENB1  NUMBER (7,2),
  AROPEND1  NUMBER (9),
  ARCURRB1  NUMBER (7,2),
  ARCODE2   NUMBER (3),
  AROPENB2  NUMBER (7,2),
  AROPEND2  NUMBER (9),
  ARCURRB2  NUMBER (7,2),
  ARCODE3   NUMBER (3),
  AROPENB3  NUMBER (7,2),
  AROPEND3  NUMBER (9),
  ARCURRB3  NUMBER (7,2),
  ARCODE4   NUMBER (3),
  AROPENB4  NUMBER (7,2),
  AROPEND4  NUMBER (9),
  ARCURRB4  NUMBER (7,2),
  ARCODE5   NUMBER (3),
  AROPENB5  NUMBER (7,2),
  AROPEND5  NUMBER (9),
  ARCURRB5  NUMBER (7,2),
  ARCODE6   NUMBER (3),
  AROPENB6  NUMBER (7,2),
  AROPEND6  NUMBER (9),
  ARCURRB6  NUMBER (7,2),
  ARCODE7   NUMBER (3),
  AROPENB7  NUMBER (7,2),
  AROPEND7  NUMBER (9),
  ARCURRB7  NUMBER (7,2),
  ARCODE8   NUMBER (3),
  AROPENB8  NUMBER (7,2),
  AROPEND8  NUMBER (9),
  ARCURRB8  NUMBER (7,2),
  ARCODE9   NUMBER (3),
  AROPENB9  NUMBER (7,2),
  AROPEND9  NUMBER (9),
  ARCURRB9  NUMBER (7,2),
  TOTAR     NUMBER (9,2),
  INDARCRE  NUMBER (9,2),
  CURRCAMT  NUMBER (9,2),

  PRIMARY KEY ( FIL )); CREATE OR REPLACE TRIGGER casefile_casecomp_trig   BEFORE insert or update on casecomp
  FOR each row
begin
  update casefile
  SET compstart = :new.datestart
  where casefile.fil = :new.fil;
end;
/ Received on Tue Jun 04 2002 - 07:33:46 CDT