Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Big Difference in Select and Create Table As Select Traces

RE: Big Difference in Select and Create Table As Select Traces

From: MacGregor, Ian A. <ian_at_slac.stanford.edu>
Date: Tue, 9 Jan 2007 12:15:11 -0800
Message-ID: <7F24308CD176594B8F14969D10C02C6C011B1261@exch-mail2.win.slac.stanford.edu>


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_at_rdtex.ru] Sent: Saturday, January 06, 2007 4:07 AM To: MacGregor, Ian A.
Cc: shivaswamykr_at_gmail.com; oracle-l_at_freelists.org 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?

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

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jan 09 2007 - 14:15:11 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US