Oracle SQL: the WITH clause doesn't seem to create temporary data.

Hi Experts:

    I have a SQL statement look like
***************************************************
WITH a AS (<some complicated SQL statements with multiple levels of nested select statements>)
( select field1 from a
   UNION ALL
   select field2 from a
   UNION ALL
   ......
   select field30 from a
)
*********************************************************

The complicated sql statements inside the WITH clause only takes a couple seconds to execute, but the whole SQL statement couldn't finish within half an hour (don't know how long it will eventually take).

I run the explain plan on this SQL statement and found it looks like this:
****************************************************************************
- SELECT STATEMENT
   - UNION-ALL
      + FILTER
      + FILTER
      + FILTER
      ......
*********************************************************************************

So it seems to me that Oracle is running this SQL query without buffering the result from WITH clause. Also, the execution time grows faster than linear comparing to the number of selects in the UNION ALL statement.

Any idea why this can happen? and how to force the plan to store temporary table? I am using Oracle SQL Developer as client and I am connecting to Oracle 10g release 10.2.0.3.0.

Thank you!
huangs3Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
can you post the actual query?
scorneoCommented:
With will always buffer the query result. I think problem may be with very big size of output of query..
sdstuberCommented:
WITH will not "always" buffer the result.

The optimizer can choose to "materialize" or to "inline" the query.

You can try the undocumented /*+ MATERIALIZE */ hint in the with query or querying ROWNUM to try to force materialization.
Or possibly something else is going on that is preventing the optimizer from making that determination on its own
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

huangs3Author Commented:
Hi sdstuber:

> can you post the actual query?
sorry I cannot post the actual query for security reason, but if necessary I can post some simplified or manipulated ones.

>You can try the undocumented /*+ MATERIALIZE */ hint in the with query or querying ROWNUM to try to
>force materialization.
Can you give me a hint how to do that?

> Or possibly something else is going on that is preventing the optimizer from making that determination
> on its own
I think the optimizer is determinating on its own, because for some other SQL statement with WITH clause, I saw the plan look like:
****************************************************
- SELECT STATEMENT
  - TEMP TABLE TRANSFORMATION
    + LOAD AS SELECT
    - UNION-ALL
       + VIEW
       + VIEW
*****************************************************
from the SQL statement
***********************************************
with a as ( select x.id, y.parent_id from x, y where x.id = y.valve_id)
(select id from a
union all
select parent_id from a)
************************************************

Thank you!
Franck PachotOracle DBACommented:
Hi,

About materialize hint it is like this:
with a as ( select /*+ materialize */ x.id, y.parent_id from x ...

If you prefer to avoid undocumented hints, you can give a try at no_merge. See:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/ds_appdev004.htm#sthref3175

Regards,
Franck.
huangs3Author Commented:
I added the /*+ materialize */  hint, but still giving the same explain plan.
sdstuberCommented:
can you at least show the query that reads the "with" query?
huangs3Author Commented:
Hi sdstuber:

    I attached a copy of SQL query, so you can have a look at its structure.
    I just notice that if I delete the return of "shape" field in the WITH clause and the corresponding UNION ALL components underneath, the EXPLAIN PLAN will buffer the inline view from WITH clause, such that the query will become very fast. I don't know why it works like this, and I still need the "shape" field.
    The Oracle database is a versioned GIS database installed with ESRI SDE. I am not sure whether this is a matter... I think the most urgent thing I currently want is to buffer the WITH clause inline view...

    Thank you.
foip-temporary-work2.sql
Franck PachotOracle DBACommented:
Hi,

I'll think about what can prevent the materialization of the view. Can you post the execution plan as well ?

If it is urgent, I see 3 possible workarounds:
 - create a global temporary table, load it with the 'with' query and then do the unions on it
 - get the 4 columns st_x (shape) , st_y (shape) , ... from the selected with' query as it iseems that the function has something to do with the issue
 - use the model clause instead of all those union all

Regards,
Franck.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
huangs3Author Commented:
Hi franckpachot:

    I just attached the execution plan in the four pictures. Some table names and actual condition variable names were hidden, but hopefully it can tell what is going on.

> create a global temporary table, load it with the 'with' query and then do the unions on it
This seems to be a possible solution for me, however, creating temp table is not recommanded and it will cost a lot of maintenance work in future...

 - get the 4 columns st_x (shape) , st_y (shape) , ... from the selected with' query as it iseems that the function has something to do with the issue
If I remove them the execution plan will be changed, but I still need to return them at this time.

 - use the model clause instead of all those union all
I will look into it.

Thank you.
plan-over-view.jpg
plan-part1.jpg
plan-part2.jpg
plan-part3.jpg
Franck PachotOracle DBACommented:
Hi,
From what you said, I thought you had 26 times the 'with' view. But you have 'only' 4, right ?
Are the functions deterministic ?
Just an idea, I'm not sure that it is the problem.
A way to understand that wiyld be to trace the optimize (event 10056) but that will probably be unreadable with a so complex query...
Regards,
Franck.
huangs3Author Commented:
Hi Franckpachot:

    Actually I think I have about 30 times the 'with' view theoretically, but actually I couldn't  wait until it finish generate the plan (it takes too long). Hence I cut it to four by deleting something for research. Because once the four get combined to one, the 30 should also be combined.
    I don't think I have the privilege to trace......
    On another hand, I got an work around:
**********************************************************************************
WITH
a as (complicated SQL returning many columns but not the GIS shape) ,
b as (complicated SQL returning only the GIS shape and Id columns)
(select field1 ...... from a
  UNION ALL
   ......
  select field24 ...... from a
  UNION ALL
  select  st_x(shape) ...... from b
  UNION ALL
  select st_y(shape) ...... from b
  UNION ALL
   select area(shape) ...... from b
    UNION ALL
    select length(shape) ...... from b
)
************************************************************
then the with clause is only repeated 4 times by the shape field.

Regards
Franck PachotOracle DBACommented:
Ok,


about:
>> If I remove them the execution plan will be changed, but I still need to return them at this time.

I was not telling to remove them but to have 4 columns in the with clause instead of one:
WITH ... st_x(shape) xshape , st_y(shape) yshape
...
select  xshape  ...... from b
  UNION ALL
  select st_y(shape) ...... from b

Regards,
Franck.
Franck PachotOracle DBACommented:
I'll try to reproduce your problem...
Franck PachotOracle DBACommented:
Hi,

Here is the result:
- oracle do not materialize the view by default
- but it does with the materialize hint, or the 'rownum trick'
- the cost is however better when materialized

So I did not reproduce your problem, but you can check the materialize hint and the 'rownum trick' to see if youare doing them right.

Regards,
Franck.
SQL> create table zt as select rownum n , lpad('X',4000,'x') x from dual connect by level<=1000;
Table created.

SQL> create function zf1(n number) return number as begin return dbms_random.value; end;
  2  /

Function created.
SQL> create function zf2(n number) return number as begin return dbms_random.value; end;
  2  /

Function created.
SQL> create function zf3(n number) return number as begin return dbms_random.value; end;
  2  /
Function created.

SQL> set autotrace trace explain

SQL> with w as ( select * from zt )
  2  select 'n1' id , zf1(n) val from w
  3  union all
  4  select 'n1' id , zf1(n) val from w
  5  union all
  6  select 'n3' id , zf3(n) val from w
  7  /

Execution Plan
----------------------------------------------------------
Plan hash value: 3994904889

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  3696 | 48048 |   394  (67)| 00:00:06 |
|   1 |  UNION-ALL         |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| ZT   |  1232 | 16016 |   131   (0)| 00:00:02 |
|   3 |   TABLE ACCESS FULL| ZT   |  1232 | 16016 |   131   (0)| 00:00:02 |
|   4 |   TABLE ACCESS FULL| ZT   |  1232 | 16016 |   131   (0)| 00:00:02 |
---------------------------------------------------------------------------

SQL> with w as ( select /*+ materialize */ * from zt )
  2  select 'n1' id , zf1(n) val from w
  3  union all
  4  select 'n2' id , zf1(n) val from w
  5  union all
  6  select 'n3' id , zf3(n) val from w
  7  /

Execution Plan
----------------------------------------------------------
Plan hash value: 757117961

----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |  3696 | 48048 |   196  (67)| 00:00:03 |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |
|   2 |   LOAD AS SELECT           |                             |       |       |            |          |
|   3 |    TABLE ACCESS FULL       | ZT                          |  1232 |  2424K|   131   (0)| 00:00:02 |
|   4 |   UNION-ALL                |                             |       |       |            |          |
|   5 |    VIEW                    |                             |  1232 | 16016 |    65   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D66AB_6441FAE5 |  1232 |  2424K|    65   (0)| 00:00:01 |
|   7 |    VIEW                    |                             |  1232 | 16016 |    65   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D66AB_6441FAE5 |  1232 |  2424K|    65   (0)| 00:00:01 |
|   9 |    VIEW                    |                             |  1232 | 16016 |    65   (0)| 00:00:01 |
|  10 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D66AB_6441FAE5 |  1232 |  2424K|    65   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

SQL> with w as ( select * from zt where rownum<1000 )
  2  select 'n1' id , zf1(n) val from w
  3  union all
  4  select 'n1' id , zf1(n) val from w
  5  union all
  6  select 'n3' id , zf3(n) val from w
  7  /

Execution Plan
----------------------------------------------------------
Plan hash value: 3487038456

----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |  2997 | 38961 |   159  (67)| 00:00:03 |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |
|   2 |   LOAD AS SELECT           |                             |       |       |            |          |
|*  3 |    COUNT STOPKEY           |                             |       |       |            |          |
|   4 |     TABLE ACCESS FULL      | ZT                          |  1232 |  2424K|   106   (0)| 00:00:02 |
|   5 |   UNION-ALL                |                             |       |       |            |          |
|   6 |    VIEW                    |                             |   999 | 12987 |    53   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D66AD_6441FAE5 |   999 |  1965K|    53   (0)| 00:00:01 |
|   8 |    VIEW                    |                             |   999 | 12987 |    53   (0)| 00:00:01 |
|   9 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D66AD_6441FAE5 |   999 |  1965K|    53   (0)| 00:00:01 |
|  10 |    VIEW                    |                             |   999 | 12987 |    53   (0)| 00:00:01 |
|  11 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D66AD_6441FAE5 |   999 |  1965K|    53   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

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

   3 - filter(ROWNUM<1000)

Open in new window

huangs3Author Commented:
I would consider this case as resolved, because I found the cause (the "shape" column related to the SDE by ESRI). This problem is more than just Oracle database problem and fortunately I still got a solution (isolate "shape" column).

Thank you so much you guys to point out various possibilities and diagonise methods leading me to the solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.