Re: group by grouping set error in oracle 10g

On Jul 31, 1:22 pm, nickli <ningli2...@xxxxxxxxxxx> wrote:
Hi,

I had the error message"ORA-03001: unimplemented feature" when
running the following query in Oracle 10g:

SELECT /*+ USE_HASH(m,t) */
m.household_id
,t.channel_code
,m.trading_area
,SUM(t.quantity)
,case when grouping_id(-1)=0 then -1
when grouping_id(1)=0 then 1
when grouping_id(2)=0 then 2
when grouping_id(3)=0 then 3
else 99 end group_set_id
.......
FROM ( SELECT /*+ index(l) */
household_id
,min(segment_key) segment_key
,min(trading_area) trading_area
FROM promo_mailed_list partition(p3134) l
WHERE 1=1
GROUP BY household_id ) m
,transaction t
WHERE t.household_id = m.household_id
AND t.transaction_type = 1
AND t.sales_credit = 1
AND t.transaction_date BETWEEN '15-JUN-2007' AND '17-AUG-2007'
GROUP BY GROUPING SETS
((m.household_id,m.segment_key,t.channel_code,m.trading_area,t.transaction_­date,-1)
,(m.household_id,m.segment_key,m.trading_area,
1)
,(m.household_id,m.segment_key,t.channel_code,m.trading_area,2)
,
(m.household_id,m.segment_key,t.channel_code,m.trading_area,3))

It seems that Oracle 10g is complaining about the GROUP BY GROUPING
SETS statement.
The same statement runs well in 9i. Could anyone tell me a workaround
for this? I tried to set the following and it is not working:

alter session set "_gby_hash_aggregation_enabled" = FALSE;
alter session set optimizer_features_enable='9.2.0';

Thanks in advance.

Nick Li

Search for ora-030001 on metalink. You may find things like bug
5847881, no workaround, fixed in 11.1.0.5. You should probably open
an SR and kick Oracle's *** if they can't fix it right away.

jg
--
@home.com is bogus. "Screening ideas are indeed thought up by the
Office for Annoying Air Travelers and vetted through the Directorate
for Confusion and Complexity, and then we review them to insure that
there are sufficient unintended irritating consequences so that the
blogosphere is constantly fueled." - Kip Hawley

.

Quantcast