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!
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!
can you post the actual query?
With will always buffer the query result. I think problem may be with very big size of output of query..
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
> 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!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I added the /*+ materialize */ hint, but still giving the same explain plan.
can you at least show the query that reads the "with" query?
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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.
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.
ASKER
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
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
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.
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.
I'll try to reproduce your problem...
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.
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)ASKER
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.
Thank you so much you guys to point out various possibilities and diagonise methods leading me to the solution.