Skip to Main Content
  • Questions

  • SELECT with WITH Clause(subfactoring) and ORA-4031 errors.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Shanmugam.

Asked: February 21, 2007 - 9:08 pm UTC

Last updated: February 07, 2018 - 12:34 am UTC

Version: 9.2.0.1

Viewed 10K+ times! This question is

You Asked

Tom,

I want to validate following reasons to keep away from using "WITH CLAUSE" in the select statement:

a) Each time the WITH query clause is run, a global temporary table is created and used in the query. Even if the SQL is using bind variables, which normally would enable code sharing, the table name is embedded in the SQL, causing many versions to be created in the shared pool and ultimately causing fragmentation and ORA-4031 errors.

b) These tables have no dictionary statistics on them and when they are joined to large, permanent tables, the Cost Based Optimizer (CBO) may make bad decisions for the query plan. For example, if there will be 100 rows in the Global Temporary Table (GTT) and it is joined to a permanent table that has 10 million rows, the CBO may decide to hash join the 2 tables, starting with the larger one, when it should start with the small GTT and perform a nested loop join.

I have used it because I read good things about it and felt it is apt to use in the situation where the J2EE application needs to be developed completely in JAVA without moving business logic and rules in middlware component to other languages/stored procedures.

Also suggest the best practices while using "WITH CLAUSE".

Thank you,

and Tom said...

a) maybe yes, maybe no. the option to use temp is there, but it will not necessarily happen.

I do not understand the reference to bind variables, they have nothing to do with anything here?? The SQL would be shared - why do you feel they would cause many copies in the shared pool??????

the global temporary table would never be embedded in the sql, no.

b) the estimated size of these tables is known to the optimizer - since it estimates ROWS for them anyway. It is what it does. 

with q
as
(select ..... ) <<<== the optimizer estimates the size when developing the plan.
select .....
  from q  <<<== the optimizer "knows" from its estimate.



Your fears of WITH are unfounded. Use it where ever/ when ever you like basically. It is a query construct like inline views, having clauses, intersect and the like.


Rating

  (26 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

WITH CLAUSE

Tamil, February 22, 2007 - 5:02 pm UTC

If the "WITH CLAUSE" uses MATERIALIZE hint, then only Oracle will create a Global Temporary Table, and based on the DYNAMIC_SAMPLING parameter value, Oracle will analyze the Global Tempoary table and compute the execution plan.

My question to the original poster:
Do your developers add MATERIALIZE hint in the SQL statement?

Tamil
Tom Kyte
February 22, 2007 - 7:53 pm UTC

it is not true. the plan is generated prior to any part of the plan being executed.

And Oracle will materialize "with" tables with or without a hint whenever it feels like it.

A reader, February 22, 2007 - 10:42 pm UTC

We are not using any hint because we were ot aware of it. SQLs using WITH CLAUSE are structured, readable and huge and performs very good. You may compare it to a stored procedure doing a sort of 'Set Operations' rather than row by row. The only problem we face today is ORA-4031. The reason, we were told by our experts, is shared pool memory fragmentaion due to the usage of 'WITH CLAUSE'. Developers were forced to keep away from it.

I follow Tom's simple philosophy stated in "Expert Oracle" in the middle of page-3 because it is the right thing to do.
Tom Kyte
February 23, 2007 - 7:28 am UTC

your experts are "not being accurate"

what evidence did they provide - because if they told you what you told us above, they were "making stuff up"

Proof

Tamil, February 23, 2007 - 9:51 am UTC

Tom,

<quote>
it is not true. the plan is generated prior to any part of the plan being executed.

And Oracle will materialize "with" tables with or without a hint whenever it feels like it.
</quote>

I do not think so.


I have created a small test case.

SQL> select * from v$version ;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production


SQL> create table t1 as select * from all_source where rownum <= 100;

Analyze table.

SQL> exec dbms_stats.gather_table_stats(user, 'T1');

PL/SQL procedure successfully completed.

Enabled sql trace with level 8.

Case#1 - No hint

Shutdown the database, and started up.

with gen as
(select * from t1 where rownum <=50)
select * from gen where rownum <=10

Tkrpof output shows:

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.04 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.00 2 4 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.05 0.04 2 4 0 10

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 65

Rows Row Source Operation
------- ---------------------------------------------------
10 COUNT STOPKEY (cr=4 pr=2 pw=0 time=3168 us)
10 VIEW (cr=4 pr=2 pw=0 time=3154 us)
10 COUNT STOPKEY (cr=4 pr=2 pw=0 time=3056 us)
10 TABLE ACCESS FULL T1 (cr=4 pr=2 pw=0 time=3041 us)


Case# 2 - Added hint

Shutdown the database, and started up.

Enabled sql trace with level 8

with gen as
(select /*+ MATERIALIZE */ * from t1 where rownum <=50)
select * from gen where rownum <=10

Tkrpof output shows:

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.09 0.09 0 0 1 0
Execute 1 0.37 0.36 2 3 7 0
Fetch 2 0.00 0.00 1 6 1 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.46 0.46 3 9 9 10

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 65

Rows Row Source Operation
------- ---------------------------------------------------
10 TEMP TABLE TRANSFORMATION (cr=9 pr=3 pw=1 time=370229 us)
1 LOAD AS SELECT (cr=3 pr=2 pw=1 time=367389 us)
50 COUNT STOPKEY (cr=3 pr=2 pw=0 time=3567 us)
50 TABLE ACCESS FULL T1 (cr=3 pr=2 pw=0 time=2757 us)
10 COUNT STOPKEY (cr=6 pr=1 pw=0 time=971 us)
10 VIEW (cr=6 pr=1 pw=0 time=948 us)
10 TABLE ACCESS FULL SYS_TEMP_0FD9D6600_D144FF (cr=6 pr=1 pw=0 time=930 us)


From the 2nd trace output, it is very clear that when I use MATERIALIZE hint, the CBO creates a global temporary table.

If the table is really BIG, then a GTT may be necessary.
But the table has only 100 rows and has been analyzed.
The 10G CBO knows about that.
There is no necessary to create a GTT even if the SQL has a hint.


Could you please explain Why the 10G CBO created a GTT in this case?

Thanks
Tamil
Tom Kyte
February 26, 2007 - 10:37 am UTC

so, how does that show that

a) it did the plan in a two step (it did not)
b) that it will always materialize the view using a global temporary table
c) that oracle will not materialize a with subquery without a hint?


I said:

a) the plan is generated prior to any part of the plan being executed.
b) And Oracle will materialize "with" tables with or without a hint whenever it feels like it.

How does your example demonstrate either of those concepts to be "false"?


Ok, let us look at (b)

You have demonstrated that Oracle will not ALWAYS do a temp table transformation when using WITH. Now, all I need is an example showing it sometimes will - hence showing that (b) is true (that oracle will materialized "with" tables with or without a hint whenever it feels like it)

ops$tkyte%ORA10GR2> create table t
  2  as
  3  select * from all_objects;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @at
ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> with data
  2  as
  3  (select * from t where object_id between 1 and 100)
  4  select * from scott.emp where ename in (select owner from data)
  5  union all
  6  select * from scott.emp where job in (select object_type from data)
  7  /

Execution Plan
----------------------------------------------------------
Plan hash value: 182856509

------------------------------------------------------------------------
| Id  | Operation                  | Name                      | Rows  |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                           |    22 |
|   1 |  TEMP TABLE TRANSFORMATION |                           |       |
|   2 |   LOAD AS SELECT           |                           |       |
|*  3 |    TABLE ACCESS FULL       | T                         |     8 |
|   4 |   UNION-ALL                |                           |       |
|*  5 |    HASH JOIN SEMI          |                           |     8 |
|   6 |     TABLE ACCESS FULL      | EMP                       |    14 |
|   7 |     VIEW                   | VW_NSO_1                  |     8 |
|   8 |      VIEW                  |                           |     8 |
|   9 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6601_8D0B18 |     8 |
|* 10 |    HASH JOIN SEMI          |                           |    14 |
|  11 |     TABLE ACCESS FULL      | EMP                       |    14 |
|  12 |     VIEW                   | VW_NSO_2                  |     8 |
|  13 |      VIEW                  |                           |     8 |
|  14 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6601_8D0B18 |     8 |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("OBJECT_ID">=1 AND "OBJECT_ID"<=100)
   5 - access("ENAME"="$nso_col_1")
  10 - access("JOB"="$nso_col_1")

Note
-----
   - dynamic sampling used for this statement


ops$tkyte%ORA10GR2> set autotrace off
ops$tkyte%ORA10GR2>




Now, showing that the plan is generated as a single step is a bit "harder". A 10053 event trace would be very conclusive (but a bit hard to read). But I think this is fairly compelling.

Using dynamic sampling - the optimizer in the above came up with the guess of 8 rows:
|   1 |  TEMP TABLE TRANSFORMATION |                           |       |
|   2 |   LOAD AS SELECT           |                           |       |
|*  3 |    TABLE ACCESS FULL       | T                         |    8  |


Now, if the optimizer had

a) built the temp table
b) AND THEN sampled

we might expect that 8 to be exact, to be precise, to be dead on. Why? Because 8 is very small - one block, it would have been dead on.

But:

ops$tkyte%ORA10GR2> select * from t where object_id between 1 and 100
  2  /

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     8 |  1024 |   160   (2)| 00:00:02
|*  1 |  TABLE ACCESS FULL| T    |     8 |  1024 |   160   (2)| 00:00:02
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID">=1 AND "OBJECT_ID"<=100)

Note
-----
   - dynamic sampling used for this statement



shows that the optimizer guesses 8 for that query before actually executing it and further:

ops$tkyte%ORA10GR2> select count(*) from t where object_id between 1 and 100;

  COUNT(*)
----------
        99


it was far away from being right and if we MATERIALIZE the data AND THEN sample:

ops$tkyte%ORA10GR2> create table t2 as select * from t where object_id between 1 and 100;

Table created.

ops$tkyte%ORA10GR2> @at
ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select * from t2;

Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157

------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    99 | 12672 |     3   (0)| 00:00:01
|   1 |  TABLE ACCESS FULL| T2   |    99 | 12672 |     3   (0)| 00:00:01
------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


we get it right.....



so, again:




a) the plan is generated prior to any part of the plan being executed.
b) And Oracle will materialize "with" tables with or without a hint whenever it feels like it.



Materialize Hint

Jonathan Lewis, February 23, 2007 - 3:05 pm UTC

Tamil,

Tom's comment was:
And Oracle will materialize "with" tables with or without a hint whenever it feels like it.

This means that if you do not give Oracle a /*+ materialize */ hint it may still materialize the subquery.

Your demonstration merely showed that the materialize hint causes Oracle to materialize the subquery - but that's not surprising since Oracle has to obey hints that have been used correctly.

If you wish to show that Oracle may materialize the subquery unhinted then, possibly, changing your main query to the following (and I have to work from memory here as it's now off-screen) may be a sufficient demonstration:
select * from gen where rownum <= 2
union all
select * from gen where rownum <= 2


Going back to the original poster - If Oracle has materialized subqueries, the result set is written into a global temporary table (GTT). If not, then the text has just been folded into the main body of the query, and the ORA-04031 may be the unfortunate side-effect of optimising an excessively complex query, or the side-effect of generating a non-mergeable result set in the PGA.

Tom Kyte
February 26, 2007 - 10:59 am UTC

thanks Jonathan

I provided my feedback above before reading your feedback here ;)

Shanmugam Dhanapal, February 23, 2007 - 5:01 pm UTC

Could you please elaborate on "If not, then the text has just been folded into the main body of the query and ... the side-effect of generating a non-mergeable result set in the PGA"

Tom Kyte
February 26, 2007 - 12:46 pm UTC

see Rodericks comment below.

the 4031 could be the result of a gigantic query plan, lack of use of bind variables (and hence the flooding of the shared pool)

I don't see the tie in to the PGA, as that would typically be a 4030, not a 4031.

maybe some clarification

Roderick, February 24, 2007 - 6:03 pm UTC

I'm not 100% familiar with the mechanics of a SQL statement that includes WITH clauses, but I'll toss in my 2 cents.
.
An ORA-4031 error is specific to running out of space in the shared pool. If you see that error, it was likely during the parse phase of the query. Maybe the execution plan generated can get huge under certain circumstances for complex queries.
.
An ORA-4030 error is specific to running out of memory in the PGA. If you see that error, then it may be a sign that the execution phase of a SQL needs a lot of private run time memory including workareas for sorting or hashing data. [I'm do not recall if I have ever seen this error happen during parse phase of a query but I imagine it is not impossible].
.
ORA-1652 is the type of error I would anticipate if a query is populating a global temp table during execution and runs out of space in the TEMP tablespace (for whatever reason). I'm not 100% sure if any records are buffered in the PGA before being written to a GTT.
.
Hope this helps rather than confuses matters.

Another proof

Tamil, February 25, 2007 - 10:47 am UTC

JL,
Thanks for your answer.

<quote>
If you wish to show that Oracle may materialize the subquery unhinted then, possibly, changing your main query to the following (and I have to work from memory here as it's now off-screen) may be a sufficient demonstration: <quote>

Here is my test. I did not add a MATERIALIZE hint.

Raw trace file shows:
PARSING IN CURSOR #1 len=133 dep=0 uid=65 oct=3 lid=65 tim=6455264884414 hv=3578672561 ad='3d2cdc10'
with gen as
(select * from t1 where rownum <= 50)
select * from gen where rownum <= 10
union all
select * from gen where rownum <= 10
END OF STMT
PARSE #1:c=330000,e=470856,p=0,cr=102,cu=2,mis=1,r=0,dep=0,og=1,tim=6455264884388
WAIT #1: nam='control file sequential read' ela= 231 file#=0 block#=1 blocks=1 obj#=-1 tim=6455264914896
WAIT #1: nam='control file sequential read' ela= 140 file#=1 block#=1 blocks=1 obj#=-1 tim=6455264915361
WAIT #1: nam='control file sequential read' ela= 138 file#=2 block#=1 blocks=1 obj#=-1 tim=6455264915672
WAIT #1: nam='control file sequential read' ela= 133 file#=0 block#=390 blocks=1 obj#=-1 tim=6455264916029
WAIT #1: nam='db file sequential read' ela= 14490 file#=5 block#=142110 blocks=1 obj#=64908 tim=6455264932727
WAIT #1: nam='db file sequential read' ela= 38898 file#=5 block#=142111 blocks=1 obj#=64908 tim=6455264972959
WAIT #1: nam='direct path write temp' ela= 2 file number=201 first dba=17034 block cnt=1 obj#=64908 tim=6455265054692
WAIT #1: nam='direct path write temp' ela= 2 file number=201 first dba=17034 block cnt=1 obj#=64908 tim=6455265062202
WAIT #1: nam='direct path write temp' ela= 132 file number=201 first dba=17034 block cnt=1 obj#=64908 tim=6455265062332
EXEC #1:c=1150000,e=1242997,p=2,cr=3,cu=7,mis=0,r=0,dep=0,og=1,tim=6455266128129
WAIT #1: nam='SQL*Net message to client' ela= 17 driver id=1650815232 #bytes=1 p3=0 obj#=64908 tim=6455266128797
WAIT #1: nam='db file sequential read' ela= 157 file#=201 block#=17034 blocks=1 obj#=-40016382 tim=6455266130118
FETCH #1:c=0,e=1166,p=1,cr=5,cu=1,mis=0,r=1,dep=0,og=1,tim=6455266130443
WAIT #1: nam='SQL*Net message from client' ela= 22644 driver id=1650815232 #bytes=1 p3=0 obj#=-40016382 tim=6455266153591
WAIT #1: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=-40016382 tim=6455266153911
FETCH #1:c=0,e=701,p=0,cr=4,cu=0,mis=0,r=15,dep=0,og=1,tim=6455266154513
WAIT #1: nam='SQL*Net message from client' ela= 12699 driver id=1650815232 #bytes=1 p3=0 obj#=-40016382 tim=6455266167436
WAIT #1: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=-40016382 tim=6455266167711
FETCH #1:c=0,e=312,p=0,cr=1,cu=0,mis=0,r=4,dep=0,og=1,tim=6455266167932
WAIT #1: nam='SQL*Net message from client' ela= 5344 driver id=1650815232 #bytes=1 p3=0 obj#=-40016382 tim=6455266174715
STAT #1 id=1 cnt=20 pid=0 pos=1 obj=0 op='TEMP TABLE TRANSFORMATION (cr=13 pr=3 pw=1 time=1243807 us)'
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=0 op='LOAD AS SELECT (cr=3 pr=2 pw=1 time=1241100 us)'
STAT #1 id=3 cnt=50 pid=2 pos=1 obj=0 op='COUNT STOPKEY (cr=3 pr=2 pw=0 time=57808 us)'
STAT #1 id=4 cnt=50 pid=3 pos=1 obj=64908 op='TABLE ACCESS FULL T1 (cr=3 pr=2 pw=0 time=56998 us)'
STAT #1 id=5 cnt=20 pid=1 pos=2 obj=0 op='UNION-ALL (cr=10 pr=1 pw=0 time=1098 us)'
STAT #1 id=6 cnt=10 pid=5 pos=1 obj=0 op='COUNT STOPKEY (cr=6 pr=1 pw=0 time=1064 us)'
STAT #1 id=7 cnt=10 pid=6 pos=1 obj=0 op='VIEW (cr=6 pr=1 pw=0 time=1042 us)'
STAT #1 id=8 cnt=10 pid=7 pos=1 obj=4254950914 op='TABLE ACCESS FULL SYS_TEMP_0FD9D6602_D2AA59 (cr=6 pr=1 pw=0 time=960 us)'
STAT #1 id=9 cnt=10 pid=5 pos=2 obj=0 op='COUNT STOPKEY (cr=4 pr=0 pw=0 time=252 us)'
STAT #1 id=10 cnt=10 pid=9 pos=1 obj=0 op='VIEW (cr=4 pr=0 pw=0 time=217 us)'
STAT #1 id=11 cnt=10 pid=10 pos=1 obj=4254950914 op='TABLE ACCESS FULL SYS_TEMP_0FD9D6602_D2AA59 (cr=4 pr=0 pw=0 time=186 us)'
WAIT #0: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=-40016382 tim=6455266176546
*** 2007-02-25 10:23:40.680
WAIT #0: nam='SQL*Net message from client' ela= 14261391 driver id=1650815232 #bytes=1 p3=0 obj#=-40016382 tim=6455280438103
=====================

Output from TKPROF:

with gen as
(select * from t1 where rownum <= 50)
select * from gen where rownum <= 10
union all
select * from gen where rownum <= 10

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.09       0.09          0          0          1           0
Execute      1      1.15       1.24          2          3          7           0
Fetch        3      0.00       0.00          1         10          1          20
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      1.24       1.34          3         13          9          20


Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 65
Rows     Row Source Operation
-------  ---------------------------------------------------
     20  TEMP TABLE TRANSFORMATION  (cr=13 pr=3 pw=1 time=1243807 us)
      1   LOAD AS SELECT  (cr=3 pr=2 pw=1 time=1241100 us)
     50    COUNT STOPKEY (cr=3 pr=2 pw=0 time=57808 us)
     50     TABLE ACCESS FULL T1 (cr=3 pr=2 pw=0 time=56998 us)
     20   UNION-ALL  (cr=10 pr=1 pw=0 time=1098 us)
     10    COUNT STOPKEY (cr=6 pr=1 pw=0 time=1064 us)
     10     VIEW  (cr=6 pr=1 pw=0 time=1042 us)
     10      TABLE ACCESS FULL SYS_TEMP_0FD9D6602_D2AA59 (cr=6 pr=1 pw=0 time=960 us)
     10    COUNT STOPKEY (cr=4 pr=0 pw=0 time=252 us)
     10     VIEW  (cr=4 pr=0 pw=0 time=217 us)
     10      TABLE ACCESS FULL SYS_TEMP_0FD9D6602_D2AA59 (cr=4 pr=0 pw=0 time=186 us)

Is it a valid proof for flooding the shared pool thus causing ora-4031 error?

Or are we heading towards a optimizer bug in 10gR2?

My basic question is: Why did oracle create a GTT for a skinny table of 100 rows where it could do every thing in memory ?

Tamil
Tom Kyte
February 26, 2007 - 1:25 pm UTC

no, what you just "proved" was my point (b) above - that oracle may or may not use a global temporary table with or without a hint!!!!!!!!


I've no idea why you think this is related to a 4031.

To Tamil

Oraboy, February 25, 2007 - 2:24 pm UTC

Tamil-
As Tom and JL pointed out, materializing a subquery is upto Oracle's optimizer and it can do it with/without materialize hint.

>>My basic question is: Why did oracle create a GTT for a skinny table of 100 rows where it could do every thing in memory ?


- your query to build gen (With clause has rownum filter)..which pretty much forces the optimizer to first perform the subquery execution..Removing the "rownum<=50" *may* generate the plan you are looking for (without temp table transformation)

SQL> create table t1 as select * from all_objects where rownum<100;

Table created.

SQL> exec dbms_stats.gather_table_stats(user, 'T1');

PL/SQL procedure successfully completed.

SQL> show rel
release 1002000100

SQL> ed
Wrote file afiedt.buf

1* select owner,object_type,count(*) from t1 group by owner,object_type
SQL> /

OWNER OBJECT_TYPE COUNT(*)
------------------------------ ------------------- ----------
SYS INDEX 37
SYS TABLE 57
SYS CLUSTER 5



SQL> ed
Wrote file afiedt.buf

1 With gen as (select * from t1 where rownum<50)
2 select * from gen g where rownum<10
3 union all
4* select * from gen g where rownum<10
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 3413705183

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 2304 | 4 (50)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | TABLE ACCESS FULL | T1 | 49 | 3528 | 2 (0)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
|* 6 | COUNT STOPKEY | | | | | |
| 7 | VIEW | | 49 | 6272 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6608_1C66B5 | 49 | 3528 | 2 (0)| 00:00:01
|* 9 | COUNT STOPKEY | | | | | |
| 10 | VIEW | | 49 | 6272 | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6608_1C66B5 | 49 | 3528 | 2 (0)| 00:00:01
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter(ROWNUM<50)
6 - filter(ROWNUM<10)
9 - filter(ROWNUM<10)

SQL> ed
Wrote file afiedt.buf

1 With gen as (select * from t1 )
2 select * from gen g where rownum<10
3 union all
4* select * from gen g where rownum<10
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 353414177

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 1296 | 6 (50)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS FULL| T1 | 99 | 7128 | 3 (0)| 00:00:01 |
|* 4 | COUNT STOPKEY | | | | | |
| 5 | TABLE ACCESS FULL| T1 | 99 | 7128 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(ROWNUM<10)
4 - filter(ROWNUM<10)


SQL> ed
Wrote file afiedt.buf

1 With gen as (select * from t1)
2 select * from gen g where object_type='CLUSTER'
3 union all
4* select * from gen g where object_type='INDEX'
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 1974235100

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 66 | 4752 | 6 (50)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
|* 2 | TABLE ACCESS FULL| T1 | 33 | 2376 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T1 | 33 | 2376 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("T1"."OBJECT_TYPE"='CLUSTER')
3 - filter("T1"."OBJECT_TYPE"='INDEX')

To Tamil - a better formatted output

oraboy, February 25, 2007 - 2:29 pm UTC 


SQL> create table t1 as select * from all_objects where rownum<100;

Table created.

SQL> exec dbms_stats.gather_table_stats(user, 'T1');

PL/SQL procedure successfully completed.

SQL> show rel
release 1002000100

SQL> ed
Wrote file afiedt.buf

  1* select owner,object_type,count(*) from t1 group by owner,object_type
SQL> /

OWNER                          OBJECT_TYPE           COUNT(*)
------------------------------ ------------------- ----------
SYS                            INDEX                       37
SYS                            TABLE                       57
SYS                            CLUSTER                      5


SQL> ed
Wrote file afiedt.buf

  1  With gen as (select  * from t1 where rownum<50)
  2  select * from gen g where rownum<10
  3  union all
  4* select * from gen g where rownum<10
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 3413705183

----------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                           |    18 |  2304 |     4  (50)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                           |       |       |            |          |
|   2 |   LOAD AS SELECT           |                           |       |       |            |          |
|*  3 |    COUNT STOPKEY           |                           |       |       |            |          |
|   4 |     TABLE ACCESS FULL      | T1                        |    49 |  3528 |     2   (0)| 00:00:01 |
|   5 |   UNION-ALL                |                           |       |       |            |          |
|*  6 |    COUNT STOPKEY           |                           |       |       |            |          |
|   7 |     VIEW                   |                           |    49 |  6272 |     2   (0)| 00:00:01 |
|   8 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D6608_1C66B5 |    49 |  3528 |     2   (0)| 00:00:01
|*  9 |    COUNT STOPKEY           |                           |       |       |            |          |
|  10 |     VIEW                   |                           |    49 |  6272 |     2   (0)| 00:00:01 |
|  11 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D6608_1C66B5 |    49 |  3528 |     2   (0)| 00:00:01
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(ROWNUM<50)
   6 - filter(ROWNUM<10)
   9 - filter(ROWNUM<10)

SQL> ed
Wrote file afiedt.buf

  1  With gen as (select  * from t1 )
  2  select * from gen g where rownum<10
  3  union all
  4* select * from gen g where rownum<10
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 353414177

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    18 |  1296 |     6  (50)| 00:00:01 |
|   1 |  UNION-ALL          |      |       |       |            |          |
|*  2 |   COUNT STOPKEY     |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL| T1   |    99 |  7128 |     3   (0)| 00:00:01 |
|*  4 |   COUNT STOPKEY     |      |       |       |            |          |
|   5 |    TABLE ACCESS FULL| T1   |    99 |  7128 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<10)
   4 - filter(ROWNUM<10)


SQL> ed
Wrote file afiedt.buf

  1  With gen as (select  * from t1)
  2  select * from gen g where object_type='CLUSTER'
  3  union all
  4* select * from gen g where object_type='INDEX'
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 1974235100

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    66 |  4752 |     6  (50)| 00:00:01 |
|   1 |  UNION-ALL         |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |    33 |  2376 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T1   |    33 |  2376 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T1"."OBJECT_TYPE"='CLUSTER')
   3 - filter("T1"."OBJECT_TYPE"='INDEX')

Helena Marková, February 26, 2007 - 3:17 am UTC


Alberto Dell'Era, February 26, 2007 - 2:27 pm UTC

Have you noticed that the OP is still running 9.2.0.1 ...

"maybe" upgrading to 9.2.0.8 could resolve the ORA-04031 errors :)

4030 vs. 4031

Jonathan Lewis, February 26, 2007 - 4:19 pm UTC

Tom and Roderick: I agree, I made a mistake with the comment about the non-mergeable result set in the PGA - that would have been a 4030 not a 4031.


Tom Kyte
February 26, 2007 - 4:58 pm UTC

Thanks for the followup Jonathan..

a difference between 9i and 10g

Anthony Molinaro, October 13, 2007 - 9:26 am UTC 

I don't believe subquery refactoring has anything at all to do with 4031 errors, but depending on what you do in your WITH clause when using MATERIALIZE, you might benefit from using the CARDINALITY hint as well.


on 10g, oracle seems to get the cardinality correct:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> ed
Wrote file afiedt.buf

  1      explain plan for
  2      with tmp as (
  3      select /*+ materialize */ level num from dual connect by level <= 10
  4      )
  5      select e.* from emp e, tmp t
  6*   where t.num = e.empno
  7  /

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                             |     1 |    50 |     7  (15)|
|   1 |  TEMP TABLE TRANSFORMATION     |                             |       |       |            |
|   2 |   LOAD AS SELECT               |                             |       |       |            |
|*  3 |    CONNECT BY WITHOUT FILTERING|                             |       |       |            |
|   4 |     FAST DUAL                  |                             |     1 |       |     2   (0)|
|*  5 |   HASH JOIN                    |                             |     1 |    50 |     5  (20)|
|   6 |    VIEW                        |                             |     1 |    13 |     2   (0)|
|   7 |     TABLE ACCESS FULL          | SYS_TEMP_0FD9D693B_717FEC1F |     1 |    13 |     2   (0)|
|   8 |    TABLE ACCESS FULL           | EMP                         |    14 |   518 |     2   (0)|
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(LEVEL<=10)
   5 - access("T"."NUM"="E"."EMPNO")


but on 9i:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0       Production
TNS for Linux: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production



SQL> ed
Wrote file afiedt.buf

  1  explain plan for
  2  with tmp as (
  3  select /*+ materialize */ level num from dual connect by level <= 10
  4  )
  5  select e.* from emp e, tmp t
  6* where t.num = e.empno
SQL> /

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------
| Id  | Operation                        |  Name                        | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                              |  8168 |   398K|     5 |
|   2 |  TEMP TABLE TRANSFORMATION       |                              |       |       |       |
|   1 |   RECURSIVE EXECUTION            | SYS_LE_2_0                   |       |       |       |
|   0 |    INSERT STATEMENT              |                              |       |       |       |
|   1 |     LOAD AS SELECT               |                              |       |       |       |
|   2 |      CONNECT BY WITH FILTERING   |                              |       |       |       |
|   3 |       NESTED LOOPS               |                              |       |       |       |
|   4 |        TABLE ACCESS FULL         | DUAL                         |       |       |       |
|   5 |        TABLE ACCESS BY USER ROWID| DUAL                         |       |       |       |
|   6 |       NESTED LOOPS               |                              |       |       |       |
|   7 |        BUFFER SORT               |                              |       |       |       |
|   8 |         CONNECT BY PUMP          |                              |       |       |       |
|   9 |        FILTER                    |                              |       |       |       |
|  10 |         TABLE ACCESS FULL        | DUAL                         |       |       |       |
|   3 |   HASH JOIN                      |                              |  8168 |   398K|     5 |
|   4 |    TABLE ACCESS FULL             | EMP                          |    14 |   518 |     2 |
|   5 |    VIEW                          |                              |  8168 |   103K|     2 |
|   6 |     TABLE ACCESS FULL            | SYS_TEMP_0FD9D6604_71610CD3  |  8168 |   103K|     2 |
-------------------------------------------------------------------------------------------------

it treats the temp table generated from the WITH like an array (or "collection" in oracle speak) where a card of 8168 is assumed if you have an 8k block size:

SQL> explain plan for select * from table(sids_array(1,2,3)) tmp, emp e where e.empno = tmp.column_value;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------
| Id  | Operation                              |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |             |  8168 |   311K|    14 |
|   1 |  HASH JOIN                             |             |  8168 |   311K|    14 |
|   2 |   TABLE ACCESS FULL                    | EMP         |    14 |   518 |     2 |
|   3 |   COLLECTION ITERATOR CONSTRUCTOR FETCH|             |       |       |       |
--------------------------------------------------------------------------------------

(same on 9i and 10g).

Seems that if you're using collections in queries (or even pipelined table functions) you would benefit from a CARDINALITY hint, but the same can be said when using MATERIALIZE in a WITH clause at times as well.

ORA 4031

Anto, February 28, 2008 - 1:44 pm UTC

Hi Tom,

We are getting this ORA 4031 consistently when trying to create a MV or table using a long SQL containing some 4 or 5 union alls

But if we create a view using the same definition, it succeeds.

Our shared pool size is 228MB and shared pool size is 8MB. Any idea what could be done here other than increasing the shared pool size or flushing the shared pool ?
Tom Kyte
February 28, 2008 - 11:24 pm UTC

creating a view doesn't actually RUN the query.

creating a materialized view does.

what are your pga memory settings, how much real memory does this machine have and what are the ulimits in place (presuming this is not windows, if it is windows, need specifics on that)

ORA 4031

Anto, February 28, 2008 - 1:45 pm UTC

large pool size is 8MB
shared pool size is 228MB

Ora 4031

Anto, February 29, 2008 - 10:38 am UTC

The MVs we were trying to create were based on views which were again based on some other views. So there were about 3-4 levels of views being accessed within views.

We were able to get around the issue by creating an intermediate MV and use that MV for the final views, instead of creating a MV at the end.

Is there any limit on the nesting of views within another view ?
Tom Kyte
March 01, 2008 - 10:43 am UTC

... Is there any limit on the nesting of views within another view ? ...

only common sense.


I like exactly ONE level of view, period.

typically, when you create views of views of views - you end up with a mess. Your first view queries 5 tables, by the time you get to level 3 - you really only needed 4 of them - but since the view was "good enough" you used it.

Not a fan of more than one level of views, you limit what the optimizer can do, you typically do much more work than you need to (because of stuff being included that is not needed for YOUR answer)


I look at views as being very special purpose - not generic and general purpose myself.

Ora 4031

Anto, February 29, 2008 - 10:39 am UTC

show parameter pga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 314572800

show parameter work

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fileio_network_adapters string
workarea_size_policy string AUTO

ORA 4031

Anto, February 29, 2008 - 11:55 am UTC

"We are getting this ORA 4031 consistently when trying to create a MV or table using a long SQL
containing some 4 or 5 union alls

But if we create a view using the same definition, it succeeds"

If we select from the final view without any intermediate MVs,(there are some 3 or 4 levels of nesting of views in this case) there is no error - even though it is actually executing the SQL then - is it because the optimizer paths may be different in this case ?

Only if the final one is a MV or table(which is based on other views/sub views) we get this Ora 4031
Tom Kyte
March 01, 2008 - 11:58 am UTC

... is it because the optimizer paths may
be different in this case ?
..

yes.

Anto, March 03, 2008 - 12:41 pm UTC


Can WITH Caluse be used in Procedures?

Maverick, March 06, 2009 - 9:43 am UTC

Tom, I am trynig to create stored procedure with a query having WITH Caluse. It's failing [looks like it did not like the syntax] with this error:

ERROR line 6, col 6, ending_line 6, ending_col 9, Found 'data', Expecting:  @   string  or   (   or   %    .    [   or  (+)  AT  DAY  YEAR  or  **  MULTISET  or   *    /   MOD  REM  or   +    -   ||  or   ;   USING


My procedure is like this:
create or replace procedure p_test_withclause(p_data out sys_refcursor)
as

begin
 open p_data for 
with data as ( select * from emp)
select empno from data;
end;


Is it not allowed?
Thanks
Tom Kyte
March 06, 2009 - 10:52 am UTC 


ops$tkyte%ORA9IR2> create or replace procedure p_test_withclause(p_data out sys_refcursor)
  2  as
  3
  4  begin
  5   open p_data for
  6  with data as ( select * from emp)
  7  select empno from data;
  8  end;
  9  /

Procedure created.


ops$tkyte%ORA10GR2> create or replace procedure p_test_withclause(p_data out sys_refcursor)
  2  as
  3
  4  begin
  5   open p_data for
  6  with data as ( select * from emp)
  7  select empno from data;
  8  end;
  9  /

Procedure created.


ops$tkyte%ORA11GR1> create or replace procedure p_test_withclause(p_data out sys_refcursor)
  2  as
  3
  4  begin
  5   open p_data for
  6  with data as ( select * from emp)
  7  select empno from data;
  8  end;
  9  /

Procedure created.



You are doing something else wrong....

Sorry it's TOAD

Maverick, March 06, 2009 - 11:10 am UTC

Sorry Tom. This is because of TOAD. TOAD is failing on this. I tried in SQLPLUS it compiled fine.
I need to get a latest version of TOAD :-)
Tom Kyte
March 06, 2009 - 11:22 am UTC

(grab sqldeveloper - it is free... debugger and more...)

when is oracle not smart enough to handle un-necessary stuff due to deep level view?

A reader, April 17, 2012 - 3:35 am UTC

Hi Tom,

To your below point

I like exactly ONE level of view, period.
typically, when you create views of views of views - you end up with a mess. Your first view queries 5 tables, by the time you get to level 3 - you really only needed 4 of them - but since the view was "good enough" you used it.
Not a fan of more than one level of views, you limit what the optimizer can do, you typically do much more work than you need to (because of stuff being included that is not needed for YOUR answer)

I have a question, under what circumstance will oracle not smart enough to eliminate stuff not relevant to my answer.

For my test(i know it is very simple case, but would like 1 example), i intentionly create a 1-1 mapping tables, and create a 2 level view, orcale is smart enough to know
for view v2, it only access one single table.

create table test1(id int primary key, name varchar2(10));
create table test2(id int not null unique, age number);
alter table test2 add constraint con1 foreign key(id) references test1(id);
create view v1 as select a.name,b.age from test1 a, test2 b where a.id=b.id(+);
create view v2 as select name from v1;
-- TABLE ACCESS FULL of test1

Tom Kyte
April 17, 2012 - 5:28 am UTC

I have a question, under what circumstance will oracle not smart enough to
eliminate stuff not relevant to my answer.

in current releases - less often than in the past - but frequently. If you have constraints in place (primary keys, foreign keys, not null constraints) - the database can remove tables from queries these days - but in 10g and before - not so much.



However, the more complex the query - given views of views of views - you'll find many exceptions where it won't be able to do the table elimination in some cases.

With clause an partial data

Kran, June 24, 2014 - 9:17 pm UTC

Hi Tom,

I'm running a select statement which has around 5 tables and, this returns around 1000 records. The same SQL if I wrap it in a WITH and do a select out of it, it returns only 500 records. Not sure why, can you please explain.

Version of DB is 11.2.0.3

Thanks,
Kranthi

Above question

Kran, June 24, 2014 - 9:26 pm UTC

Please ignore above question, it was due to a union. Nothing to do with the WITH clause.

Thanks

Multiple With clauses in a single query

Raghav, July 18, 2014 - 8:42 am UTC

Tom

If suppose, I want to insert data into a table from various tables (around 10 tables having number of columns in each table and having large data in each), is it suggested to select the data from all tables using WITH clause? Won't it consume more memory? Won't it cause performance issue?

Thanks & Regards
Raghav

When Oracle materializes view

Zilvinas Vidmantas, February 06, 2018 - 1:13 pm UTC

Hello,

In my experience Oracle will materialize view when it used more than once in a statement. But if it used only once Oracle will not materialize it. That it my observations and they maybe are not 100% true. In this thread Oracle done just what I told...
With one exception. When view was used twice but with different filters, Oracle(I think) decided to rewrite query and have chosen not materialize it even when it used twice.
Can anyone find example when Oracle materializes view when it used olny once in query? Tried very large tables. Still no materialize
Connor McDonald
February 07, 2018 - 12:34 am UTC

Are we talking views here or the WITH statement.

In either case, anything that makes the view/query non-mergeable means some sort of "materialization" will occur, eg

SQL> create table t as select * from all_objects;

Table created.

SQL>
SQL> create or replace
  2  view V as
  3  select t.*,
  4     row_number() over ( order by object_id ) as seq
  5  from t;

View created.

SQL>
SQL>
SQL> set autotrace traceonly explain
SQL> select *
  2  from v
  3  where owner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 1300657591

------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      | 73754 |    34M|       |  2597   (1)| 00:00:01 |
|*  1 |  VIEW               | V    | 73754 |    34M|       |  2597   (1)| 00:00:01 |
|   2 |   WINDOW SORT       |      | 73754 |  9579K|    13M|  2597   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T    | 73754 |  9579K|       |   404   (1)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"='SYS')


Notice that the VIEW applied first and THEN the filter applied.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library