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?
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.