Re: View performance problem

I think you could get some performance boost by reordering your WHERE clauses. Placing them in the order in which the greatest number of records are excluded can be a big help. Optimally, these where clauses would involve columns that are indexed, as well.

The two last clauses may be good candidates to order first and second. Doing before and after explain plans will help you know if there is any benefit.

--
Michael O'Neill
mjoneill_at_email.com


<terry_stjean_at_my-deja.com> wrote in message
news:905kue$v6u$1_at_nnrp1.deja.com...


> Maybe you can help me out with the best way to do this.

> I have 3 tables.

> A store master.

> A Product master

> A sales file.

> I have an index on both the product master and sales by upc (not

> unique).

> I have an index on both the store and sales by store and customer

> affiliation (unique).

>

> Here is my select in the view:

> select rev_pos_sales.STARTWEEK, rev_pos_sales.STORENUM,

> rev_pos_sales.FYEAR, rev_pos_sales.FPERIOD,

> rev_pos_sales.FWEEK, rev_pos_sales.QTY, rev_pos_sales.RETTOTAL,

> rev_store_hier.CUST_NAME, rev_store_hier.PROVID,

> rev_prod_hier.BRAND_CODE, rev_prod_hier.BRAND_NAME,

> rev_prod_hier.CATEGORY_CODE, rev_prod_hier.CATEGORY_NAME,

> rev_prod_hier.sub_category_code,

> rev_prod_hier.sub_category_name,

> rev_prod_hier.FRANCHISE_CODE, rev_prod_hier.FRANCHISE_NAME,

> rev_prod_hier.FRANCHISE_GRP_CODE,

> rev_prod_hier.FRANCHISE_GRP_NAME,

> rev_prod_hier.SUB_DIVISION_CODE,

> rev_prod_hier.SUB_DIVISION_NAME,

> rev_prod_hier.SPEC_CHAR_CODE, rev_prod_hier.SPEC_CHAR_NAME,

> rev_prod_hier.HO_PRODID, rev_prod_hier.ACTIVE,

> rev_prod_hier.PDES1, rev_prod_hier.PDES2,

> rev_store_hier.PROV_NAME, rev_prod_hier.HO_UPC,

> rev_store_hier.CUSTNUM, rev_pos_sales.ENDWEEK

> from REV_POS_SALES, REV_PROD_HIER, REV_STORE_HIER

> where rev_prod_hier.divid = 'REV1' and

> rev_pos_sales.upc = rev_prod_hier.upc and

> rev_pos_sales.afid = rev_store_hier.afid and

> rev_pos_sales.storenum = rev_store_hier.storenum and

> rev_pos_sales.afid = 'H' and

> rev_pos_sales.startweek between '01-JAN-1999' and '27-JAN-2001';

> I also have an index on the sales by affiliation and startweek.

> Any suggestions? Can I use hints?

>

> Terry

>

> In article <904ame$vug$1_at_nnrp1.deja.com>,

>   jdarrah_co_at_my-deja.com wrote:

> > If any init.ora paremeters were changed, it could effect the execution

> > plan.  Does the execution plan look good (i.e. no cartiesion merge

> > joins) If the execution plan look bad look at the statistics on the

> > table they may have been deleted or, if your using the choose mode for

> > your optimizer and sombody analyzed the table, the execution path

 could


> > change.  If it looks like the query is executing a logical plan, then

> > the next thing would be to look at the wait statistics for the session

> > while executing the query.  Either look in  V$SESSION_WAIT or use

> > oradebug to turn on event 10046 (I don't know how to do this on NT)

 and


> > look at the resulting trace file.

> >

> > In article <903aki$3ju$1_at_nnrp1.deja.com>,

> >   terry_stjean_at_my-deja.com wrote:

> > > I have a view created which joins 3 tables. A product table, a store

> > > table and a sales transaction table.

> > > Up until this past week, hen I queried the view, the performance

 wasn't


> > > bad, however this week the performance is really slow. This is even

> > > with no on else on the system.

> > > No changes have been made to the view and no data was added to the 3

> > > tables the view uses.

> > > I have no idea what is going on. Any ideas of things to check?

> > > We are running Oracle 8.0.5 on NT 4.

> > >

> > > Terry

> > >

> > > Sent via Deja.com http://www.deja.com/

> > > Before you buy.

> > >

> >

> > Sent via Deja.com http://www.deja.com/

> > Before you buy.

> >

>

>

> Sent via Deja.com http://www.deja.com/

> Before you buy.