|
|
|
Forum Newbie
Group: Forum Members
Last Login: 3/14/2007 10:58:00 AM
Posts: 3,
Visits: 1
|
|
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),());
Thanks in advance...
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Today @ 7:20:23 AM
Posts: 533,
Visits: 282
|
|
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
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 3/14/2007 10:58:00 AM
Posts: 3,
Visits: 1
|
|
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Today @ 7:20:23 AM
Posts: 533,
Visits: 282
|
|
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
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 3/14/2007 10:58:00 AM
Posts: 3,
Visits: 1
|
|
|
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.
|
|
|
|