Re: Table Refresh Slow Reports?

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.