| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: View with group by
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.
Check out the server tuning giude for mergeable vs non-mergeable views...
In essence, when you have views with grouping expressions within (max, min, distinct etc), you need to be careful when joining to other tables/views...
The database (as you have discovered) will often in this instance choose to fully evaluate the view (ie go and get all the data) and then apply the where clause predicates once the view has been fully resolved...
HTH
--
"Some days you're the pigeon, and some days you're the statue." Received on Wed Sep 01 1999 - 06:33:40 CDT
 |
 |