Re: View performance problem

From: <terry_stjean_at_my-deja.com>
Date: Thu, 30 Nov 2000 13:33:02 GMT
Message-ID: <905kue$v6u$1@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. Received on Thu Nov 30 2000 - 07:33:02 CST