Analytics

## How to optimize queries with grouping sets?

I have a query which uses grouping sets that takes a lot of time. What indexes can I define and on which fields of the table?

For discussion purposes, below is my query:

select
COL1 ,
COL2,
COL3 ,
COL4,
COL5,
COL6 ,
COL7 ,
COL8,
COL9,
COL10 ,
COL11,
COL12,
count(distinct(COL13 ))
from TABLE_ONE
group by rollup(1,2,3), rollup(4,5,6), rollup (7,8), grouping sets ((12,11),(12,10),(12,9),());

4 REPLIES
Junior Contributor

## Re: How to optimize queries with grouping sets?

And now for the 1 Mio. dollar question:

How many aggregate levels are calculated by
group by rollup(1,2,3), rollup(4,5,6), rollup (7,8), grouping sets ((12,11),(12,10),(12,9),())

a. 3 + 3 + 2 + 4 = 12
b. 4 + 4 + 3 + 4 = 15
c. 3 * 3 * 2 * 4 = 72
d. 4 * 4 * 3 * 4 = 192

Hint:

SQL Reference: Data Manipulation Statements
Chapter 1: The SELECT Statement
How Extended GROUP BY Operator Specifications Are Resolved

Multiply that number * 2 (because of the COUNT(DISTINCT)) and then you'll see that no index will help you in that case.

Do you really want that result set?
Nobody will ever even try to read that :-)

Dieter

## Re: How to optimize queries with grouping sets?

Dieter,

Thanks you for your reply. The result set will be passed to Cognos OLAP cube as an external rollup. This is just a workaround for a Cognos limitation. Cognos can not handle the count(distinct) since it has 65,000 categories limit.
This is just one of the SQLs to cover all the combinations so I really need to make these SQLs run really fast. The table has around 120 million records... Are there other ways to make the queries run faster?

Teddy
Junior Contributor

## Re: How to optimize queries with grouping sets?

Hi Teddy,
192 levels of aggregations (and 384 aggregate steps in Explain) on a base set of 120 mio. rows needs a loooong time.
Are you really shure you (or Cognos) needs those 192 levels?

Dieter

## Re: How to optimize queries with grouping sets?

Yes, Cognos needs those data to be able to have a complete cube. This query runs more than 24 hours. This is just a workaround for Cognos. We are looking for other OLAP tools that can handle our requirement.