Re: Table Refresh Slow Reports?

Users are using the system between the time I refresh the table and the analyst comes in, but nothing affecting this data.

The analyst just runs the report, nothing special on his side.

I like the idea of truncating the table...how do I disable an index? I assume I then need to rebuild it?

I am indeed recreating the index and reanalyizing the table after refreshing the data.

In article <8og5hg$c1r$1_at_porthos.nl.uu.net>,   "Frank van Bortel" <f.van.bortel_at_vnl.nl> wrote:
> 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.
>
>

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