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: dbms_stats messing up sys schema

RE: dbms_stats messing up sys schema

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Thu, 9 Nov 2006 17:01:25 +0100
Message-ID: <F2C9CCA71510B442AF71446CAE8AEBAF79BEE3@MSXVS04.trivadis.com>


Cosmin

> I have noticed that, for some tables only, even small ones, Oracle
> decides it's easier to create a global temporary table in the
> SYS.ora_temp_xyz rather than simply doing the "select..."

DBMS_STATS uses the subquery factoring clause. Therefore it is up to the query optimizer to decide if the subqueries have to be materialized or not. So, it is possible that for some queries temporary tables are created.

That said, IIRC, those GTT are named SYS_TEMP_???????

> this to me, is messing up too much the SYS data dictionary--
> creating tons of objects of this nature...

Honestly I have no problem with that.

> Why does this happen and how can I prevent it?

AFAIK the only way to do it is to add the hint INLINE at statement level. I.e., for this specific case, you can't.

HTH
Chris

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Nov 09 2006 - 10:01:25 CST

Original text of this message

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