Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Is ROLLUP really better than doing a nested query when rank is involved?

Is ROLLUP really better than doing a nested query when rank is involved?

From: <>
Date: Tue, 04 Sep 2007 03:05:36 -0000
Message-ID: <>

Here is a thread I posted elsewhere that I wonder if someone here could shed some light on...

The original question I had:

I realize the question was vague, but a super DBA understood what I was asking and suggested I study up on ROLLUP and CUBE (I am not a DBA by the way). So I did... and here is what I found:

The problem is that I ultimately only want the top 25 stores in total sales BUT with their subtotals as well... So I used ROLLUP to generate
the totals and subtotals... and in the same query I generated a ranking of the data... and there I found my problem: the ranking does not distinguish between totals and subtotals. So the results look like:

store1, realTotal, 1
store1, subTotal, 2
store2, realTotal, 3
store7, realTotal, 4

store10, realTotal, 5
store10, subTotal, 6

Clearly, the rows with subtotals should NOT be ranked... but I am struggling with SQL to figure out how to get exactly what I want.

Well... I have a solution. But I'm not sure about it. It involves something I learned about only recently: a user-defined aggregate function that concatenates values from across rows. Using this aggregate function, stragg, I can do another pass through the results mentioned above while grouping by store, and produce:

store1, 'realTotal subTotal', 1
store2, 'realTotal subTotal', 3
store7, 'realTotal subTotal', 4

store10, 'realTotal subTotal', 5

And another trick: use min( rank ) in the same aggregation, and order by it as well.

Together that gives one a very compact result set that has everything ranked and grouped perfectly.

...but... a worry: is this REALLY better than doing a nested query? I wonder if the use of the user-defined aggregate function is very expensive...

For the benefit of anyone wondering, here's the structure that works... in one pass in theory... but again, I am now wondering if it really is faster than the nested query equivalent that hits the database twice on the table 'stores' (seems to me that the middle select below is effectively hitting the table again, just in temporary

select rownum as rank, id, days, totals

   select id, stragg(day) as days, stragg(total) as totals, min( rank ) as innerrank


      select id, day, sum(sales) as total, rank() over (order by sum(sales) desc) rank

      from stores
      group by id, rollup(day)

   group by id
   order by min(rank)
where rownum <= 25 Any comments? Is this query really better than the following: Received on Mon Sep 03 2007 - 22:05:36 CDT

Original text of this message