RE: Big Difference in Select and Create Table As Select Traces
- From: "MacGregor, Ian A." <ian@xxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 9 Jan 2007 12:15:11 -0800
My first response over quoted a previous message. I set
optimizer_features_enable='10.1.0'. Which among other things turns off hash
group by. I had tried creating a table and using insert before, but to no
avail. The plans for create materialized view ps_compensation view and create
materialized view oracle.ps_compnsation view do differ. The latter statement
is followed immediately by "create table" in the trace. In the former when sys
creates the mv as sys there is quite a bit of additional recursive sql before
the create table statement is issued.
Ian
-----Original Message-----
From: Andrey Kriushin [mailto:Andrey.Kriushin@xxxxxxxx]
Sent: Saturday, January 06, 2007 4:07 AM
To: MacGregor, Ian A.
Cc: shivaswamykr@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Big Difference in Select and Create Table As Select Traces
I'd suggest to check ML for the bug(s) related to the hash group by
(I've used Advanced Search with Knowledge Base & Bug Database and found
52 hits). So, don't use it without the patch, walkaround or till 10.2.0.3.
Thanks to Shivaswamy Raghunath for pointing to the Note:399077.1. That's
what I've suspected: the query transformation limitations in the CTAS.
As for transformation itself - have you also tried:
- MERGE hint?
- create an empty table and then use an INSERT with APPEND or PARALLEL
hint? There might be some chances, that the INSERT is not so limited as
the CTAS.
One more question - was the execution plan the same as in your
standalone SELECT (except for the object creation of course) when you
were creating MV under sys account?
--Andrey
--
//www.freelists.org/webpage/oracle-l
Other related posts:
