Re: Table Refresh Slow Reports?

What happens between the time you drop and recreate the table and the time your analyst comes in?
What does your analyst do before he runs the report?

BTW, why not truncate the table (works faster - no recreate needed) and disable the index?

And you do analyse table *and* index *after* the inserts, do you?

--
Kind Regards,
Frank
"fooguy" <jweisen_at_my-deja.com> wrote in message
news:8oe60q$j06$1_at_nnrp1.deja.com...


> Optimizer is set to CHOOSE, stats have been updated, plan is same either

> way (before or after table is refreshed).

>

> FYI: This is Oracle 7.3.4 under OpenVMS 7.1 Alpha.

>

> In article <nCWp5.721$xa.64302_at_nnrp1.sbc.net>,

>   "spencer" <spencerp_at_swbell.net> wrote:

> > 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.

> > >

> >

> >

>

> --

> *********************************************

> "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.