View with group by

From: <mapascoe_at_my-deja.com>
Date: Wed, 01 Sep 1999 02:43:00 GMT
Message-ID: <7qi3ri$n1n$1@nnrp1.deja.com>

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 Tue Aug 31 1999 - 21:43:00 CDT