Re: Table Refresh Slow Reports?

check:

optimizer mode (RULE, COST, or CHOOSE)
statistics
explain plan

"fooguy" <jweisen_at_my-deja.com> wrote in message news:8o63p3$qt1$1_at_nnrp1.deja.com...
> I think I've lost the last piece of my mind.
>
> Every night we refresh a table we use for reporting. When our financial
> analyst comes in in the morning, if he runs the report, it takes ~3
> minutes. If I run the same SQL script I run at night to drop and
> recreate the table, his report takes 20-40 minutes. Does anyone have any
> ideas or suggestions?
>
> Thanks in advance.
>
> CONNECT ADMARC/PASSWORD;
>
> DROP TABLE WO_SLS;
>
> CREATE TABLE WO_SLS(
> ACCT_KEY NUMBER(9) DEFAULT 0
> ,PUB CHAR(4) DEFAULT ' '
> ,REF_NBR NUMBER(8) DEFAULT 0
> ,JOB_NBR NUMBER(9) DEFAULT 0
> ,ADJ_NBR NUMBER(4) DEFAULT 0
> ,INVC_NBR NUMBER(9) DEFAULT 0
> ,ENTRY_NBR NUMBER(1) DEFAULT 0
> ,SLS_NBR NUMBER(4) DEFAULT 0
> ,SLS_PCT NUMBER(4) DEFAULT 0)
> TABLESPACE USERS06
> STORAGE(INITIAL 1M
> NEXT 500K
> PCTINCREASE 0
> MINEXTENTS 1
> MAXEXTENTS 120);
>
> INSERT INTO WO_SLS
> SELECT ACCT_KEY,PUB,REF_NBR,JOB_NBR,ADJ_NBR,INVC_NBR,1,
> SLS_NBR_1,SLS_PCT_1 FROM WO WHERE WO.ISSUE >= '01-JAN-98';
>
> INSERT INTO WO_SLS
> SELECT ACCT_KEY,PUB,REF_NBR,JOB_NBR,ADJ_NBR,INVC_NBR,2,
> SLS_NBR_2,SLS_PCT_2 FROM WO WHERE WO.ISSUE >= '01-JAN-98';
>
> INSERT INTO WO_SLS
> SELECT ACCT_KEY,PUB,REF_NBR,JOB_NBR,ADJ_NBR,INVC_NBR,3,
> SLS_NBR_3,SLS_PCT_3 FROM WO WHERE WO.ISSUE >= '01-JAN-98';
>
> CREATE UNIQUE INDEX "ADMARC"."AAS_WO_SLS" ON "ADMARC"."WO_SLS"
> ("ACCT_KEY", "ADJ_NBR", "ENTRY_NBR", "JOB_NBR") TABLESPACE "AASINDX02"
> PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 1024K NEXT 500K
> MINEXTENTS 1 MAXEXTENTS 120 PCTINCREASE 1);
>
> --
> *********************************************
> "All I every wanted from life was to see
> Larry Wall give Bill Gates a Perl Necklace."
>
> /*
> John Eisenschmidt
> fooguy AT AT AT eisenschmidt DOT DOT DOT org
> (you know the drill)
> */
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Received on Sat Aug 26 2000 - 16:27:21 CDT