Re: View with group by

It appears that your table stores Sales for the Month, but tax for the year to date. That's quite odd.

However, on the assumption that there is some purpose being served with that design and on a further important assumption that the tax value only increases during a given year (i.e. Sales tax for a month always exceed refunds), you could get your results with the following view

create view vx as
select emp, trtry, trunc(month/100) year, sum(sales) sales, max(tax) tax from sales_tab
group by emp, trtry, trunc(month/100);

Rudy

On Tue, 31 Aug 1999, mapascoe_at_my-deja.com wrote: >Hello,
>
>I have a performance problem that I hope someone can help me with.
>
>The simplified version of the problem is that I have a table that
>has sales information by employee, territory, month and product.
>In each row, I have the sales totals for the month and the and
>the tax paid this year up to the current month. For example:
>
>EMP TRTRY MONTH PROD SALES TAX
>--- ----- ------- ----- ----- ---
>234 NY 199906 1233 10000 100
>234 NY 199907 1233 150 102
>
>715 WEST 199906 4371 5000 50
>715 WEST 199907 4371 2000 70
>715 WEST 199908 4371 22000 290
>
>. . .
>
>(Note that we have no information for EMP 234 for 199908.)
>
>I would like to create a view that gives me the total sales and total
>tax paid for a particular year:
>
>EMP TRTRY YEAR SALES TAX
>--- ----- ---- ----- ---
>234 NY 1999 10150 102
>715 WEST 1999 29000 290
>. . .
>
>What I did was to create three views: one that summarizes sales, one
>that summarizes tax, and a third to join the first two:
>
>create view v1 as
> select emp, trtry, trunc(month/100) year, sum(sales) sales
> from sales_tab
> group by emp, trunc(month/100)
>
>create view v2 as
> select emp, trtry, trunc(month/100) year, tax
> from sales_tab ST
> where month in (
> select max(month)
> from sales_tab
> where emp_id = ST.emp_id
> and trunc(month/100) = trunc(ST.month/100)
> )
> group by emp, trunc(month/100)
>
>create view sales_vw as
> select v1.emp_id, v1.trtry, v1.year, v1.sales, v2.tax
> from v1, v2
> where v1.emp_id = v2.emp_id
> and v1.trtry = v2.trtry
> and v1.year = v2.year
>
>The sales view is fine if I ask for a specific employee, but if I join
>this view with, say, the EMPLOYEE table, it takes forever because
>Oracle is doing full table scans on SALES_TAB. I can get it to use
>and the index on EMP if I declare the first two views as
>
>create view v1 as
> select --+ index(S1 sales_ind)
> emp, trtry, trunc(month/100) year, sum(sales) sales
> from sales_tab S1
> where emp > 0
> group by emp, trunc(month/100)
>
>create view v2 as
> select --+ index (S2 sales_ind)
> emp, trtry, trunc(month/100) year, tax
> from sales_tab S2
> where month in (
> select max(month)
> from sales_tab
> where emp_id = S2.emp_id
> and trunc(month/100) = trunc(S2.month/100)
> )
> and emp > 0
> group by emp, trunc(month/100)
>
>but then, it uses a full index scan, which is still pretty bad.
>
>Any suggestions?
>
>
>Cheers,
>Miguel Pascoe
>Miguel.Pascoe_at_fmr.com
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Wed Sep 01 1999 - 07:55:29 CDT