Should one revisit existing code that uses inline
Ganesh, June 28, 2002 - 12:41 pm UTC
Thanks for the prompt response.
The plans being different for the two queries, do you think one should replace all of the existing code using inline with sql-with or tune it on a case by case basis .
Is it even worth it?
Thanks,
Ganesh.
June 28, 2002 - 2:00 pm UTC
I tune where tuning is necessary. It is not worthwhile (in my opinion) to revisit every line of code everytime a new feature comes out.
Differences between views, inline views and subquery factoring
andrew, June 28, 2002 - 3:17 pm UTC
Tom
As you've shown, inline views and "subquery factoring" are different. Please could you point out differences between inline views, views and "subquery factoring". Automatic query re-writes is one area of uncertainty to me.
June 28, 2002 - 5:16 pm UTC
There is very little difference between an inline view and a view view. A view view is simply a stored query. An inline view just has you put the query right there. I would say an inline view and a view view are the same for all intents and purposes.
This page describes the benefit of a WITH clause (subquery factoring). It is a way to tell the optimizer "use this result twice, it is the same"
Can I use this in a ctas?
A reader, January 08, 2003 - 5:08 pm UTC
Can the With clause be used in a ctas?
January 08, 2003 - 6:32 pm UTC
ops$tkyte@ORA920.US.ORACLE.COM> drop table t;
Table dropped.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create table t as
2 with
3 free_space_by_tablespace as
4 ( select sum(bytes)/1024 Kbytes_free,
5 max(bytes)/1024 largest,
6 tablespace_name
7 from sys.dba_free_space
8 group by tablespace_name ),
9 space_by_permanent_tablespace as
10 ( select sum(bytes)/1024 Kbytes_alloc,
11 sum(maxbytes)/1024 Kbytes_max,
12 tablespace_name
13 from sys.dba_data_files
14 group by tablespace_name ),
15 space_by_temporary_tablespace as
16 ( select sum(bytes)/1024 Kbytes_alloc,
17 sum(maxbytes)/1024 Kbytes_max,
18 tablespace_name
19 from sys.dba_temp_files
20 group by tablespace_name )
21 /* Now, finally to the query itself */
22 select b.tablespace_name name,
23 kbytes_alloc kbytes,
24 kbytes_alloc-nvl(kbytes_free,0) used,
25 nvl(kbytes_free,0) free,
26 ((kbytes_alloc-nvl(kbytes_free,0))/
27 kbytes_alloc)*100 pct_used,
28 nvl(largest,0) largest,
29 nvl(kbytes_max,kbytes_alloc) Max_Size,
30 decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
31 from free_space_by_tablespace a
32 RIGHT OUTER JOIN
33 space_by_permanent_tablespace b on a.tablespace_name = b.tablespace_name
34 union all
35 select b.tablespace_name,
36 kbytes_alloc kbytes,
37 kbytes_alloc-nvl(kbytes_free,0) used,
38 nvl(kbytes_free,0) free,
39 ((kbytes_alloc-nvl(kbytes_free,0))/
40 kbytes_alloc)*100 pct_used,
41 nvl(largest,0) largest,
42 nvl(kbytes_max,kbytes_alloc) Max_Size,
43 decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
44 from free_space_by_tablespace a
45 RIGHT OUTER JOIN
46 space_by_temporary_tablespace b on a.tablespace_name = b.tablespace_name
47 /
Table created.
ops$tkyte@ORA920.US.ORACLE.COM>
immature oracle designers
harakiri, January 08, 2003 - 11:48 pm UTC
First, the plan in 9i is no longer a simple hierarchical query. The correct output is still produced by utlxpls, of course. All homegrown plan queries are rendered invalid with new temporary table transformation.
The plan contains:
TABLE ACCESS FULL SYS_TEMP_0FD9D6600_338EED1D
in 2 places. A temporary table build under the node:
RECURSIVE EXECUTION SYS_LE_2_0
There is no match in the names whatsoever! Oracle probably don't have a clue that execution plan in the new design is essentially a Directed Acyclic Graph (DAG). DAG still can be represented as adjacency list with parent_id referring to the parent row without need to invent kludgy names and link by the other columns. I must be naive to expect a simple design from oracle.
Second, what is new manageabilty stuff with all those advisory views? Am I supposed to learn more internal details? I was assuming that oracle would remove all the unnecessary levels of implementation details, straighten up the code, and automatically size up all those internal caches and areas. Instead, I have to read more documentation! Isn't oracle just throwing more features and call it manageability? Typical oracle: simplicity, via adding more complexity!
January 09, 2003 - 7:23 am UTC
what the heck are you talking about and how does this at all relate to subquery factoring and the with clause???
I have no clue what you are talking about. the plan table is still the plan table is still the plan table.
You have no idea the complexity of the database apparently either.
I don't get it with the advisory comment either. Lets see, you fire up OEM, OEM tells you graphically and textually "hey, you know what, if you sized the buffer cache to X, the shared pool to Y -- things would be better. Would you like me to do that for you?". You press button and wah-lah, done.
Tell me, how did you learn about directed acyclic graphs (might big words). I'm sure you didn't invent them -- I'll bet you READ about them. You know, in some document somewhere?
It would really be frustrating don't you think if you sized the buffer cache to M and the shared pool to N and came in the next day to find that the database set it to X and Y without telling you?
Remember the line "Open the pod bay doors Hal, I'm sorry Dave, I'm afraid I can't do that"
</code>
http://www.moviesounds.com/2001/imsorry.wav <code>
not sure I want my database reconfiguring itself at 2pm during peak load all by itself -- do you...
If you have a real question about "how do i", and want to ask it, feel free to submit it some day.
plan diff
mikito, January 09, 2003 - 12:53 pm UTC
Run utlxpls and compare the output with the output you printed in the reply above. There is a whole subtree missing:
| 1 | RECURSIVE EXECUTION | SYS_LE_2_0 | | | |
| 0 | INSERT STATEMENT | | 83 | 2490 | 135 |
| 1 | LOAD AS SELECT | | | | |
| 2 | SORT GROUP BY | | 83 | 2490 | 135 |
| 3 | VIEW | | 83 | 2490 | 133 |
| 4 | UNION-ALL | | | | |
| 5 | NESTED LOOPS | | 82 | 9922 | 99 |
| 6 | NESTED LOOPS | | 82 | 7790 | 99 |
|* 7 | TABLE ACCESS FULL | TS$ | 82 | 4592 | 17 |
|* 8 | TABLE ACCESS CLUSTER | FET$ | 1 | 39 | 1 |
|* 9 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 26 | |
| 10 | NESTED LOOPS | | 1 | 147 | 34 |
| 11 | NESTED LOOPS | | 1 | 121 | 34 |
|* 12 | TABLE ACCESS FULL | TS$ | 1 | 82 | 17 |
|* 13 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) | 1 | 39 | |
|* 14 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 26 | |
Now the technical question. What is this funny name SYS_LE_2_0. How does this relate to 'SYS_TEMP_0FD9D660A_26900'?
January 09, 2003 - 2:14 pm UTC
Oh -- it's you. The guy who wants the software to do all of our thinking for us. Now I understand the aggressive, somewhat beligerent and superior tone better.
never mind.
(seems funny, you know what to do, you just want to complain about it. oh well.)
The sys_ names are of course just system generated names -- like you would see for constraints that are not named. there need not be any implied relation between the two you see. They are just temporary names for temporary objects -- their names are not meaningful.
diffreence between inline view and WHERE clause
A reader, January 09, 2003 - 5:03 pm UTC
Hi
since this topic is about inline views difference with other clauses I have a doubt
if I have a query like
select ename, dname
from emp, dept
where emp.deptno = dept.deptno
and b.deptno = 10
and emp.empno between 7500 and 7600
what would it differ from
select ename, dname
from (select * from emp where empno between 7500 and 7600) a,
(select * from dept where deptno = 10) b
considering emp.empno, emp.deptno and dept.deptno are indexed?
May be this example we wont see much difference since it's only two small tables but with joins of 4 or 5 tables will changing WHERE clause in Inline views change dramatically the execution plan?
January 09, 2003 - 7:04 pm UTC
It can -- it might not. I can give examples where it will, and some where it won't. Especially when outer joins are involved (they it almost certainly WILL change the plan and the results).
In the case like above where the two queries are semantically equivalent, it'll most likely not change anything.
Smaller Query?
Martin, February 27, 2003 - 9:27 am UTC
Hi Tom,
don't worry this is not a question about directed acyclic graphs ( I have enough trouble spelling it, never mind what it is! ;-)).
You're example above of how subquery factoring can tell the optimiser to reuse the relevant parts of the subquery is great, but was wondering if you could do a quick example of a much simpler query which can benefit from the WITH clause? Every example I've tried seems to make no benefit in plan / stats.
Thanks in advance
February 27, 2003 - 7:09 pm UTC
what you need is a relatively expensive subquery, used many times. Consider:
big_table@ORA920> set echo on
big_table@ORA920> set autotrace on statistics
big_table@ORA920>
big_table@ORA920> with owners
2 as
3 ( select distinct owner username from all_objects )
4 select count(*)
5 from all_objects, owners
6 where all_objects.owner = owners.username
7 union all
8 select count(*)
9 from dba_objects, owners
10 where dba_objects.owner = owners.username
11 /
COUNT(*)
----------
30537
30977
2 rows selected.
Statistics
----------------------------------------------------------
4 recursive calls
7 db block gets
302441 consistent gets
1 physical reads
520 redo size
418 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
2 rows processed
big_table@ORA920>
big_table@ORA920> select count(*)
2 from all_objects, (select distinct owner username from all_objects ) owners
3 where all_objects.owner = owners.username
4 union all
5 select count(*)
6 from dba_objects, (select distinct owner username from all_objects ) owners
7 where dba_objects.owner = owners.username
8 /
COUNT(*)
----------
30537
30977
2 rows selected.
Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
442058 consistent gets
0 physical reads
0 redo size
418 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
2 rows processed
big_table@ORA920> set autotrace off
big_table@ORA920>
See the 75%++ reduction in LIO's -- we didn't have to distinct owner from all_users two times.
Hmmm..
Martin, February 28, 2003 - 3:16 am UTC
Hi Tom,
thanks for the response, but I can't seem to replicate the savings you show, i.e.
SQL> ed
Wrote file afiedt.buf
1 with owners
2 as
3 ( select distinct owner username from all_objects )
4 select count(*)
5 from all_objects, owners
6 where all_objects.owner = owners.username
7 union all
8 select count(*)
9 from dba_objects, owners
10* where dba_objects.owner = owners.username
SQL> /
COUNT(*)
----------
39911
40088
2 rows selected.
Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view
SP2-0612: Error generating AUTOTRACE EXPLAIN report
Statistics
----------------------------------------------------------
521 recursive calls
8 db block gets
798808 consistent gets
261 physical reads
0 redo size
426 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
18 sorts (memory)
1 sorts (disk)
2 rows processed
SQL> select count(*)
2 2 from all_objects, (select distinct owner username from all_objects )
3 owners
4 3 where all_objects.owner = owners.username
5 4 union all
6 5 select count(*)
7 6 from dba_objects, (select distinct owner username from all_objects )
8 owners
9 7 where dba_objects.owner = owners.username
10
SQL> ed
Wrote file afiedt.buf
1 select count(*)
2 from all_objects, (select distinct owner username from all_objects )
3 owners
4 where all_objects.owner = owners.username
5 union all
6 select count(*)
7 from dba_objects, (select distinct owner username from all_objects )
8 owners
9* where dba_objects.owner = owners.username
SQL> /
COUNT(*)
----------
39911
40088
2 rows selected.
Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view
SP2-0612: Error generating AUTOTRACE EXPLAIN report
Statistics
----------------------------------------------------------
28 recursive calls
6 db block gets
798699 consistent gets
255 physical reads
0 redo size
426 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
1 sorts (disk)
2 rows processed
I think this is the reason why I'm having difficulty finding situations where using the WITH works better, in the
above situation it seems worse!
February 28, 2003 - 9:46 am UTC
It'll vary by version -- I'll bet you have 9iR1 perhaps? Here is another test from 9iR2 that shows the differences in the plans (i ran the queries 2 times to avoid the recursive sql counts for parsing which accounts for the nomimal differences you see above perhaps?)
ps$tkyte@ORA920> create table t1 as select * from all_objects;
Table created.
ops$tkyte@ORA920> create table t2 as select * from dba_objects;
Table created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> analyze table t1 compute statistics
2 for table for all indexes for all indexed columns;
Table analyzed.
ops$tkyte@ORA920> analyze table t2 compute statistics
2 for table for all indexes for all indexed columns;
Table analyzed.
ops$tkyte@ORA920>
ops$tkyte@ORA920> with owners
2 as
3 ( select distinct owner username from t1 )
4 select count(*)
5 from t1, owners
6 where t1.owner = owners.username
7 union all
8 select count(*)
9 from t2, owners
10 where t2.owner = owners.username
11 /
COUNT(*)
----------
29648
30089
ops$tkyte@ORA920> set autotrace on
ops$tkyte@ORA920> /
COUNT(*)
----------
29648
30089
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=138 Card=2 Bytes=68)<b>
1 2 RECURSIVE EXECUTION OF 'SYS_LE_2_0'
2 0 TEMP TABLE TRANSFORMATION</b>
3 2 UNION-ALL
4 3 SORT (AGGREGATE)
5 4 HASH JOIN (Cost=68 Card=948736 Bytes=32257024)
6 5 TABLE ACCESS (FULL) OF 'T1' (Cost=42 Card=29648 Bytes=504016)
7 5 VIEW (Cost=8 Card=29648 Bytes=504016)
8 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6603_F54CC05A' (Cost=8 Card=29648 Bytes=504016)
9 3 SORT (AGGREGATE)
10 9 HASH JOIN (Cost=70 Card=962848 Bytes=32736832)
11 10 VIEW (Cost=8 Card=29648 Bytes=504016)
12 11 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6603_F54CC05A' (Cost=8 Card=29648 Bytes=504016)
13 10 TABLE ACCESS (FULL) OF 'T2' (Cost=43 Card=30089 Bytes=511513)
Statistics
----------------------------------------------------------
4 recursive calls
7 db block gets
1239 consistent gets
1 physical reads
528 redo size
418 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
ops$tkyte@ORA920> set autotrace off
ops$tkyte@ORA920>
ops$tkyte@ORA920> select count(*)
2 from t1, (select distinct owner username from t1 ) owners
3 where t1.owner = owners.username
4 union all
5 select count(*)
6 from t2, (select distinct owner username from t1 ) owners
7 where t2.owner = owners.username
8 /
COUNT(*)
----------
29648
30089
ops$tkyte@ORA920> set autotrace on
ops$tkyte@ORA920> /
COUNT(*)
----------
29648
30089
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=430 Card=2 Bytes=68)
1 0 UNION-ALL
2 1 SORT (AGGREGATE)
3 2 HASH JOIN (Cost=214 Card=29648 Bytes=1008032)
4 3 TABLE ACCESS (FULL) OF 'T1' (Cost=42 Card=29648 Bytes=504016)
5 3 VIEW (Cost=154 Card=29648 Bytes=504016)
6 5 SORT (UNIQUE) (Cost=154 Card=29648 Bytes=504016)
7 6 TABLE ACCESS (FULL) OF 'T1' (Cost=42 Card=29648 Bytes=504016)
8 1 SORT (AGGREGATE)
9 8 HASH JOIN (Cost=216 Card=30089 Bytes=1023026)
10 9 VIEW (Cost=154 Card=29648 Bytes=504016)
11 10 SORT (UNIQUE) (Cost=154 Card=29648 Bytes=504016)
12 11 TABLE ACCESS (FULL) OF 'T1' (Cost=42 Card=29648 Bytes=504016)
13 9 TABLE ACCESS (FULL) OF 'T2' (Cost=43 Card=30089 Bytes=511513)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1639 consistent gets
0 physical reads
0 redo size
418 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
2 rows processed
ops$tkyte@ORA920> set autotrace off
<b>when I did this in 9iR1 -- it was a bit different as the optimizer chose:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3517 Card=2 Bytes=68)
1 0 UNION-ALL
2 1 SORT (AGGREGATE)
3 2 MERGE JOIN (Cost=1754 Card=22196 Bytes=754664)
4 3 SORT (JOIN) (Cost=877 Card=22196 Bytes=377332)
5 4 VIEW (Cost=877 Card=22196 Bytes=377332)
6 5 SORT (UNIQUE) (Cost=877 Card=22196 Bytes=377332)
7 6 TABLE ACCESS (FULL) OF 'T1' (Cost=23 Card=22196 Bytes=377332)
8 3 SORT (JOIN) (Cost=877 Card=22196 Bytes=377332)
9 8 TABLE ACCESS (FULL) OF 'T1' (Cost=23 Card=22196 Bytes=377332)
10 1 SORT (AGGREGATE)
11 10 MERGE JOIN (Cost=1763 Card=22273 Bytes=757282)
12 11 SORT (JOIN) (Cost=877 Card=22196 Bytes=377332)
13 12 VIEW (Cost=877 Card=22196 Bytes=377332)
14 13 SORT (UNIQUE) (Cost=877 Card=22196 Bytes=377332)
15 14 TABLE ACCESS (FULL) OF 'T1' (Cost=23 Card=22196 Bytes=377332)
16 11 SORT (JOIN) (Cost=887 Card=22273 Bytes=378641)
17 16 TABLE ACCESS (FULL) OF 'T2' (Cost=23 Card=22273 Bytes=378641)
for both plans</b>
different init.ora settings and such will affect the outcome as well.
Error with with in SQQPLUS
A Reader, February 28, 2003 - 9:28 am UTC
Hi Tom,
When I run one of the examples in SQLPLUS, I get the following error:
SQL> with owners
SP2-0734: unknown command beginning "with owner..." - rest of line ignored.
SQL> as
SP2-0042: unknown command "as" - rest of line ignored.
SQL> ( select distinct owner username from all_objects )
When I copy the whole statment into buffer, I get the following:
SP2-0642: SQL*Plus internal error state 2091, context 0:0:0
Unsafe to proceed
I am running 9.2 Standard Edition. Does this version support the "with"?
Thank you.
February 28, 2003 - 10:14 am UTC
yes it does however you are using 8i or before sqlplus ;)
SQLPlus is the guy having heartburn with "with" -- see it is an SP error, not an ORA error.
Aha!
Martin, February 28, 2003 - 10:11 am UTC
Yep, in 9.2 it does seem to be significantly better, and I get the kind of savings that you show, yet in 9.1 it's worse. Oh well, another excuse (as if we needed any) to go to 9.2.
Thanks for all your help.
February 28, 2003 - 10:37 am UTC
Well, it is not "worse" -- just not "any better in some cases". Here is the tkprof from 9iR1 for the original example. I chopped the plans out cause they are huge -- but -- they were the same.
with owners
as
( select distinct owner username from all_objects )
select count(*)
from all_objects, owners
where all_objects.owner = owners.username
union all
select count(*)
from dba_objects, owners
where dba_objects.owner = owners.username
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.14 0.13 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 14.68 15.28 0 411570 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 14.82 15.41 0 411570 0 2
select count(*)
from all_objects, (select distinct owner username from all_objects ) owners
where all_objects.owner = owners.username
union all
select count(*)
from dba_objects, (select distinct owner username from all_objects ) owners
where dba_objects.owner = owners.username
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.10 0.10 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 14.30 15.10 0 411570 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 14.40 15.21 0 411570 0 2
As an addendum...
Martin, February 28, 2003 - 10:15 am UTC
Actually, Tom, further quick question, since the plans for the WITH (and WITHout..) at 9.1 are the same, does this mean we can really think of the WITH statement (at 9.1) as shorthand for the major query? "Under the covers" is this what oracle's doing?
Thanks
February 28, 2003 - 10:49 am UTC
Not really - it all depends on the query. Consider this now (9iR1):
ops$tkyte@ORA9I> set autotrace on statistics
ops$tkyte@ORA9I> with owners
2 as
3 ( select distinct owner username from all_objects where rownum >= 1)
4 select count(*)
5 from all_objects, owners
6 where all_objects.owner = owners.username
7 union all
8 select count(*)
9 from dba_objects, owners
10 where dba_objects.owner = owners.username
11 /
COUNT(*)
----------
22197
22273
Statistics
----------------------------------------------------------
4 recursive calls
10 db block gets
280407 consistent gets
1 physical reads
516 redo size
426 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
2 rows processed
ops$tkyte@ORA9I> select count(*)
2 from all_objects, (select distinct owner username from all_objects where rownum >= 1) owners
3 where all_objects.owner = owners.username
4 union all
5 select count(*)
6 from dba_objects, (select distinct owner username from all_objects where rownum >= 1) owners
7 where dba_objects.owner = owners.username
8 /
COUNT(*)
----------
22197
22273
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
411570 consistent gets
0 physical reads
0 redo size
426 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
2 rows processed
ops$tkyte@ORA9I> set autotrace off
In 9iR1 is seems that if the optimizer can merge the subquery -- it will. Adding rownum (aggregates, order bys, set operations like intersect and such) precludes a simple merge so it materialized the subquery once off to the side and reused it twice in the first query.
In the second query -- it did it twice.
potential to use the same sub query twice
A reader, July 06, 2003 - 9:41 am UTC
ops$tkyte@ORA9I> set autotrace on statistics
ops$tkyte@ORA9I> with owners
2 as
3 ( select distinct owner username from all_objects where rownum >= 1)
4 select count(*)
5 from all_objects, owners
6 where all_objects.owner = owners.username
7 union all
8 select count(*)
9 from dba_objects, owners
10 where dba_objects.owner = owners.username
11 /
Tom in the above , can you explain.. where is the potential to to use ( select distinct owner username from all_objects where rownum >= 1) , twice?
July 06, 2003 - 4:29 pm UTC
we are joining two different views -- ALL_OBJECTS and DBA_OBJECTS to this factored subquery -- so we use it twice.
Lines 5 and 9
In PL/SQL?
Kashif, July 11, 2003 - 12:05 pm UTC
Hi Tom,
Can the with clause be used inside pl/sql? Unfortunately I don't have access to 9i so I'm not able to play around with this feature. I couldn't figure it out from the New Features and Warehousing guides, and I couldn't even find an explanation for the WITH clause in the 9i SQL Reference.
Secondly, are the developers of the CBO not allowed to discuss any potential features and improvements they have included in a new release of the CBO? It seems like we're frequently guessing what the optimizer does from one release to the next, and observing radical differences in a feature in two different releases, without having any advance knowledge or expectation of the difference. Or am I not reading some document somewhere? Many thanks.
Kashif
OK
Catherine, February 25, 2004 - 8:11 am UTC
Dear Tom,
Can the following inline view be transformed into a
correlated subquery?
sql> select deptno,max_sal,(select ename from emp where
sal = max_sal and rownum = 1),min_sal,(select ename
from emp where sal = min_sal and rownum = 1)
from(
select deptno,max(sal) as max_sal,min(sal) as
min_sal from emp group by deptno
);
Could you please help?
Thanks in advance.
February 25, 2004 - 9:30 am UTC
I do not see the relationship between
a) inline view -- the equivalent of a view or table to be selected from
b) a correlated subquery -- something you use in a predicate to restrict rows.
Your query looks a bit funky. The question it seems to be answering is:
generate the max sals by DEPTNO
report back a random ename that makes that sal by deptno, even if that ename doesn't work in that DEPT!!!
consider:
ops$tkyte@ORA920PC> create table emp ( deptno number, sal number, ename varchar2(20) );
Table created.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> insert into emp values ( 10, 100, 'first row' );
1 row created.
ops$tkyte@ORA920PC> insert into emp values ( 10, 50, 'second row' );
1 row created.
ops$tkyte@ORA920PC> insert into emp values ( 20, 100, 'third row' );
1 row created.
ops$tkyte@ORA920PC> insert into emp values ( 20, 50, 'fourth row' );
1 row created.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select deptno,
2 max_sal,(select ename from emp where sal = max_sal and rownum = 1),
3 min_sal,(select ename from emp where sal = min_sal and rownum = 1)
4 from( select deptno,max(sal) as max_sal,min(sal) as
5 min_sal from emp group by deptno
6 );
DEPTNO MAX_SAL (SELECTENAMEFROMEMPW MIN_SAL (SELECTENAMEFROMEMPW
---------- ---------- -------------------- ---------- --------------------
10 100 first row 50 second row
20 100 first row 50 second row
<b> is that what you really wanted? or is this what you meant:</b>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select distinct deptno,
2 first_value(sal) over (partition by deptno order by sal, rowid) min_sal,
3 first_value(ename) over (partition by deptno order by sal, rowid) min_ename,
4 first_value(sal) over (partition by deptno order by sal DESC, rowid) max_sal,
5 first_value(ename) over (partition by deptno order by sal DESC, rowid) max_ename
6 from emp
7 /
DEPTNO MIN_SAL MIN_ENAME MAX_SAL MAX_ENAME
---------- ---------- -------------------- ---------- --------------------
10 50 second row 100 first row
20 50 fourth row 100 third row
WITH and complex types
vll, February 25, 2004 - 9:46 am UTC
Hi, Tom!
Great thanks as usual for all you are doing for us!!!
I'm using WITH a lot in my new projects and it really helps. But recently I tried to include in WITH query some complex types (mostly - XMLTYPE fields) from one of my tables. And I never had a chance to make it working - it just freezes and after a minute or 2 gives an error "lost communication with a server" or someting like this. It's 9i release 2. All my tests just tell me that with simple types WITH is working fine, as soon as you have complex type - you have a problem. Are you by chance aware of any problem with this? I can post my query, but it's really large and I don't think it can help...
February 25, 2004 - 9:47 am UTC
if you have an example that returns with the 3113 - -please file a tar with support, that would be a "bug"
I'm getting the same error
Steve, February 26, 2004 - 8:02 pm UTC
I'm also getting the 3113 error (on 9.2.0.4), but it's happening inconsistently. For a while, the error was coming up about 50% of the time. A few hours later, it was less than 10%. When the error doesn't happen, I find no problems with the results of the process.
Briefly, my process is running a packaged procedure which passes a PL/SQL collection (varray) to a remote (dblink) packaged procedure which creates a flat file for that data using UTL_FILE. In the procedure that builds the PL/SQL collection, several other collections (each is a SQL table type) are used to pass data between packaged functions. These collections are being used in SQL statements using "TABLE( CAST(" in the functions.
One of the things I've had a problem with is deciding where to define the PL/SQL types (remote site or local site). Both options have resulted in problems. The current setup is defining on the remote site.
I don't know if "vll from USA" has a similar setup, but if so, I hope you will let us know if you find a solution, and I will do the same.
Tom, can you think of any hints and tips you could give us as far as the best approach for doing this kind of thing?
February 27, 2004 - 7:19 am UTC
you *need* to contact support to resolve a 3113 like this. it means "back end went away - crashed".
Found a way around the 3113
Steve, February 27, 2004 - 8:54 pm UTC
It doesn't appear that anything was actually crashing, since I was using both instances in other windows which continued to work before and after the error. Anyway, I found a workaround for the error, which I'll describe below in case anyone is doing something similar. But first, a little soapbox action:
I was reluctant to contact Oracle Support because, in the 15+ years that I've worked on Oracle, I've gotten very little help from them. The responses I usually get are either that I have to upgrade to a later version or that the feature I'm using wasn't meant to be used that way. In either case, I spend hours communicating with them and still have to find a workaround. So now I always try to find a workaround first and contact Support only as a last resort.
<getting off soapbox>
The application I'm working on is available on an internet site and allows the users to retrieve a variety of statistical data in various formats (report, chart, or CSV file). There are two instances involved, the first (call it DB1) contains the data warehouse and the second (call it DB2) contains the internet-related data. Each is on a separate Unix box.
The 3113 error was happening in the routines that create the CSV file. A request is initiated on DB2 by a user. Packaged functions and procedures are called on DB1 to build the data in a PL/SQL varray. The varray is passed back to DB2 to build the file using utl_file so the user can download it. Since the same type must be used on both sides to avoid a type mismatch, the type must be defined on either DB1 or DB2 and referenced via dblink by the other.
The first attempt to do this was by calling the packaged routines on DB1 which built the varray and called a procedure on DB2 passing it the varray. When the type was defined on DB1, DB2 didn't recognize the type. When the type was defined on DB2, the 3113 error came up intermittently.
The new variation calls a packaged procedure on DB2 which creates the varray (empty) and passes it as an IN OUT parameter to a procedure on DB1 which calls the other functions to build the varray. When the type was defined on DB2, I wasn't able to make it work. But, when the type was defined on DB2, everything seemed to be okay. I've run it about 50 times so far, and it is working consistently.
Hopefully, this information will be helpful to someone out there. If anyone would like more detail on this let me know.
Tom, thanks for all the help you provide on this site. Sorry for not following your advice on contacting Oracle Support, but, as I said, that's not worked out very well for me in the past.
February 28, 2004 - 10:30 am UTC
your *session* crashed -- your dedicated server crashed. the instance did not fail, your session did.
support is actually pretty good at 3113, ora-600's, things that create trace files. for the "feature wasn't meant to be used like that", i cannot really respond, I'd need an example - but many times i find that to "be the case" (using shared server to run really long transactions -- yet getting upset that no one can log in since the shared servers are all currently busy. using replication for failover/DR yet not having any conflict detection/resolution routines and getting upset when the other site comes backup and starts pushing deferred transactions. the list is very very long)
but, anyway.
If you have a reproducible test case (and it seems you do) it would really benefit us all by filing the tar with it. You have something concrete they can work with -- and instead of others hitting this issue in the future, they would just have it "work". Thats my soapbox -- if you don't tell them there is a problem, problem might well as not exist and problem will never get fixed for anyone.
3113 again
Steve, March 02, 2004 - 5:19 pm UTC
Good point, Tom. I will try to put together a reproducible test case - one which is not so tightly tied to our environment, but still gets the 3113. I need to concentrate on getting my project finished, since it is already overdue, but I should be able to work on this later in the week or early next week. Thanks for your help.
what is wrong here
PRS, April 15, 2004 - 1:22 pm UTC
Following query gives me ORA-904 invalid identifier. Inline view does not see table B. Any idea?
SELECT B.opportunity_id
,B.BO_ID_CUST
,A.WSI_ACCOUNT_ID
,E.WSI_ACCOUNT_NO
,B.FIRST_NAME
,B.LAST_NAME
,B.OPPORTUNITY_STATUS
,f.xlatlongname status_desc
,A.TD_OPP_FLW_UP_ACT
,g.xlatlongname acat_desc
,B.PERSON_ID
,D.NAME
,A.RA_CAMPAIGN_ID
,C.RA_CMPGN_NAME
,B.ROW_ADDED_DTTM
,B.ROW_LASTMANT_DTTM
,B.NEXT_ACTIVITY_DT
,B.ACT_CLOSE_DT
,B.ACT_REVENUE
,B.EST_REVENUE
,B.PHONE
,'' dummy
,h.descrlong
,', Created By ' || h.row_added_oprid || ', on ' || h.row_added_dttm note
FROM PS_WSI_RSF_OPP_FLD A
, PS_RSF_OPPORTUNITY B
, PS_RA_CAMPAIGN C
, PS_RD_PERSON_NAME D
, PS_WSI_ACCOUNT E
,PSXLATITEM F
,PSXLATITEM G
,(select descrlong,row_added_oprid,row_added_dttm
from ( select descrlong,row_added_oprid,row_added_dttm
from ps_rsf_opp_note k
where k.opportunity_id = B.opportunity_id
order by note_seq_nbr DESC )
where rownum = 1) h
WHERE A.OPPORTUNITY_ID = B.OPPORTUNITY_ID
AND A.RA_CAMPAIGN_ID = C.RA_CAMPAIGN_ID(+)
AND B.PERSON_ID = D.PERSON_ID(+)
AND A.WSI_ACCOUNT_ID = E.WSI_ACCOUNT_ID(+)
AND f.fieldvalue = b.opportunity_status
AND f.fieldname = 'OPPORTUNITY_STATUS'
AND e.wsi_account_no = '33312345'
AND g.fieldvalue = A.TD_OPP_FLW_UP_ACT
AND g.fieldname = 'TD_OPP_FLW_UP_ACT'
April 15, 2004 - 2:15 pm UTC
you cannot do that. that would be joining to a correlated subquery.
inline views
raj, April 28, 2004 - 2:32 am UTC
Hi Tom,
which one of the below queries is better:
1)
select
sum(sal) sal,
sum(comm) comm,
sum(sal) + sum(comm) totalsal,
(sum(comm)/sum(sal)) * 100 percent
from
emp
group by deptno
2)
select
sal,
comm,
sal + comm totalsal,
(comm/sal) * 100 percent
(
select
sum(sal) sal,
sum(comm) commision
from
emp
group by deptno
)
in the first query, are the sum(sal), sum(comm) computed more than once because of more occurances?
Version : Oracle 9.2
Can we force the temp table route ?
Adrian, September 01, 2004 - 11:37 am UTC
Tom,
The docs say that subquery factoring enables EITHER a temp table or an in-line view, depending on whichever Oracle deems suitable at the time. Is there a way to force the temp table as the in-line view is causing me serious trouble with additional executions of a user-function which I need to eliminate.
I want to be able to run the SQL with the user-function call INSIDE the WITH clause once, and then be able to reference the function's return value OUTSIDE the WITH clause, without the penalty of re-executing the function. At the moment, the CBO is choosing an ILV over a temp table, causing it to re-execute the function to evaluate it in the outermost predicate. This is causing much pain...
BTW, 9.2.0.5.
Regards
Adrian
September 01, 2004 - 11:54 am UTC
give me example query to work with please.
Read consistency with WITH
Arun Gupta, September 01, 2004 - 12:43 pm UTC
Tom,
When using WITH clause, when does the point in time read consistency start? Is it with the execution of subquery in the WITH clause or when the main SELECT starts?
Thanks.
September 01, 2004 - 1:24 pm UTC
the query is the query -- the "whole thing"
An example...
Adrian, September 02, 2004 - 7:14 am UTC
Tom,
The following is the example. As you can see, a temp table transformation is not occurring, which means that any references to the pseudo-column created from my function call are resulting in new executions. The one that causes the most trouble is the one inside the analytic function.
If I create either a GTT or fixed table of the results of the subquery, then the main select flies...
EXPLAIN PLAN FOR
WITH ilv AS (
SELECT ss7.ss7_rec_id
, dms.dms_rec_id
, ss7.release_call_date
, ss7.start_call_date
, TRUNC(EXTRACT(SECOND FROM (ss7.release_call_date-dms.cdr_date) )) AS date_diff
, ROUND(TO_NUMBER((CAST(ss7.release_call_date AS DATE)-CAST(ss7.start_call_date AS DATE))*86400),4) - NVL(dms.duration, 0) AS duration_diff
, dms_tools.is_matched(
ss7.a_number_suffix,
ss7.b_number,
dms.a_num_suffix,
dms.b_num,
ss7.release_call_date-ss7.start_call_date,
dms.duration
) AS match_type
FROM abb_ss7 ss7
, abb_dms dms
WHERE ss7.release_call_date_int IN ( dms.cdr_date_int, dms.cdr_date_int + 1 )
)
SELECT ss7_rec_id
, dms_rec_id
, match_type
, duration_diff
, date_diff
FROM (
SELECT ss7_rec_id
, dms_rec_id
, match_type
, duration_diff
, date_diff
, ROW_NUMBER() OVER
( PARTITION BY ss7_rec_id
ORDER BY match_type, ABS(duration_diff), ABS(date_diff) ) AS rn
FROM ilv
WHERE match_type != 4
)
WHERE rn = 1
select * from table (dbms_xplan.display)
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 184K| 13M| | 114 |
|* 1 | VIEW | | 184K| 13M| | 114 |
|* 2 | WINDOW SORT PUSHED RANK| | 184K| 17M| | 114 |
| 3 | CONCATENATION | | | | | |
|* 4 | HASH JOIN | | 63 | 6363 | 1008K| 56 |
| 5 | TABLE ACCESS FULL | ABB_SS7 | 14629 | 828K| | 15 |
| 6 | TABLE ACCESS FULL | ABB_DMS | 25231 | 1059K| | 11 |
|* 7 | HASH JOIN | | 63 | 6363 | 1008K| 56 |
| 8 | TABLE ACCESS FULL | ABB_SS7 | 14629 | 828K| | 15 |
| 9 | TABLE ACCESS FULL | ABB_DMS | 25231 | 1059K| | 11 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_003"."RN"=1)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "SS7"."SS7_REC_ID" ORDER BY
"CDR"."DMS_TOOLS"."IS_MATCHED"("SS7"."A_NUMBER_SUFFIX","SS7"."B_NUMBER","DMS"."A
_NUM_SUFFIX","DMS"."B_NUM","SS7"."RELEASE_CALL_DATE"-"SS7"."START_CALL_DATE","DM
S"."DURATION"),ABS(ROUND(TO_NUMBER(TO_CHAR((CAST("SS7"."RELEASE_CALL_DATE" AS
DATE)-CAST("SS7"."START_CALL_DATE" AS DATE))*86400)),4)-NVL("DMS"."DURATION",0))
,ABS(TRUNC(EXTRACT(SECOND FROM "SS7"."RELEASE_CALL_DATE"-"DMS"."CDR_DATE"))))<=1
)
4 - access("SS7"."RELEASE_CALL_DATE_INT"="DMS"."CDR_DATE_INT"+1)
filter("CDR"."DMS_TOOLS"."IS_MATCHED"("SS7"."A_NUMBER_SUFFIX","SS7"."B_NU
MBER","DMS"."A_NUM_SUFFIX","DMS"."B_NUM","SS7"."RELEASE_CALL_DATE"-"SS7"."START_
CALL_DATE","DMS"."DURATION")<>4)
7 - access("SS7"."RELEASE_CALL_DATE_INT"="DMS"."CDR_DATE_INT")
filter("CDR"."DMS_TOOLS"."IS_MATCHED"("SS7"."A_NUMBER_SUFFIX","SS7"."B_NU
MBER","DMS"."A_NUM_SUFFIX","DMS"."B_NUM","SS7"."RELEASE_CALL_DATE"-"SS7"."START_
CALL_DATE","DMS"."DURATION")<>4 AND LNNVL("SS7"."RELEASE_CALL_DATE_INT"="DMS"."C
DR_DATE_INT"+1))
Note: cpu costing is off
So, short of creating a GTT and using a two-stage process, is there a way to force Oracle to use a TT transformation ?
As a test, I thought if I could make two references to the WITH subquery, then Oracle would definitely materialise the resultset. So I added a UNION ALL with a second SELECT FROM ilv WHERE 1=2 but to no avail...
Regards
Adrian
September 02, 2004 - 9:34 am UTC
what is the time to
a) populate the gtt and query it
vs
b) just running the query
and why not have the match_type != 4 in the with subquery?
(although, if you add "and rownum > 0" to the subquery you might find it doing what you asked for)
Major difference...
Adrian, September 02, 2004 - 10:23 am UTC
Tom,
1) 7 mins
2) 12 hours
If I add the match_type != 4 to the subquery, Oracle will re-execute the function, as verified by DBMS_XPLAN ( how did we ever manage without DBMS_XPLAN's predicate section ? ).
I will try the rownum trick...
Regards
Adrian
How on earth... ?
Adrian, September 02, 2004 - 10:41 am UTC
Tom,
That works - DBMS_XPLAN shows TempSpc being used. How on earth does that happen with a ROWNUM > 0 predicate ? And how did you think to add it ?
BTW, the timings I gave before were for volume tests. Using the tables in the query I sent you, the wall clock timings are:-
1) GTT, then INSERT => 38 seconds
2) WITH subquery and ROWNUM > 0 => 38 seconds
3) WITH subquery ( ILV, no TT transformation ) => well, it is still running...
I'd really like to know what is going on and how you thought of it. Also, how persistent will a technique like that be across new releases..
Regards
Adrian
September 02, 2004 - 10:54 am UTC
Effective Oracle by Design -- chapter on effective sql :)
various operations, like rownum, level, set operations, some sorts, aggregations cannot be "merged"
It is quite possible that a NO_MERGE hint would do the trick as well.
I'd better re-read it...
Adrian, September 02, 2004 - 11:50 am UTC
Tom,
Better re-read that section then. Didn't even think about trying to avoid a merge...
Thanks
Adrian
Indexes on temp tables?
A reader, September 14, 2004 - 9:16 pm UTC
I have many data transformation processes that go like
create table temp1 as ....
create table temp2 as select ... from temp1,....
<create indexes on temp1,temp2>
insert into final_table select ... from temp1,temp2,...
and so on
The subquery factoring (WITH clause) feature would be a great fit here, I can just define all my temp tables using the WITH clause and Oracle will either materialize them as statement-scope gtt's or simply inline views.
If the data volumes are large and it does indeed materialize them as temp tables, what about indexes? Right now, I have carefully indexed the temp tables so as to give maximum benefit. How can I do this using the WITH feature?
Thanks
September 15, 2004 - 7:37 am UTC
indexes are pointless and "not the right answer" for large data sets, it would intelligently not create them, saving time, resources.
A reader, September 15, 2004 - 9:31 am UTC
"indexes are pointless and "not the right answer" for large data sets, it would intelligently not create them, saving time, resources"
You are just dodging the question because I pressed the wrong button! Yes, I realize that indexes are not (always) the right answer for large datasets, but as I said, for my particular need and for what I am doing in my INSERT INTO ....WITH...SELECT statement they are critical. Thats why I said that I have carefully crafted indexes that do help.
That said, is there a way for to create temporary indexes on these (potentially "materializable" temp tables) if they are determined to be useful (by the CBO, not me!)?
Thanks
September 15, 2004 - 10:09 am UTC
prove it
not dodging, show me.
A reader, September 15, 2004 - 11:27 am UTC
I will try to come up with a test case from my current process, but dont you admit that this is possible? i.e. indexes might be useful on these intermediate temporarily materialized tables?
If I have something like
insert into new_table
with t as (select * from <join of zillion tables>)
select * from t where c1='foo';
Wouldnt the temp table 't' benefit from a index on "c1" (assuming that only 1 row in a million has the value "foo"?)
Thanks
September 15, 2004 - 11:43 am UTC
nope -- we'd merge the c1='foo' into the with in this case in all likelyhood.
PARALLEL INSERT & WITH Subquery_factoring
Jai Lee, November 11, 2004 - 1:14 am UTC
If I explain or run following INSERT statement, server process' CPU is 100 % under 9.2.0.5.
(alter session enable parallel dml;)
INSERT /*+ APPEND PARALLEL(A 4) */ INTO TAB1 A
WITH MISU_TMP AS
( SELECT .. FROM MISU WHERE.. GROUP BY COL1)
SELECT /*+ USE_HASH(B C) PARALLEL(B 4) PARALEL(C 4)*/ *
FROM TAB2 B, MISU_TMP C
UNION ALL
...
IF there is no PARALLEL Hint at INSERT regardless ENABLE PARALLEL DML, it works.
Q1. INSERT WITH subquery does not work with parallel DML?
Q2. CPU 100% means optimizer cannot parse that statement?
November 11, 2004 - 7:57 am UTC
q1) false
q2) means something -- if this reproduces, suggest you
a) export the affected tables with ROWS=N
b) export the statistics on these segments with dbms_stats to a table
c) export that statistics table
d) list out all NON-DEFAULT init.ora settings
e) upload that into your tar with support -- as it should allow them to
reproduce the issue in a couple of seconds.
force merge on inline SQL using the WITH clause
Marc Werner, November 19, 2004 - 6:04 am UTC
Tom,
why does Oracle (10g) not merge the following:
select a.*
from (
with t as (select 3 c1 from dual)
select *
from any_tableX b join t t1
on (b.id = t1.c1)
) a
plan shows a VIEW clause at top-level !
whereas :
select a.*
from (
select * from tableX b join
(select 3 c1 from dual) t1
on (b.id = t1.c1)
) a
is getting merged ?!
To justify the usage of the WITH clause here -
imagine we got an INLINE query, filtering 30% of an exiting table and join that query 4 times.
Regards,
Marc Werner
November 19, 2004 - 11:04 am UTC
it is DUAL after all. FAST DUAL none the less. You cannot extrapolate what the CBO does with a 1 row fast dual select vs "filtering 30% of an existing table..."
Not sure based on your question whether you are wondering why there is a VIEW materialized or why it didn't materialize the view in the second case.
merge WITH's
Marc Werner, November 23, 2004 - 12:26 pm UTC
Tom,
on top of my 1st query I asked, why Oracle doesnt merge
that statement, because it can merge the semantically equivalent statement beneath.
Here comes an "closer to reality" - example without using dual:
create table t1
as select * from all_objects
where rownum < 500;
analyze table t1 compute statistics;
select tmp.* from (
with l_upd as (
select owner, max( t1.last_ddl_time) last_ddl
from t1
group by owner
)
select u.username, l_upd3.owner from
all_users u join l_upd
on ( l_upd.owner = u.username )
join l_upd l_upd2 on ( l_upd2.owner = u.username )
join l_upd l_upd3 on ( l_upd3.owner = u.username )
) tmp;
Again we get an unecessary materialization (VIEW) at one level under root in the plan !
Why is that ? How can I force that query to get merged ?
It's crucial for DWH's queries - as I got here - to get mergeed across different inline - levels , otherwise u either end up heavily writing temp or coding like :
select a - case when t2.y=1 then 2 else 3 end,
b - case when t2.y=1 then 2 else 3 end,
c - case when t2.y=1 then 2 else 3 end,
...
from t1 join t2 on ...
instead of :
select a - calc1, b - calc1, c - ...
from t1 jon ( select case when t2.y=1 then 2 else 3 end
from t2 ) t2
...
apparently does the with clause cause the optimizer real problems in merging ?!
I am aware of the "merge-killers" :
- rownum, group by
- union
- analyt. functions
Thanks for your patience,
Regards,
Marc.
November 23, 2004 - 1:41 pm UTC
with subquery factoring is specifically "designed" to materialized views as a matter of fact. it is one of its goals.
(and you said you are aware of merge killers -- you have listed group by. well you appear to have a group by)
In your "real case" you have a group by -- you cannot join and then group, you must group and then join.
and your example further -- it references l_upd many many multiple times.
do you understand what it would mean to run that query in a merged fashion??? forget about temp (temp is cheap, buy more temp space) -- that query would never complete. How many times do you want to execute that query???
sure, could it merge? yes.
should it merge? absolutely not.
ops$tkyte@ORA9IR2> select tmp.*
2 from
3 (
4 with l_upd
5 as (
6 select owner, max( t1.last_ddl_time) last_ddl
7 from t1
8 group by owner
9 )
10 select u.username, l_upd3.owner from
11 all_users u join l_upd
12 on ( l_upd.owner = u.username )
13 join l_upd l_upd2 on ( l_upd2.owner = u.username )
14 join l_upd l_upd3 on ( l_upd3.owner = u.username )
15 ) tmp;
USERNAME OWNER
------------------------------ ------------------------------
SYS SYS
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 VIEW
2 3 RECURSIVE EXECUTION OF 'SYS_LE_3_0'
3 1 TEMP TABLE TRANSFORMATION
4 3 MERGE JOIN
5 4 MERGE JOIN
6 5 SORT (JOIN)
7 6 NESTED LOOPS
8 7 NESTED LOOPS
9 8 NESTED LOOPS
10 9 VIEW
11 10 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_5695B330'
12 9 TABLE ACCESS (BY INDEX ROWID) OF 'USER$'
13 12 INDEX (UNIQUE SCAN) OF 'I_USER1' (UNIQUE)
14 8 TABLE ACCESS (CLUSTER) OF 'TS$'
15 14 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
16 7 TABLE ACCESS (CLUSTER) OF 'TS$'
17 16 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
18 5 SORT (JOIN)
19 18 VIEW
20 19 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_5695B330'
21 4 SORT (JOIN)
22 21 VIEW
23 22 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_5695B330'
Statistics
----------------------------------------------------------
4 recursive calls
7 db block gets
29 consistent gets
1 physical reads
520 redo size
437 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select tmp.*
2 from
3 (
4 select u.username, l_upd3.owner from
5 all_users u join
6 ( select owner, max( t1.last_ddl_time) last_ddl
7 from t1
8 group by owner)
9 l_upd on ( l_upd.owner = u.username )
10 join
11 ( select owner, max( t1.last_ddl_time) last_ddl
12 from t1
13 group by owner)
14 l_upd2 on ( l_upd2.owner = u.username )
15 join
16 ( select owner, max( t1.last_ddl_time) last_ddl
17 from t1
18 group by owner)
19 l_upd3 on ( l_upd3.owner = u.username )
20 ) tmp;
USERNAME OWNER
------------------------------ ------------------------------
SYS SYS
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 MERGE JOIN
3 2 VIEW
4 3 SORT (GROUP BY)
5 4 TABLE ACCESS (FULL) OF 'T1'
6 2 SORT (JOIN)
7 6 VIEW
8 7 SORT (GROUP BY)
9 8 NESTED LOOPS
10 9 NESTED LOOPS
11 10 NESTED LOOPS
12 11 TABLE ACCESS (FULL) OF 'T1'
13 11 TABLE ACCESS (BY INDEX ROWID) OF 'USER$'
14 13 INDEX (UNIQUE SCAN) OF 'I_USER1' (UNIQUE)
15 10 TABLE ACCESS (CLUSTER) OF 'TS$'
16 15 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
17 9 TABLE ACCESS (CLUSTER) OF 'TS$'
18 17 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
19 1 SORT (JOIN)
20 19 VIEW
21 20 SORT (GROUP BY)
22 21 TABLE ACCESS (FULL) OF 'T1'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2035 consistent gets
0 physical reads
0 redo size
437 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
<b>Now, which one looks better to you? Thank goodness it materalized the result once so it could use it repeatedly.</b>
heavily using temp is sometimes consider the "price of playing the game" here, there is nothing wrong with using temp when the stuff doesn't fit in to ram -- especially with aggregates (which you in general have to get the last row before you see the FIRST row -- they are not "slow by slow" processing friendly.
TOP-level doesnt merge !!
A reader, November 25, 2004 - 5:45 am UTC
Tom,
sorry, you missed the point - probably I didnt explain sufficiently .
I mean the VIEW on the top level of the plan (1st statement)- you can see
the second query is merged through that top level :
SELECT TMP.* FROM (
...
)
The WITH - statement doesnt merge at the top level even though its mergeable as the second (semantically equal) statement proves !
So why does Oracle write the end - result to temp - when its just selecting 1:1 from it right after :
0 SELECT STATEMENT ...
1 2 VIEW <------------ no need to materialize here !!!
...
Regards,
Marc.
November 25, 2004 - 10:11 am UTC
do you have a test case showing this is "a bad thing(tm)" -- that is, that it was actually materialized and this penalized you in some fashion.
(the view step doesn't always mean "materialized")....
Cause, if you change it to this:
ops$tkyte@ORA9IR2> create table t1
2 as select owner||rownum owner, last_ddl_time from all_objects
3 /
Table created.
ops$tkyte@ORA9IR2> create table t2
2 as
3 select owner username from t1;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T1' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T2' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from plan_table;
25 rows deleted.
ops$tkyte@ORA9IR2> explain plan for
2 select tmp.* from (
3 with l_upd as (
4 select owner, max( t1.last_ddl_time) last_ddl
5 from t1
6 group by owner
7 )
8 select u.username, l_upd3.owner from
9 t2 u join l_upd
10 on ( l_upd.owner = u.username )
11 join l_upd l_upd2 on ( l_upd2.owner = u.username )
12 join l_upd l_upd3 on ( l_upd3.owner = u.username )
13 ) tmp
14 where username like 'SYS%'
15 /
Explained.
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 270 | 19980 | | 51 |
| 1 | VIEW | | 270 | 19980 | | 51 |
| 3 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | RECURSIVE EXECUTION | SYS_LE_3| | | | |
| 0 | INSERT STATEMENT | | 28723 | 504K| | 135 |
| 1 | LOAD AS SELECT | | | | | |
| 2 | SORT GROUP BY | | 28723 | 504K| 1592K| 135 |
| 3 | TABLE ACCESS FULL | T1 | 28723 | 504K| | 17 |
|* 4 | HASH JOIN | | 270 | 32670 | | 51 |
|* 5 | HASH JOIN | | 270 | 22680 | | 38 |
|* 6 | HASH JOIN | | 270 | 12690 | | 25 |
|* 7 | TABLE ACCESS FULL | T2 | 270 | 2700 | | 12 |
|* 8 | VIEW | | 28723 | 1037K| | 12 |
| 9 | TABLE ACCESS FULL | SYS_TEMP| 28723 | 504K| | 12 |
|* 10 | VIEW | | 28723 | 1037K| | 12 |
| 11 | TABLE ACCESS FULL | SYS_TEMP| 28723 | 504K| | 12 |
|* 12 | VIEW | | 28723 | 1037K| | 12 |
| 13 | TABLE ACCESS FULL | SYS_TEMP| 28723 | 504K| | 12 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("L_UPD3"."OWNER"="U"."USERNAME")
5 - access("L_UPD2"."OWNER"="U"."USERNAME")
6 - access("L_UPD"."OWNER"="U"."USERNAME")
7 - filter("U"."USERNAME" LIKE 'SYS%')
8 - filter("L_UPD"."OWNER" LIKE 'SYS%')
10 - filter("L_UPD2"."OWNER" LIKE 'SYS%')
12 - filter("L_UPD3"."OWNER" LIKE 'SYS%')
Note: cpu costing is off
36 rows selected.
<b>that actually shows "it was merged", the predicate was pushed -- it was merged</b>
and the execution profile of that query with this one:
ops$tkyte@ORA9IR2> with l_upd as (
2 select owner, max( t1.last_ddl_time) last_ddl
3 from t1
4 group by owner
5 )
6 select u.username, l_upd3.owner from
7 t2 u join l_upd
8 on ( l_upd.owner = u.username )
9 join l_upd l_upd2 on ( l_upd2.owner = u.username )
10 join l_upd l_upd3 on ( l_upd3.owner = u.username )
11 where username like 'SYS%'
12 /
<b>is identical, the amount of "work" is the same all around</b>
pushed predicate
Marc Werner, November 25, 2004 - 12:04 pm UTC
Tom,
which info from the dbms_xplan output proves it doesnt materialize the joined data ?
- the empty field under TempSpc ?
1.)what does "pushed predicate" mean ?
2.)would dbms_xplan also show an empty TempSpc, if Oracle would be able to materialize an inline SQL fully in memory ( this time un-mergeable , e.g.: for the statement inside the with clause ) ??
thanks for the helpful info .
Regards,
Marc.
November 25, 2004 - 12:35 pm UTC
it pushed the predicate down -- it merged the "where username like 'SYS%'" down, it really did "merge" that view totally. the two queries were equivalent.
The tkprofs I did (but did not post here) were sufficient for me.
If you can show me a case where the performance of the two techniques are measurably different -- I'd be glad to take a look at it.
ops$tkyte@ORA9IR2> exec dbms_stats.set_table_stats( user, 'T1', numrows => 1000000000, numblks => 10000000 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec dbms_stats.set_table_stats( user, 'T2', numrows => 1000000000, numblks => 10000000 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> delete from plan_table;
21 rows deleted.
ops$tkyte@ORA9IR2> explain plan for
2 select tmp.* from (
3 with l_upd as (
4 select owner, max( t1.last_ddl_time) last_ddl
5 from t1
6 group by owner
7 )
8 select u.username, l_upd3.owner from
9 t2 u join l_upd
10 on ( l_upd.owner = u.username )
11 join l_upd l_upd2 on ( l_upd2.owner = u.username )
12 join l_upd l_upd3 on ( l_upd3.owner = u.username )
13 ) tmp
14 where username like 'SYS%'
15 /
Explained.
ops$tkyte@ORA9IR2> select * from table( dbms_xplan.display );
PLAN_TABLE_OUTPUT
----------------------------------------------
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1638G| 110T| | 70M|
| 1 | VIEW | | 1638G| 110T| | 70M|
| 3 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | RECURSIVE EXECUTION | SYS_LE_3| | | | |
| 0 | INSERT STATEMENT | | 1000M| 42G| | 5849K|
| 1 | LOAD AS SELECT | | | | | |
| 2 | SORT GROUP BY | | 1000M| 42G| | 5849K|
| 3 | TABLE ACCESS FULL | T1 | 1000M| 42G| | 1517K|
| 4 | MERGE JOIN | | 1638G| 220T| | 70M|
| 5 | MERGE JOIN | | 51G| 5292G| | 58M|
| 6 | SORT JOIN | | 1600M| 110G| 239G| 47M|
|* 7 | HASH JOIN | | 1600M| 110G| 2336M| 7120K|
|* 8 | TABLE ACCESS FULL | T2 | 50M| 1764M| | 1517K|
|* 9 | VIEW | | 1000M| 34G| | 946K|
| 10 | TABLE ACCESS FULL | SYS_TEMP| 1000M| 42G| | 946K|
|* 11 | SORT JOIN | | 1000M| 34G| 82G| 11M|
|* 12 | VIEW | | 1000M| 34G| | 946K|
| 13 | TABLE ACCESS FULL | SYS_TEMP| 1000M| 42G| | 946K|
|* 14 | SORT JOIN | | 1000M| 34G| 82G| 11M|
|* 15 | VIEW | | 1000M| 34G| | 946K|
| 16 | TABLE ACCESS FULL | SYS_TEMP| 1000M| 42G| | 946K|
-------------------------------------------------------------------------------
even for terabytes of stuff...
view on top of the plan
Marc Werner, November 26, 2004 - 6:58 am UTC
but does the plan-output :
Rows TempSpc ...
---------------------------------------------
0 SELECT STATEMENT 270
1 VIEW 270
.. ...
show me if that VIEW is materialized or not or is TKPROF the only way to find out ?
the TempSpc field is empty (can I trust this information and rule --> no temp space used ??) but the additional level 1 VIEW tells me it had not merged the full statement !!
Regards,
Marc.
November 26, 2004 - 10:20 am UTC
the statement was merged, the filters are proof of that -- if it were not merged, the filters would have happend *after* the 'view' step.
If you can show us a case where by this extra "view" step in a plan is the cause of some performance related issue -- we'll be glad to take a peek at it.
Difference between oracle 8i and oracle 9.0
TONY, November 30, 2004 - 6:26 am UTC
Sir
Can you please send me the major differences between oracle 8i and oracle 9.0 my email address is netbosh@rediffmail.com
November 30, 2004 - 8:01 am UTC
Sudipta, December 14, 2004 - 1:27 am UTC
query subfactoring
karma, January 25, 2005 - 3:39 pm UTC
If i have view with 5 union all and every select needs to join to table x then will query subfactoring help instead of joining this table x 5 times ?
January 25, 2005 - 6:51 pm UTC
doubtful if it is just a straighforward join. subquery factoring is most useful when you have something like:
select *
from t1, (select a complex query here) X
where <join>
union all
select *
from t2, (select same complex query ) X
to factor out something complex. a straight join to a table would be dealt with "just inline" best.
Explain plan
reader, January 31, 2005 - 10:28 am UTC
what exactly is
1 2 RECURSIVE EXECUTION OF 'SYS_LE_2_0'
2 0 TEMP TABLE TRANSFORMATION
I have seen it, but not sure how to interpret it.
Thanks.
January 31, 2005 - 11:30 am UTC
it is part of query rewrite that involved Oracle setting up a temporary table used to hold a subresult.
see
</code>
http://www.dbazine.com/jlewis6.html <code>
for a nice example
Redo Generation with Subquery factoring
Vivek Sharma, February 18, 2005 - 5:06 am UTC
Hi Tom,
I was going through the queries and the use of WITH clause. When I checked the execution plan, it is generating a redo of around 520 bytes. Why is it so ?
Please see below :
SQL> create table t1 as select * from all_objects;
Table created.
Elapsed: 00:00:04.02
SQL> create table t2 as select * from dba_objects;
Table created.
Elapsed: 00:00:01.03
SQL> analyze table t1 compute statistics
2 /
Table analyzed.
Elapsed: 00:00:01.08
SQL> analyze table t2 compute statistics
2 ;
Table analyzed.
Elapsed: 00:00:01.04
1 with owners
2 as
3 ( select distinct owner username from t1 )
4 select count(*)
5 from t1, owners
6 where t1.owner = owners.username
7 union all
8 select count(*)
9 from t2, owners
10* where t2.owner = owners.username
SQL> /
COUNT(*)
----------
28609
28988
Elapsed: 00:00:01.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=88 Card=2 Bytes=44)
1 2 RECURSIVE EXECUTION OF 'SYS_LE_2_0'
2 0 TEMP TABLE TRANSFORMATION
3 2 UNION-ALL
4 3 SORT (AGGREGATE)
5 4 HASH JOIN (Cost=44 Card=28609 Bytes=629398)
6 5 VIEW (Cost=2 Card=16 Bytes=272)
7 6 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6602_1334A
3' (Cost=2 Card=16 Bytes=80)
8 5 TABLE ACCESS (FULL) OF 'T1' (Cost=41 Card=28609 By
tes=143045)
9 3 SORT (AGGREGATE)
10 9 HASH JOIN (Cost=44 Card=28988 Bytes=637736)
11 10 VIEW (Cost=2 Card=16 Bytes=272)
12 11 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6602_1334A
3' (Cost=2 Card=16 Bytes=80)
13 10 TABLE ACCESS (FULL) OF 'T2' (Cost=41 Card=28988 By
tes=144940)
Statistics
----------------------------------------------------------
90 recursive calls
9 db block gets
1214 consistent gets
502 physical reads
1260 redo size
418 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> ed
Wrote file afiedt.buf
1 select count(*)
2 from t1, ( select distinct owner username from t1 ) owners
3 where t1.owner = owners.username
4 union all
5 select count(*)
6 from t2, ( select distinct owner username from t1 ) owners
7* where t2.owner = owners.username
SQL> /
COUNT(*)
----------
28609
28988
Elapsed: 00:00:01.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=254 Card=2 Bytes=44)
1 0 UNION-ALL
2 1 SORT (AGGREGATE)
3 2 HASH JOIN (Cost=127 Card=28609 Bytes=629398)
4 3 VIEW (Cost=85 Card=16 Bytes=272)
5 4 SORT (UNIQUE) (Cost=85 Card=16 Bytes=80)
6 5 TABLE ACCESS (FULL) OF 'T1' (Cost=41 Card=28609
Bytes=143045)
7 3 TABLE ACCESS (FULL) OF 'T1' (Cost=41 Card=28609 Byte
s=143045)
8 1 SORT (AGGREGATE)
9 8 HASH JOIN (Cost=127 Card=28988 Bytes=637736)
10 9 VIEW (Cost=85 Card=16 Bytes=272)
11 10 SORT (UNIQUE) (Cost=85 Card=16 Bytes=80)
12 11 TABLE ACCESS (FULL) OF 'T1' (Cost=41 Card=28609
Bytes=143045)
13 9 TABLE ACCESS (FULL) OF 'T2' (Cost=41 Card=28988 Byte
s=144940)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1585 consistent gets
597 physical reads
0 redo size
418 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> with owners
2 as
3 ( select distinct owner username from t1 )
4 select count(*)
5 from t1, owners
6 where t1.owner = owners.username
7 union all
8 select count(*)
9 from t2, owners
10 where t2.owner = owners.username
11 /
COUNT(*)
----------
28609
28988
Elapsed: 00:00:01.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=88 Card=2 Bytes=44)
1 2 RECURSIVE EXECUTION OF 'SYS_LE_2_0'
2 0 TEMP TABLE TRANSFORMATION
3 2 UNION-ALL
4 3 SORT (AGGREGATE)
5 4 HASH JOIN (Cost=44 Card=28609 Bytes=629398)
6 5 VIEW (Cost=2 Card=16 Bytes=272)
7 6 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6603_1334A
3' (Cost=2 Card=16 Bytes=80)
8 5 TABLE ACCESS (FULL) OF 'T1' (Cost=41 Card=28609 By
tes=143045)
9 3 SORT (AGGREGATE)
10 9 HASH JOIN (Cost=44 Card=28988 Bytes=637736)
11 10 VIEW (Cost=2 Card=16 Bytes=272)
12 11 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6603_1334A
3' (Cost=2 Card=16 Bytes=80)
13 10 TABLE ACCESS (FULL) OF 'T2' (Cost=41 Card=28988 By
tes=144940)
Statistics
----------------------------------------------------------
4 recursive calls
7 db block gets
1198 consistent gets
405 physical reads
520 redo size
418 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
I executed WITH query several times and can see this redo generated.
Regards
Vivek Sharma
February 18, 2005 - 8:46 am UTC
it could be because it wrote to a temp table.
it could be block cleanouts.
it could be auditing.
it could be lots of stuff -- but hey, 520 bytes -- so?
WITH VS INLINE
VKOUL, February 28, 2005 - 2:41 pm UTC
I have changed the query to incorporate WITH clause
Statistics Of Plain Query
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
349 consistent gets
0 physical reads
0 redo size
1379 bytes sent via SQL*Net to client
6624 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
48 sorts (memory)
0 sorts (disk)
1 rows processed
Statistics Of Query (WITH Clause)
Statistics
----------------------------------------------------------
4 recursive calls
7 db block gets
134 consistent gets
1 physical reads
520 redo size
1379 bytes sent via SQL*Net to client
5305 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
28 sorts (memory)
0 sorts (disk)
1 rows processed
Q1 : Which one should be considered better ?
Thanks
February 28, 2005 - 2:43 pm UTC
looks like you referenced an inline view 2 times and cut the work in half -- #2 looks better from here.
VKOUL, March 01, 2005 - 6:09 pm UTC
The above query set was run on small set of data say 50 records and it took 42 to 63 milliseconds, but when I ran it on a set of data containing 100s of 1000s of records, the #2 query just took hours & hours, and finally I had to get the sesion killed.
Query #1
SELECT 'DWFPM51' "Report ID",
'O' "Report Type",
'Q' "Period Type",
2004 "Program Year",
2 "Quarter",
DECODE(msfw, 1, 'Y', 'N') msfw,
1,
'TEST',
COUNT(DISTINCT(CASE
WHEN NOT (rownumber = 1 AND office = 0 AND next_service_id IS NULL)
THEN seeker_id
END
)
) "Total Population",
COUNT(DISTINCT(CASE
WHEN service_id = 112 AND office = 1
THEN seeker_id
END
)
) "Referred To Jobs",
COUNT(DISTINCT(CASE
WHEN rownumber != 1 -- rownumber = 2 Commented Out.
AND service_id != 0 -- 0 Service IDs Are From seeker_data Table.
THEN seeker_id
END
)
) "Some Reportable Service",
COUNT(DISTINCT(CASE
WHEN rownumber != 1
AND service_id = 379
THEN seeker_id
END
)
) "Referred To Supportive Service",
COUNT(DISTINCT(CASE
WHEN rownumber != 1
AND service_id IN (96, 98, 100, 102, 104, 106, 114, 126, 158, 327, 328, 341, 344, 345, 347, 357, 372, 384, 385, 399, 409)
THEN seeker_id
END
)
) "Received Carrer Guidance",
COUNT(DISTINCT(CASE
WHEN rownumber != 1
AND service_id = 358
THEN seeker_id
END
)
) "Job Development Contact Made",
COUNT(DISTINCT(CASE
WHEN EXISTS (SELECT 'x'
FROM msfw_placement_iot t6
WHERE t3.seeker_id = t6.seeker_id
AND employment_start_date BETWEEN '01-jul-2004' AND '31-dec-2004'
AND office_id = 1
-- The IOT table has job_order_id NN. AND job_order_id IS NOT NULL
)
THEN seeker_id
END
)
) "MSFW Placed",
COUNT(DISTINCT(CASE
WHEN EXISTS (SELECT 'x'
FROM msfw_placement_iot t6
WHERE t3.seeker_id = t6.seeker_id
AND employment_start_date BETWEEN '01-jul-2004' AND '31-dec-2004'
-- The IOT table has job_order_id NN. AND job_order_id IS NOT NULL
AND onet_code NOT IN ('NULL', '45-2092.00', '45-2092.02', '45-2091.00')
AND office_id = 1
)
THEN seeker_id
END
)
) "Long Term Non-Agriculture Job"
FROM (SELECT t1.seeker_id, t1.initial_entry_date entry_service, t1.origin,
(SELECT 1
FROM (SELECT seeker_id, ROW_NUMBER() OVER (PARTITION BY seeker_id ORDER BY maint_history_id DESC) rownumber
FROM msfw_seeker_flag_iot
WHERE maintenance_date <= '31-dec-2004'
) msfw
WHERE msfw.seeker_id = t1.seeker_id
AND rownumber = 1
) msfw,
t1.service_id,
t1.office,
LEAD(service_id) OVER (PARTITION BY t1.seeker_id ORDER BY t1.initial_entry_date, service_id) next_service_id,
ROW_NUMBER() OVER (PARTITION BY t1.seeker_id ORDER BY t1.initial_entry_date, service_id) rownumber
FROM (SELECT seeker_id, initial_entry_date, 'SD' origin, 0 service_id,
CASE
WHEN office_id = 1
THEN 1 -- Same Office.
ELSE 0 -- Different Office.
END office
FROM seeker_data t1
WHERE initial_entry_date BETWEEN '01-jul-2004' AND NVL((SELECT MIN(service_date)
FROM (SELECT seeker_id, service_date
FROM (SELECT seeker_id, service_date, office_id,
LEAD(office_id) OVER (PARTITION BY seeker_id ORDER BY service_date) next_office
FROM (SELECT seeker_id, actual_start_date service_date, office_id
FROM services_and_activities
WHERE actual_start_date BETWEEN '01-jul-2004' AND '31-dec-2004'
UNION ALL
SELECT seeker_id, service_date , office_id
FROM seeker_services
WHERE service_date BETWEEN '01-jul-2004' AND '31-dec-2004'
)
)
WHERE office_id = 1
OR next_office = 1
) t2
WHERE t2.seeker_id = t1.seeker_id
), '31-dec-2004')
UNION ALL
SELECT seeker_id, service_date, origin, service_id,
CASE
WHEN office_id = 1
THEN 1 -- Same Office.
ELSE 0 -- Different Office.
END office
FROM (SELECT seeker_id, office_id, service_date, origin, service_id,
LEAD(office_id) OVER (PARTITION BY seeker_id ORDER BY service_date) next_office
FROM (SELECT seeker_id, actual_start_date service_date, 'SA' origin , service_id, office_id
FROM services_and_activities
WHERE actual_start_date BETWEEN '01-jul-2004' AND '31-dec-2004'
UNION ALL
SELECT seeker_id, service_date , 'SS' origin , seeker_service_type_id, office_id
FROM seeker_services
WHERE service_date BETWEEN '01-jul-2004' AND '31-dec-2004'
)
)
WHERE office_id = 1
OR next_office = 1
) t1
) t3
GROUP BY DECODE(msfw, 1, 'Y', 'N')
/
SQL> /
Elapsed: 00:00:29.05
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6758 Card=1452 Bytes
=84216)
1 0 INDEX (RANGE SCAN) OF 'PK_MSFW_PLACEMENT_IOT' (UNIQUE) (Co
st=2 Card=1 Bytes=35)
2 0 INDEX (RANGE SCAN) OF 'PK_MSFW_PLACEMENT_IOT' (UNIQUE) (Co
st=2 Card=1 Bytes=42)
3 0 SORT (GROUP BY) (Cost=6758 Card=1452 Bytes=84216)
4 3 VIEW (Cost=6752 Card=1452 Bytes=84216)
5 4 WINDOW (SORT) (Cost=6752 Card=1452 Bytes=55176)
6 5 VIEW (Cost=7178 Card=1452 Bytes=55176)
7 6 UNION-ALL
8 7 FILTER
9 8 TABLE ACCESS (FULL) OF 'SEEKER_DATA' (Cost=443
8 Card=36 Bytes=648)
10 8 SORT (AGGREGATE)
11 10 VIEW (Cost=6 Card=2 Bytes=96)
12 11 WINDOW (SORT) (Cost=6 Card=2 Bytes=70)
13 12 VIEW (Cost=4 Card=2 Bytes=70)
14 13 UNION-ALL
15 14 TABLE ACCESS (BY INDEX ROWID) OF 'SE
RVICES_AND_ACTIVITIES' (Cost=2 Card=1 Bytes=18)
16 15 INDEX (RANGE SCAN) OF 'SAA_IX1' (N
ON-UNIQUE) (Cost=1 Card=4)
17 14 TABLE ACCESS (BY INDEX ROWID) OF 'SE
EKER_SERVICES' (Cost=2 Card=1 Bytes=18)
18 17 INDEX (RANGE SCAN) OF 'SS_IX1' (NO
N-UNIQUE) (Cost=1 Card=6)
19 7 VIEW (Cost=2308 Card=1416 Bytes=86376)
20 19 WINDOW (SORT) (Cost=2308 Card=1416 Bytes=67968
)
21 20 VIEW (Cost=2300 Card=1416 Bytes=67968)
22 21 UNION-ALL
23 22 TABLE ACCESS (BY INDEX ROWID) OF 'SERVIC
ES_AND_ACTIVITIES' (Cost=3 Card=350 Bytes=7700)
24 23 INDEX (RANGE SCAN) OF 'DW_SAA_IX2' (NO
N-UNIQUE) (Cost=2 Card=352)
25 22 TABLE ACCESS (FULL) OF 'SEEKER_SERVICES'
(Cost=2297 Card=1066 Bytes=23452)
Statistics
----------------------------------------------------------
0 recursive calls
56 db block gets
1032971 consistent gets
50112 physical reads
0 redo size
807 bytes sent via SQL*Net to client
6295 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
167757 sorts (memory)
2 sorts (disk)
2 rows processed
SQL> disc
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
SQL>
Query #2
SELECT 'DWFPM15' "Report ID",
'O' "Report Type",
'Q' "Period Type",
2004 "Program Year",
2 "Quarter",
DECODE(msfw, 1, 'Y', 'N') msfw,
1,
'TEST',
COUNT(DISTINCT(CASE
WHEN NOT (rownumber = 1 AND office = 0 AND next_service_id IS NULL)
THEN seeker_id
END
)
) "Total Population",
COUNT(DISTINCT(CASE
WHEN service_id = 112 AND office = 1
THEN seeker_id
END
)
) "Referred To Jobs",
COUNT(DISTINCT(CASE
WHEN rownumber != 1 -- rownumber = 2 Commented Out.
AND service_id != 0 -- 0 Service IDs Are From seeker_data Table.
THEN seeker_id
END
)
) "Some Reportable Service",
COUNT(DISTINCT(CASE
WHEN rownumber != 1
AND service_id = 379
THEN seeker_id
END
)
) "Referred To Supportive Service",
COUNT(DISTINCT(CASE
WHEN rownumber != 1
AND service_id IN (96, 98, 100, 102, 104, 106, 114, 126, 158, 327, 328, 341, 344, 345, 347, 357, 372, 384, 385, 399, 409)
THEN seeker_id
END
)
) "Received Carrer Guidance",
COUNT(DISTINCT(CASE
WHEN rownumber != 1
AND service_id = 358
THEN seeker_id
END
)
) "Job Development Contact Made",
COUNT(DISTINCT(CASE
WHEN EXISTS (SELECT 'x'
FROM msfw_placement_iot t6
WHERE t3.seeker_id = t6.seeker_id
AND employment_start_date BETWEEN '01-jul-2004' AND '31-dec-2004'
AND office_id = 1
-- The IOT table has job_order_id NN. AND job_order_id IS NOT NULL
)
THEN seeker_id
END
)
) "MSFW Placed",
COUNT(DISTINCT(CASE
WHEN EXISTS (SELECT 'x'
FROM msfw_placement_iot t6
WHERE t3.seeker_id = t6.seeker_id
AND employment_start_date BETWEEN '01-jul-2004' AND '31-dec-2004'
-- The IOT table has job_order_id NN. AND job_order_id IS NOT NULL
AND onet_code NOT IN ('NULL', '45-2092.00', '45-2092.02', '45-2091.00')
AND office_id = 1
)
THEN seeker_id
END
)
) "Long Term Non-Agriculture Job"
FROM (SELECT t1.seeker_id, t1.initial_entry_date entry_service, t1.origin,
(SELECT 1
FROM (SELECT seeker_id, ROW_NUMBER() OVER (PARTITION BY seeker_id ORDER BY maint_history_id DESC) rownumber
FROM msfw_seeker_flag_iot
WHERE maintenance_date <= '31-dec-2004'
) msfw
WHERE msfw.seeker_id = t1.seeker_id
AND rownumber = 1
) msfw,
t1.service_id,
t1.office,
LEAD(service_id) OVER (PARTITION BY t1.seeker_id ORDER BY t1.initial_entry_date, service_id) next_service_id,
ROW_NUMBER() OVER (PARTITION BY t1.seeker_id ORDER BY t1.initial_entry_date, service_id) rownumber
FROM (WITH
services AS
(SELECT seeker_id, office_id, service_date, origin, service_id,
LEAD(office_id) OVER (PARTITION BY seeker_id ORDER BY service_date) next_office
FROM (SELECT seeker_id, actual_start_date service_date, 'SA' origin , service_id, office_id
FROM services_and_activities
WHERE actual_start_date BETWEEN '01-jul-2004' AND '31-dec-2004'
UNION ALL
SELECT seeker_id, service_date , 'SS' origin , seeker_service_type_id, office_id
FROM seeker_services
WHERE service_date BETWEEN '01-jul-2004' AND '31-dec-2004'
)
) -- Services Query Reference.
SELECT seeker_id, initial_entry_date, 'SD' origin, 0 service_id,
CASE
WHEN office_id = 1
THEN 1 -- Same Office.
ELSE 0 -- Different Office.
END office
FROM seeker_data t1
WHERE initial_entry_date BETWEEN '01-jul-2004' AND NVL((SELECT MIN(service_date)
FROM (SELECT seeker_id, service_date
FROM services
WHERE office_id = 1
OR next_office = 1
) t2
WHERE t2.seeker_id = t1.seeker_id
), '31-dec-2004')
UNION ALL
SELECT seeker_id, service_date, origin, service_id,
CASE
WHEN office_id = 1
THEN 1 -- Same Office.
ELSE 0 -- Different Office.
END office
FROM services
WHERE office_id = 1
OR next_office = 1
) t1
) t3
GROUP BY DECODE(msfw, 1, 'Y', 'N')
/
SQL> /
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4453 Card=1452 Bytes
=84216)
1 0 INDEX (RANGE SCAN) OF 'PK_MSFW_PLACEMENT_IOT' (UNIQUE) (Co
st=2 Card=1 Bytes=35)
2 0 INDEX (RANGE SCAN) OF 'PK_MSFW_PLACEMENT_IOT' (UNIQUE) (Co
st=2 Card=1 Bytes=42)
3 0 SORT (GROUP BY) (Cost=4453 Card=1452 Bytes=84216)
4 3 VIEW (Cost=4447 Card=1452 Bytes=84216)
5 4 WINDOW (SORT) (Cost=4447 Card=1452 Bytes=55176)
6 5 VIEW (Cost=4584 Card=1452 Bytes=55176)
7 8 RECURSIVE EXECUTION OF 'SYS_LE_8_0'
8 6 TEMP TABLE TRANSFORMATION
9 8 UNION-ALL
10 9 FILTER
11 10 TABLE ACCESS (FULL) OF 'SEEKER_DATA' (Cost=4
438 Card=36 Bytes=648)
12 10 SORT (AGGREGATE)
13 12 VIEW (Cost=2 Card=1416 Bytes=67968)
14 13 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D66
02_5919F12C' (Cost=2 Card=1416 Bytes=49560)
15 9 VIEW (Cost=2 Card=1416 Bytes=86376)
16 15 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6602_5
919F12C' (Cost=2 Card=1416 Bytes=49560)
SQL>
I'll post the statistics when they will be available as the query #2 is taking very long to execute.
Thanks
March 01, 2005 - 6:39 pm UTC
don't post anything, this is too big for "review"
please don't post a query here just to be "tuned"
Why ?
Rj, March 02, 2005 - 11:23 am UTC
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
1 with e
2 as (select * from emp),
3 d as (select * from dept where deptno = 400)
4 select * from e
5* ,d
SQL> /
no rows selected
Why this doesn't return any rows. I haven't joined "d" to "e". So, I would expect to see the rows from emp irrespective of what "d" has.
Please explain.
Thanks!
March 02, 2005 - 11:35 am UTC
you have joined d to e
from e, d
that is the join.
when you join N rows to 0 rows, you get zero rows.
you would have to OUTER join E to D
with e
as (select * from emp),
d as (select * from dept where deptno = 400)
select * from e left outer join d on (1=1)
/
Thanks
rj, March 02, 2005 - 12:16 pm UTC
Thanks Tom! Got the point!
VKOUL, March 03, 2005 - 11:59 am UTC
Sorry for posting big queries, you said the queries are to be tuned and I do not understand that part. The broad outline of query is
(Simple) Query #1
SELECT SUM(DECODE(col1, ...
FROM (SELECT ...
FROM (SELECT ...
FROM (SELECT ...
FROM
)
)
)
(WITH) Query #2
SELECT SUM(DECODE(col1, ...
FROM (SELECT ...
FROM (SELECT ...
FROM (WITH ...
...
)
)
)
***************************************************
Query # 1 (Simple)
Elapsed: 00:00:30.09
Statistics
----------------------------------------------------------
0 recursive calls
56 db block gets
1032971 consistent gets
50112 physical reads
0 redo size
794 bytes sent via SQL*Net to client
6295 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
167757 sorts (memory)
2 sorts (disk)
2 rows processed
Query # 2 (WITH clause)
Elapsed: 08:54:33.02
Statistics
----------------------------------------------------------
4 recursive calls
111 db block gets
128667459 consistent gets
127151785 physical reads
728 redo size
807 bytes sent via SQL*Net to client
4967 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
83879 sorts (memory)
2 sorts (disk)
2 rows processed
SQL> disc
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
SQL>
I don't know why this is taking almost 9 hours to run, when the normal query takes 30 seconds to run.
Look at consistent gets and physical reads, they have exploded.
The change (To use WITH clause) is done in innermost inline view only, rest all of the query is same, fetches the same result.
Thanks
Forcing the use of Temporary Tables using WITH
Drew, March 03, 2005 - 6:18 pm UTC
In the SQL Reference, it states (about subquery factoring) that: "Oracle optimizes the query by treating the query name as either an inline view or as a temporary table."
Is there any way to force/influence Oracle to considering the query as a temporary table?
I ask because running:
WITH x AS (SELECT ... FROM XXX ...)
y AS (SELECT ... FROM YYY ...)
SELECT ...
FROM ...
WHERE a IN (SELECT ... FROM x) AND
b IN (SELECT ... FROM y)
...
It takes 40 seconds, and 460,000 consistent gets.
However, when I replace the queries in the WITH clause with a couple of real tables (CTAS):
CREATE TABLE x AS (SELECT ... FROM XXX ...);
CREATE TABLE y AS (SELECT ... FROM YYY ...);
and run the same query (minus the WITH clause):
SELECT ...
FROM ...
WHERE a IN (SELECT ... FROM x) AND
b IN (SELECT ... FROM y)
...
It now only takes 0.07 seconds, and 400 consistent gets!!
I want the benefit of creating the temporary tables (which take just about 1 second using CTAS) using subquery factoring.
Can it be done?
March 03, 2005 - 6:21 pm UTC
adding rownum to the select list, or using the materialize view (not documented) should do it.
Query factoring
Anil, March 04, 2005 - 6:38 am UTC
Hi Tom
I had a query to get the minimum value from table cfr_leg staring from table cfr_leg_seg. cfr_leg_seg_map is an association table between cfr_leg_seg and cfr_leg(IOT).
I manged to write a query as follows with normal ways
Var x number;
Exec :x := 78348;
select min(FLL.CMRCL_CGO_WT_BU),min(FLL.CMRCL_CGO_vol_BU) avcap from CFR_LEG FLL
where fll.fll_id in (
select fls.fll_id from CFR_LEG_SEG_MAP FLS,cfr_leg_seg cfr
where cfr.cfr_id=fls.cfr_id and cfr.cfm_id =:X)
it generates a consistent read of 800 for processing about 700 rows from all the 3 tables..
Then I wrote the same query using with cluase that gives me the same result with 19 cr. I also tested the perforamcne and found with query is 10 times faster than a normal join
with
cfrid as (select cfr.cfr_id from cfr_leg_seg cfr where cfr.cfm_id =78348 and rownum < 2),
fllid as (select fls.fll_id from CFR_LEG_SEG_MAP FLS where fls.cfr_id in (select cfr_id from cfrid)),
flt_org_dst as (select flt_num , BRD_PNT,off_pnt from cfr_leg cfr where cfr.fll_id in (select * from fllid))
select min(FLL.CMRCL_CGO_WT_BU), min(FLL.CMRCL_CGO_vol_BU) from cfr_leg fll where (flt_num
,brd_pnt,off_pnt) in (select flt_num , BRD_PNT,off_pnt from flt_org_dst)
and SCH_DEP_DATE >='01-jan-2004' and
SCH_DEP_DATE <= '31-jul-2004';
It looks query factoring can be used for efficient access in many places. Is my observation correct. Also is there any drawback in using with clause that I need to take care.
Your advice would be valuable
Rgds
Anil
Query factoring
Anil, March 04, 2005 - 6:44 am UTC
Hi Tom
I had a query to get the minimum value from table cfr_leg staring from table cfr_leg_seg. cfr_leg_seg_map is an association table between cfr_leg_seg and cfr_leg(IOT).
I manged to write a query as follows with normal ways
Var x number;
Exec :x := 78348;
select min(FLL.CMRCL_CGO_WT_BU),min(FLL.CMRCL_CGO_vol_BU) avcap from CFR_LEG FLL
where fll.fll_id in (
select fls.fll_id from CFR_LEG_SEG_MAP FLS,cfr_leg_seg cfr
where cfr.cfr_id=fls.cfr_id and cfr.cfm_id =78348)
and day_of_opn in (1,5,7)
6 /
MIN(FLL.CMRCL_CGO_WT_BU) AVCAP
------------------------ ----------
34 4
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=238 Card=1 Bytes=38)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=238 Card=54 Bytes=2052)
3 2 NESTED LOOPS (Cost=111 Card=127 Bytes=2667)
4 3 INDEX (RANGE SCAN) OF 'CFR_CFM_I' (INDEX) (Cost=3 Card=106 Bytes=1060)
5 3 INDEX (RANGE SCAN) OF 'FLS_PK' (INDEX (UNIQUE)) (Cost=2 Card=1 Bytes=11)
6 2 TABLE ACCESS (BY INDEX ROWID) OF 'CFR_LEG' (TABLE) (Cost=1 Card=1 Bytes=17)
7 6 INDEX (UNIQUE SCAN) OF 'FLL_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)
Statistics
----------------------------------------------------------
169 recursive calls
0 db block gets
923 consistent gets
0 physical reads
0 redo size
467 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
it generates a consistent read of 800 for processing about 700 rows from all the 3 tables..
Then I wrote the same query using with cluase that gives me the same result with 19 cr. I also tested the perforamcne and found with query is 10 times faster than a normal join
with
cfrid as (select cfr.cfr_id from cfr_leg_seg cfr where cfr.cfm_id =78348 and rownum < 2),
fllid as (select fls.fll_id from CFR_LEG_SEG_MAP FLS where fls.cfr_id in (select cfr_id from cfrid)),
flt_org_dst as (select flt_num , BRD_PNT,off_pnt from cfr_leg cfr where cfr.fll_id in (select * from fllid))
select min(FLL.CMRCL_CGO_WT_BU), min(FLL.CMRCL_CGO_vol_BU) from cfr_leg fll where (flt_num
,brd_pnt,off_pnt) in (select flt_num , BRD_PNT,off_pnt from flt_org_dst)
and SCH_DEP_DATE >='01-jan-2004' and
SCH_DEP_DATE <= '31-jul-2004';
------------------------ -------------------------
34 4
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=15 Card=1 Bytes=75)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CFR_LEG' (TABLE) (Cost=9 Card=1 Bytes=32)
3 2 NESTED LOOPS (Cost=15 Card=1 Bytes=75)
4 3 NESTED LOOPS (Cost=6 Card=1 Bytes=43)
5 4 NESTED LOOPS (Cost=5 Card=1 Bytes=24)
6 5 VIEW (Cost=3 Card=1 Bytes=13)
7 6 COUNT (STOPKEY)
8 7 INDEX (RANGE SCAN) OF 'CFR_CFM_I' (INDEX) (Cost=3 Card=106 Bytes=1060)
9 5 INDEX (RANGE SCAN) OF 'FLS_PK' (INDEX (UNIQUE)) (Cost=2 Card=1 Bytes=11)
10 4 TABLE ACCESS (BY INDEX ROWID) OF 'CFR_LEG' (TABLE) (Cost=1 Card=1 Bytes=19)
11 10 INDEX (UNIQUE SCAN) OF 'FLL_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)
12 3 INDEX (RANGE SCAN) OF 'CFR_LEG_I2' (INDEX) (Cost=2 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
487 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
It looks query factoring can be used for efficient access in many places. Is my observation correct. Also is there any drawback in using with clause that I need to take care.
Your advice would be valuable
Rgds
Anil
March 04, 2005 - 8:24 am UTC
yes, this technique can be useful.
there are drawbacks to everything -- it is a tool. Just as you would not use "distinct" on every query or "union over union all". Use it when it makes sense to you to use it. Use it when you have for example a complex inline view/subquery that you reference in more than one location.
Materializing Subquery
Drew, March 04, 2005 - 10:18 am UTC
Thanks so much for the info about using rownum in the SELECT clause of the subquery. It worked like a charm. It will free us from having to manuallt build, populate, then drop temporary tables in a few places.
Does the use of rownum *guarantee* the materialization of the temporary table? If not, could you show me how I can specify the undocumented "materialize view" option?
Is one more likely to result in a materialization?
Drew, March 04, 2005 - 2:58 pm UTC
Thanks for your quick and helpful responses.
Considering the materialize hint vs. the use of rownum : is one more likely to result in materialization that the other?
When you say that the server would have to change something radical, do you mean that Oracle's behavior would have to be "coded" differently for it not to materialize when rownum or /*+ materialize */ is used?
March 04, 2005 - 6:12 pm UTC
Oracles current behaviour would have to change -- rownum, since it is assigned to a row as it flows out of a query block -- would cause the materialization, unless something really drastic happens (we cannot merge nor predicate push into a "view" with rownum -- as it changes the answer, it has to be "stable")
Sense the sys.dba_data_files changes
Mustafa, March 24, 2005 - 9:38 am UTC
Hi Tom,
I have a question. I am beginer for using Oracle. I want to write a program which checks the tablespace size. if the table space is becoming to be full then the program have to warn me. But i don t know how can i sense changing?
March 24, 2005 - 3:45 pm UTC
there are billions upon billions of tools that do this (eg: this is a wheel that has been invented many times over)
why not use a system management tool like enterprise manager or anything else you want to buy?
to sense "changing", you need to keep a history. you would need to program that yourself (or just use one of many tools out there)
Temp Table Transformation
Muhammad Riaz Shahid, May 11, 2005 - 3:28 am UTC
Dear Tom!
Can you please have a look on following query and tell me why Temp Table Transformation Not happening ? (and how we can make it to use temp table transoformation ?):
WITH get_pts
as (SELECT full_name, pt_cd FROM pts)
SELECT jh.*,<some other selects here>,
(SELECT full_name
FROM gt_pts
WHERE pt_cd = jh.bl) t1,
(SELECT full_name
FROM gt_pts
WHERE pt_cd = jh.b2) t2
(SELECT full_name
FROM gt_pts
WHERE pt_cd = jh.b3) t3,
(SELECT full_name
FROM gt_pts
WHERE pt_cd = jh.b4) t4,
(SELECT full_name
FROM gt_pts
WHERE pt_cd = jh.b5) t5,
(SELECT full_name
FROM gt_pts
WHERE pt_cd = jh.b6) t6,
....................................
..................................
from jh
where jh.col1=:1
and execution plan is (a part of execution plan actually, since original plan is too large to read):
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=519)
1 0 FILTER
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'BOOKING_AGENTS' (Cost=
1 Card=1 Bytes=25)
3 2 INDEX (UNIQUE SCAN) OF 'BOOKING_AGENTS_PK' (UNIQUE)
4 0 TABLE ACCESS (BY INDEX ROWID) OF 'CODES' (Cost=2 Card=1 Bytes=26)
5 4 INDEX (UNIQUE SCAN) OF 'CODES_PK' (UNIQUE) (Cost=1 Card=1)
6 0 SORT (GROUP BY NOSORT) (Cost=3 Card=1 Bytes=19)
7 6 TABLE ACCESS (BY INDEX ROWID OF 'CONSOLIDATION_STATUS'
(Cost=3 Card=1 Bytes=19)
8 7 INDEX (RANGE SCAN) OF 'CONSOLIDATION_STATUS_PK' (UNIQUE) (Cost=2 Card=1)
9 0 TABLE ACCESS (BY INDEX ROWID) OF 'PTS' (Cost=2 Card=1 Bytes=17)
10 9 INDEX (UNIQUE SCAN) OF 'PTS_PK' (UNIQUE) (Cost=1 Card=1)
11 0 TABLE ACCESS (BY INDEX ROWID) OF 'PTS'
(Cost=2 Card=1 Bytes=17)
12 11 INDEX (UNIQUE SCAN) OF 'PTS_PK' (UNIQUE) (Cost=1 Card=1)
13 0 TABLE ACCESS (BY INDEX ROWID) OF 'PTS' (Cost=2 Card=1 Byes=17)
14 13 INDEX (UNIQUE SCAN) OF 'PTS_PK' (UNIQUE) (Cost=1 Card=1)
15 0 TABLE ACCESS (BY INDEX ROWID) OF 'PTS' (Cost=2 Card=1 Bytes=17)
16 15 INDEX (UNIQUE SCAN) OF 'PTS_PK' (UNIQUE) (Cost=1 Card=1)
17 0 TABLE ACCESS (BY INDEX ROWID) OF 'PTS' (Cost=2 Card=1 Bytes=17)
18 17 INDEX (UNIQUE SCAN) OF 'PTS_PK' (UNIQUE) (Cost=1 Card=1)
19 0 TABLE ACCESS (BY INDEX ROWID) OF 'PTS' (Cost=2 Card=1 Bytes=17)
20 19 INDEX (UNIQUE SCAN) OF 'PTS_PK' (UNIQUE) (Cost=1 Card=1)
Thanks in Advance
May 11, 2005 - 7:28 am UTC
scalar subqueries are different and aren't you glad it did not. did you really want to full scan a temporary table instead of index probing on each row.
but this looks like a pivot to me, I wouldn't even consider a join I don't think
Agreed
Muhammad Riaz Shahid, May 11, 2005 - 8:17 am UTC
Ok i am agreed. i have one more question. consider the case (just a simple example):
SQL> ed
Wrote file afiedt.buf
1 create view test as
2 with aa as (Select sum(sal) sm from scott.emp)
3 select deptno,dname, (select * From aa) sm_Sal
4* from scott.dept
SQL> /
View created.
SQL> select * From test;
DEPTNO DNAME SM_SAL
---------- -------------- ----------
10 ACCOUNTING 18118
20 RESEARCH 18118
30 SALES 18118
40 OPERATIONS 18118
SQL> ed
Wrote file afiedt.buf
1* select distinct deptno From test
SQL> /
select distinct deptno From test
*
ERROR at line 1:
ORA-32035: unreferenced query name defined in WITH clause
Why so ?
May 11, 2005 - 8:55 am UTC
please contact support for that one, looks like that should be filed as a bug.
cause of unreferenced error
Andrew, June 24, 2005 - 1:31 pm UTC
Question 1:
Is this correct? :
The view:
create view test as
with aa as (Select sum(sal) sm from scott.emp)
select deptno,dname, (select * From aa) sm_Sal
from scott.dept
only needs to reference aa when sm_sal is selected
as in
select * From test;
The query
select distinct deptno From test;
would be transformed to:
with aa as (Select sum(sal) sm from scott.emp)
select distinct deptno
from scott.dept
which will generate to the ORA-32035 error
question 2:
This example seems to cause the temp table transformation
by referencing the 'with' definition twice in the query. Are there any rules to force the transformation to occur with hints, or some other usage pattern?
June 24, 2005 - 6:41 pm UTC
I didn't follow question 1? what was the question?
2) you cannot "force" it, no
cause of unreferenced error
Andrew, June 24, 2005 - 1:35 pm UTC
sorry I forgot my example:
SQL> WITH
2 all_quarters AS -- provides start and end dates for calander qtrs from approx 2002 to 2016
3 (SELECT DISTINCT TRUNC(SYSDATE - ii, 'Q') qtr_start_date,
4 TRUNC(TRUNC(sysdate - ii,'Q')+92,'Q')-1 qtr_end_date
5 FROM (SELECT ROWNUM - 4000 ii
6 FROM (SELECT LEVEL L
7 FROM DUAL CONNECT BY LEVEL < 4990
8 )
9 )
10 ),
11 emp_sched_chg_quarters AS
12 (SELECT DISTINCT a.employee_id employee_id,
13 b.qtr_end_date change_qtr
14 FROM ind_production_adjust_schedule a,
15 all_quarters b
16 WHERE a.sale_date_begin > b.qtr_start_date AND
17 a.sale_date_end < b.qtr_end_date AND
18 a.as_of_date = to_date('5/31/2004','mm/dd/yyyy')
19 )
20 SELECT c.employee_id, c.change_qtr
21 FROM emp_sched_chg_quarters c;
EMPLOY CHANGE_QTR
------ --------------------
123456 31-MAR-2004 00:00:00
234567 30-JUN-2004 00:00:00
345678 31-MAR-2004 00:00:00
Elapsed: 00:00:45.09
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=1 Bytes=11)
1 0 VIEW (Cost=13 Card=1 Bytes=11)
2 1 SORT (UNIQUE) (Cost=13 Card=1 Bytes=40)
3 2 NESTED LOOPS (Cost=3 Card=1 Bytes=40)
4 3 VIEW (Cost=1 Card=1 Bytes=13)
5 4 COUNT
6 5 VIEW (Cost=1 Card=1)
7 6 CONNECT BY (WITHOUT FILTERING)
8 7 TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=1
)
9 3 TABLE ACCESS (FULL) OF 'IND_PRODUCTION_ADJUST_SCHEDU
LE' (Cost=2 Card=1 Bytes=27)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
94794 consistent gets
0 physical reads
0 redo size
307 bytes sent via SQL*Net to client
344 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
3 rows processed
SQL> --where exists (select * from all_quarters);
SQL>
SQL>
SQL>
SQL>
SQL> WITH
2 all_quarters AS -- provides start and end dates for calander qtrs from approx 2002 to 2016
3 (SELECT DISTINCT TRUNC(SYSDATE - ii, 'Q') qtr_start_date,
4 TRUNC(TRUNC(sysdate - ii,'Q')+92,'Q')-1 qtr_end_date
5 FROM (SELECT ROWNUM - 4000 ii
6 FROM (SELECT LEVEL L
7 FROM DUAL CONNECT BY LEVEL < 4990
8 )
9 )
10 ),
11 emp_sched_chg_quarters AS
12 (SELECT DISTINCT a.employee_id employee_id,
13 b.qtr_end_date change_qtr
14 FROM ind_production_adjust_schedule a,
15 all_quarters b
16 WHERE a.sale_date_begin > b.qtr_start_date AND
17 a.sale_date_end < b.qtr_end_date AND
18 a.as_of_date = to_date('5/31/2004','mm/dd/yyyy')
19 )
20 SELECT c.employee_id, c.change_qtr
21 FROM emp_sched_chg_quarters c
22 WHERE EXISTS (SELECT 'x' FROM all_quarters WHERE ROWNUM < 2);
EMPLOY CHANGE_QTR
------ --------------------
123456 31-MAR-2004 00:00:00
234567 30-JUN-2004 00:00:00
345678 31-MAR-2004 00:00:00
Elapsed: 00:00:01.05
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=1 Bytes=11)
1 2 RECURSIVE EXECUTION OF 'SYS_LE_2_0'
2 0 TEMP TABLE TRANSFORMATION
3 2 FILTER
4 3 VIEW (Cost=13 Card=1 Bytes=11)
5 4 SORT (UNIQUE) (Cost=13 Card=1 Bytes=39)
6 5 NESTED LOOPS (Cost=3 Card=1 Bytes=39)
7 6 VIEW (Cost=1 Card=1 Bytes=12)
8 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D699C_894
6E06C' (Cost=1 Card=1 Bytes=13)
9 6 TABLE ACCESS (FULL) OF 'IND_PRODUCTION_ADJUST_SC
HEDULE' (Cost=2 Card=1 Bytes=27)
10 3 COUNT (STOPKEY)
11 10 VIEW (Cost=1 Card=1)
12 11 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D699C_8946E06
C' (Cost=1 Card=1 Bytes=13)
Statistics
----------------------------------------------------------
98 recursive calls
9 db block gets
1071 consistent gets
1 physical reads
1268 redo size
306 bytes sent via SQL*Net to client
344 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
cause of unreferenced error
Andrew, June 27, 2005 - 12:59 am UTC
Tom,
Sorry I was not clear, my first question was whether my reasoning was correct - that when this view is queried and the column defined by the 'with' is not selected. the final transformed query will still contain the 'with' definition - thus the 'unreferenced' error. is this correct?
Sorry my second question did not contain the example that - from testing - I believed forces the temp table transformation - whenever 2 or more references to the with definition are used.
Since then I've found that my '2 references rule' is not a rule at all. Sometimes it does, sometimes it doesn't. It would be nice if you could force execution one way or another or at least know the rules the optimizer uses.
Thanks
June 27, 2005 - 7:04 am UTC
if you "with" a subquery and do not use the "withed" subquery, that is an error, yes.
forces is too strong a word, it chose to materialize the withed query in that example, yes.
The optimizers jobs it to develop the best plan for a given query, it has many choices and things it can do.
In PL/SQL?
Dan, July 28, 2005 - 4:34 pm UTC
Can WITH be used anything like this:
open refcur
for
with t as (select * from user_objects)
select ...
or
with t as (select * from user_objects)
open refcur
for
select ...
Thanks
July 29, 2005 - 7:32 am UTC
the with is "part of SQL", it would go after the for
ops$tkyte@ORA9IR2> variable x refcursor
ops$tkyte@ORA9IR2> exec open :x for with data as (select * from dual) select * from data;
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> print x
D
-
X
ops$tkyte@ORA9IR2>
ROWNUM >0 in Factored Subquery
Paul Carpenter, September 05, 2005 - 12:10 pm UTC
I was having BIG problems trying to get a factored subquery to be materialized, added the MATERIALIZED hint to no avail, but tucked away in this post was the "ROWNUM > 0" may help advice, and low and behold it highlighted the fact that my main query was selecting the ROWID from one of the factored tables, which obviously prevents the optimizer from being able to use a temporary table and so simply used the factored subquery as shorthand and always re-executed. Adding the "ROWNUM > 0 clause caused a ORA-1446 - cannot select ROWID from a view" which led me to the path of enlightenment. Once I'd aliased the ROWID column in the subquery and used that instead in the main query everything worked a treat no need to use the hint
Keep up the good work.
materialize hint
Andrew, October 11, 2005 - 11:03 am UTC
i have not seen a query where this hint does not force the temp transformation
> with a as (select /*+ materialize */ 'x' from dual)
2 select * from a;
'
-
x
Elapsed: 00:00:01.00
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE
1 2
RECURSIVE EXECUTION OF 'SYS_LE_2_0'
2 0
TEMP TABLE TRANSFORMATION
3 2
VIEW
4 3
TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6601_D33E6ED5'
cpu cost for complex XML to relational query returns high figures
Flavio Casetta, November 03, 2005 - 11:54 am UTC
Hello Tom,
I have a query like this:
select /* EXTRACT_ORDERS */
extractvalue(value(g), '/InvOrder/Nr') as InvOrderNr,
extractvalue(value(g), '/InvOrder/Item') as Item,
extractvalue(value(g), '/InvOrder/Qty') as Qty,
extractvalue(value(g), '/InvOrder/Priority') as Priority,
extractvalue(value(g), '/InvOrder/Reordering') as Reordering,
extractvalue(value(g), '/InvOrder/HlOrder') as HLOrder,
extractvalue(value(g), '/InvOrder/DelDate') as DelDate,
f.cust,
f.mod,
f.tod,
f.seq,
f.pickwavenr,
f.pickwaveProdline,
f.pickwavetour,
f.pickwavetourseq,
f.prodrunnr,
f.prodrundate,
f.prodrunfinal
from (select value(e) as customernode,
extractvalue(value(e), '/Customer/Cust') as Cust,
extractvalue(value(e), '/Customer/Mod') as Mod,
extractvalue(value(e), '/Customer/Tod') as Tod,
extractvalue(value(e), '/Customer/Seq') as Seq,
d.pickwavenr,
d.pickwaveProdline,
d.pickwavetour,
d.pickwavetourseq,
d.prodrunnr,
d.prodrundate,
d.prodrunfinal
from (select value(c) as pickwavenode,
extractvalue(value(c), '/PickWave/Nr') as PickWaveNr,
extractvalue(value(c), '/PickWave/ProdLine') as PickWaveProdLine,
extractvalue(value(c), '/PickWave/Tour') as PickWaveTour,
extractvalue(value(c), '/PickWave/TourSeq') as PickWaveTourSeq,
a.prodrunnr,
a.prodrundate,
a.prodrunfinal
from (select value(b) as ProdRunNode,
extractvalue(value(b), '/ProdRun/Nr') as ProdRunNr,
to_date(extractvalue(value(b), '/ProdRun/Date'),'DDMMYYYY') as ProdRunDate,
extractvalue(value(b), '/ProdRun/Final') as ProdRunFinal
from xml_orders x,
table(xmlsequence(extract(x.xmlfile,'/PickData/ProdRun'))) b
where x.id = 15
) a,
table(xmlsequence(extract(a.prodrunnode,'/ProdRun/PickWave'))) c
) d,
table(xmlsequence(extract(d.pickwavenode,'/PickWave/Customer'))) e
) f,
table(xmlsequence(extract(f.customernode,'/Customer/InvOrder'))) g;
whose plan is calculated as follows:
Plan hash value: 3430051198
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 14T(100)| |
| 1 | NESTED LOOPS | | 4451T| 130P| 14T (1)|49498625:1|
| 2 | NESTED LOOPS | | 544G| 15T| 1818M (1)|6060:04:01|
| 3 | NESTED LOOPS | | 66M| 1845M| 222K (1)| 00:44:31 |
| 4 | NESTED LOOPS | | 8168 | 215K| 30 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | XML_ORDERS | 1 | 25 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | XML_ORDERS_PK | 1 | | 0 (0)| |
| 7 | COLLECTION ITERATOR PICKLER FETCH| | | | | |
| 8 | COLLECTION ITERATOR PICKLER FETCH | | | | | |
| 9 | COLLECTION ITERATOR PICKLER FETCH | | | | | |
| 10 | COLLECTION ITERATOR PICKLER FETCH | | | | | |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("X"."ID"=15)
37 rows selected.
As you can see the calculated cost is astronomical.
My understanding is that the optimizer in this case is guessing these numbers. If I look at the cost figures, it looks like an exponential increment:
8168 -> 8168^2 -> 8168^3 -> 8168^4
Where is the optimizer taking that 8168?
Is there any way to feed different values using dbms_stats.set_table_stats or some other method?
If I look at the structures created by Oracle, I see several object types defined as user defined types and varrays pairs.
It seems that Oracle is assuming that the number of elements in the collections is 8168 at the first level, then 8168^2 at the second level and so on.
Is my "theory" correct?
Thanks!
Flavio
November 04, 2005 - 2:44 am UTC
Excellent!
Flavio Casetta, November 04, 2005 - 4:21 am UTC
Tom,
thank you very much for that link.
Best regards,
Flavio
Difference in PIO inline view versus 'with' clause
Jack Douglas, December 19, 2005 - 1:12 pm UTC
Hi Tom,
The following appears to show a difference in IO behaviour between inline views and 'with' clauses - can you explain why the 'physical reads' is not zero for the second query?
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 19 17:56:38 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - 64bit Production
TRACKER@oracle10> create table t as
2 select rownum as id, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' as dummy
3 from dba_objects where rownum <= 10000;
Table created.
Elapsed: 00:00:00.14
TRACKER@oracle10>
TRACKER@oracle10> analyze table t compute statistics;
Table analyzed.
Elapsed: 00:00:00.15
TRACKER@oracle10>
TRACKER@oracle10> set autotrace on;
TRACKER@oracle10>
TRACKER@oracle10> select count(*) from (select * from (select * from t where rownum > 0) union all select * from (se
COUNT(*)
----------
20000
Elapsed: 00:00:00.10
Execution Plan
----------------------------------------------------------
Plan hash value: 3104055737
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | VIEW | | 20000 | 10 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | |
| 4 | VIEW | | 10000 | 5 (0)| 00:00:01 |
| 5 | COUNT | | | | |
|* 6 | FILTER | | | | |
| 7 | TABLE ACCESS FULL| T | 10000 | 5 (0)| 00:00:01 |
| 8 | VIEW | | 10000 | 5 (0)| 00:00:01 |
| 9 | COUNT | | | | |
|* 10 | FILTER | | | | |
| 11 | TABLE ACCESS FULL| T | 10000 | 5 (0)| 00:00:01 |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(ROWNUM>0)
10 - filter(ROWNUM>0)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
88 consistent gets
0 physical reads
0 redo size
335 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
TRACKER@oracle10> with tt as (select * from t where rownum > 0)
2 select count(*) from (select * from tt union all select * from tt);
COUNT(*)
----------
20000
Elapsed: 00:00:15.24
Execution Plan
----------------------------------------------------------
Plan hash value: 3758785460
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 15 (0)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | COUNT | | | | | |
|* 4 | FILTER | | | | | |
| 5 | TABLE ACCESS FULL | T | 10000 | 517K| 5 (0)| 00:00:01 |
| 6 | SORT AGGREGATE | | 1 | | | |
| 7 | VIEW | | 20000 | | 10 (0)| 00:00:01 |
| 8 | UNION-ALL | | | | | |
| 9 | VIEW | | 10000 | | 5 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6633_6D85FBA | 10000 | 517K| 5 (0)| 00:00:01 |
| 11 | VIEW | | 10000 | | 5 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6633_6D85FBA | 10000 | 517K| 5 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(ROWNUM>0)
Statistics
----------------------------------------------------------
100 recursive calls
51 db block gets
145 consistent gets
42 physical reads
1388 redo size
335 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
December 19, 2005 - 1:20 pm UTC
it materialized the with subquery and that spilled into temp. the physical IO was likely reads from temp.
one of the goals of the with subquery factoring is to materialize the result set once and use it repeatedly - which is what it did in this case.
Thanks
Jack Douglas, December 19, 2005 - 1:49 pm UTC
Thanks Tom, that makes sense.
one more thing
Jack Douglas, December 19, 2005 - 2:27 pm UTC
Tom,
When you said "spilled over" into temp, did you mean to imply that if the materialized 'with' was small enough then this wouldn't happen?
It seems that materialized 'with' clauses will always generate at least some PIO no matter how small:
TRACKER@oracle10> set autotrace trace statistics;
TRACKER@oracle10> drop table t;
Table dropped.
TRACKER@oracle10> create table t as
2 select rownum as id, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' as dummy
3 from dba_objects where rownum <= 100;
Table created.
TRACKER@oracle10> analyze table t compute statistics;
Table analyzed.
TRACKER@oracle10> with tt as (select * from t where rownum > 0)
2 select count(*) from (select * from tt union all select * from tt);
Statistics
----------------------------------------------------------
100 recursive calls
10 db block gets
22 consistent gets
1 physical reads
1432 redo size
335 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
December 19, 2005 - 3:36 pm UTC
tkprof it and see where the IO is happening (statistics_level = all before tracing)
Yes, if it can fit in ram, it might not need to do IO to and from temp.
tkprof
Jack Douglas, December 20, 2005 - 5:19 am UTC
Here is the relevent output from tkprof. This confirms that we are doing both a physical write to, and a physical read from, the materialized table, doesn't it?
My cache size is > 100Mb so that is not an issue in any of these examples.
I have noticed the same behaviour on 9ir2 as on 10gr2.
with tt as (select * from t where rownum > 0) select count(*) from (select *
from tt union all select * from tt)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.09 0 0 1 0
Execute 1 0.00 0.18 0 3 7 0
Fetch 2 0.00 0.00 1 8 1 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.28 1 11 9 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 122
Rows Row Source Operation
------- ---------------------------------------------------
1 TEMP TABLE TRANSFORMATION (cr=11 pr=1 pw=1 time=185361 us)
1 LOAD AS SELECT (cr=3 pr=0 pw=1 time=151741 us)
100 COUNT (cr=3 pr=0 pw=0 time=286 us)
100 FILTER (cr=3 pr=0 pw=0 time=178 us)
100 TABLE ACCESS FULL T (cr=3 pr=0 pw=0 time=70 us)
1 SORT AGGREGATE (cr=8 pr=1 pw=0 time=1286 us)
200 VIEW (cr=8 pr=1 pw=0 time=1050 us)
200 UNION-ALL (cr=8 pr=1 pw=0 time=848 us)
100 VIEW (cr=5 pr=1 pw=0 time=347 us)
100 TABLE ACCESS FULL SYS_TEMP_0FD9D6658_6D85FBA (cr=5 pr=1 pw=0 time=243 us)
100 VIEW (cr=3 pr=0 pw=0 time=122 us)
100 TABLE ACCESS FULL SYS_TEMP_0FD9D6658_6D85FBA (cr=3 pr=0 pw=0 time=19 us)
December 20, 2005 - 9:20 am UTC
yes, it shows the one physical IO was to the temporary table that was created for this.
is there another way?
Jack Douglas, December 20, 2005 - 10:02 am UTC
Hi Tom,
Do you know of a way of avoiding this PIO? You normally say tune for LIO and PIO will take care of itself, but here we are stuck with PIO whatever we do.
In some cases we will have to use /*+ inline */ and lose the benefit of the temp table because reading several times from the cache is faster than once from the disk.
This seems like it might be a serious limitation of 'with' clauses - do you think it would be worth making an enhancement request?
Jack
December 20, 2005 - 10:29 am UTC
don't materialize it? use inline views.
Encancement Request
Jack Douglas, December 28, 2005 - 5:21 am UTC
Hi Tom,
For the sake of anyone else who is interested, here is the bug number for the enhancement request I made: 4912547
Happy Christmas to you and thanks for giving us your time - I learn a lot from this site.
Jack
WITH clause is not working for nested table
Rajeswari, April 19, 2006 - 7:38 am UTC
scott@ORA9.2> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for HPUX: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
CREATE or replace TYPE NUMERIC_ID_TABLE_TYPE AS TABLE OF NUMBER(9)
/
declare
cursor c1(arg1 numeric_id_table_type) is
with test_view as (select column_value from table(cast(arg1 as numeric_id_table_type)) )
select column_value from test_view
union
select column_value from test_view;
var1 number(9);
begin
open c1(numeric_id_table_type(1, 2, 3, 4, 5));
fetch c1 into var1;
while c1%found loop
dbms_output.put_line('var1 = ' || var1);
fetch c1 into var1;
end loop;
close c1;
end;
/
declare
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at line 3
ORA-06512: at line 11
If I remove union or with clause it is working fine. Whether it is limitation of with clause?
April 19, 2006 - 9:04 am UTC
worked in 10gr2, but not 9ir2 - please contact support to work through this one.
Elapsed time
Raju, May 18, 2006 - 2:41 pm UTC
Hi Tom,
I rewrote a poorly performing query using the "WITH" clause and saw that the consistent gets and the physical reads are reduced by 1/3rd. But now I find that the elapsed time has increased by about 20% i,e ran in SQL*Plus with timing switched ON. Now, I really don't know if I should revert to the old query or use the new query as it has better consistent/physical read values. The query will be used in the overnight batch process. Please can you advice?
Thanks
May 19, 2006 - 10:16 am UTC
use tkprof, set timing on in sqlplus isn't particularly enlightening. tkprof will show the work (cpu/ela/ios) and compare those two.
WITH clause significantly slower than inline view when using CONNECT BY
Mohit Agarwal, May 25, 2006 - 3:17 pm UTC
I have a query that needs to return a set of loans related by a parent-child relationship given any loan in that family of loans.
SELECT loanid
FROM (
SELECT loanid
FROM loan
CONNECT BY loanid = PRIOR parentloanid
START WITH loanid = <some loanid>
ORDER BY level DESC)
WHERE rownum = 1;
This gives me the original parent loan. Cost of this query is 3. LoanID is PK and an index exists on Parentloanid.
I then use
SELECT loanid
FROM loan
CONNECT BY parentloanid = PRIOR loanid
START WITH loanid = <the loanid from previous query>.
Cost is 4.
If I combine them
WITH l AS (SELECT loanid
FROM (
SELECT loanid
FROM loan
CONNECT BY loanid = PRIOR parentloanid
START WITH loanid = <some loanid>
ORDER BY level DESC)
WHERE rownum = 1)
SELECT loanid
FROM loan
CONNECT BY parentloanid = PRIOR loanid
START WITH loanid = (SELECT loanid FROM L)
The cost blows up to 13126. It starts doing FTS in both queries when using the CONNECT BY (CONNECT BY PUMP section) where earlier it used an INDEX SCAN.
If I used an inline view the cost is 8
SELECT loanid
FROM loan
CONNECT BY parentloanid = PRIOR loanid
START WITH loanid =
(SELECT loanid
FROM (
SELECT loanid
FROM loan
CONNECT BY loanid = PRIOR parentloanid
START WITH loanid = <some loanid>
ORDER BY level DESC)
WHERE rownum = 1)
Is there perhaps an issue of using a WITH clause and a CONNECT BY clause in the same query?
WITH clause significantly slower than inline view when using CONNECT BY
Mohit Agarwal, May 25, 2006 - 3:42 pm UTC
I made a mistake in the cost I posted. The cost for the WITH clause query is 9173 and its doing the FTS only for the query in the WITH clause.
By itself the plan for the query in the WITH clause is
SELECT STATEMENT Cost = 3
VIEW
COUNT STOPKEY
VIEW
SORT ORDER BY STOPKEY
CONNECT BY WITH FILTERING
TABLE ACCESS BY INDEX ROWID PRODORA.LOAN
INDEX UNIQUE SCAN PRODORA.PK_LOAN
NESTED LOOPS
BUFFER SORT
CONNECT BY PUMP
TABLE ACCESS BY INDEX ROWID PRODORA.LOAN
INDEX UNIQUE SCAN PRODORA.PK_LOAN
TABLE ACCESS FULL PRODORA.LOAN
Using the WITH clause it changed to
SELECT STATEMENT Cost = 9173
TEMP TABLE TRANSFORMATION
LOAD AS SELECT
VIEW
COUNT STOPKEY
VIEW
SORT ORDER BY STOPKEY
CONNECT BY WITH FILTERING
TABLE ACCESS BY INDEX ROWID PRODORA.LOAN
INDEX UNIQUE SCAN PRODORA.PK_LOAN
HASH JOIN
CONNECT BY PUMP
TABLE ACCESS FULL PRODORA.LOAN
TABLE ACCESS FULL PRODORA.LOAN
CONNECT BY WITH FILTERING
TABLE ACCESS BY INDEX ROWID PRODORA.LOAN
INDEX UNIQUE SCAN PRODORA.PK_LOAN
VIEW
TABLE ACCESS FULL SYS.SYS_TEMP_0FD9D68B2_DD869F54
NESTED LOOPS
BUFFER SORT
CONNECT BY PUMP
TABLE ACCESS BY INDEX ROWID PRODORA.LOAN
INDEX RANGE SCAN PRODORA.LOAN_NDX10
TABLE ACCESS FULL PRODORA.LOAN
VIEW
TABLE ACCESS FULL SYS.SYS_TEMP_0FD9D68B2_DD869F54
As you can see in the top half of the plan, it stopped using the PK in the CONNECT BY looping. The Loan table has about 3M records in it.
Thanks
With Clause and ora-3001
A reader, December 18, 2007 - 5:10 am UTC
Hi Tom,
I've got a view created under a user that uses a With clause. When I try to read from the view as the owner it works fine. However, when I try a select from the view as another user, I'm hitting Ora-03001:- Unimplemented feature. Have you seen this before? I've tried the With clause with/without the Materialize hint. But it just doesn't seem to like the With clause at all. Oh, we are on 9ir2.
Thanks
December 18, 2007 - 1:54 pm UTC
need help reproducing
ops$tkyte%ORA9IR2> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
ops$tkyte%ORA9IR2> create user a identified by a;
User created.
ops$tkyte%ORA9IR2> grant create session to a;
Grant succeeded.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace view v
2 as
3 with data
4 as
5 (select sysdate+level dt from dual connect by level <= 5)
6 select * from data
7 /
View created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select * from v;
DT
---------
19-DEC-07
20-DEC-07
21-DEC-07
22-DEC-07
23-DEC-07
ops$tkyte%ORA9IR2> grant select on v to a;
Grant succeeded.
ops$tkyte%ORA9IR2> @connect a/a
ops$tkyte%ORA9IR2> set termout off
a%ORA9IR2>
a%ORA9IR2> set termout on
a%ORA9IR2> select * from ops$tkyte.v;
DT
---------
19-DEC-07
20-DEC-07
21-DEC-07
22-DEC-07
23-DEC-07
With clause and ora-3001 - Update
A reader, December 18, 2007 - 5:46 am UTC
Hi Tom,
I managed to sort this one out thanks. I had a packaged function call in my subquery, which it didn't like. I think it is a restriction of some sort in 9ir2. Apparently you can't use the With Clause and a function call together.
Regards,
Chandini
December 18, 2007 - 1:56 pm UTC
still need help...
ops$tkyte%ORA9IR2> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace package foo
2 as
3 function bar return number;
4 end;
5 /
Package created.
ops$tkyte%ORA9IR2> create or replace package body foo
2 as
3 function bar return number as begin return 1; end;
4 end;
5 /
Package body created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create user a identified by a;
User created.
ops$tkyte%ORA9IR2> grant create session to a;
Grant succeeded.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace view v
2 as
3 with data
4 as
5 (select sysdate+level+foo.bar dt from dual connect by level <= 5)
6 select foo.bar, dt from data
7 /
View created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select * from v;
BAR DT
---------- ---------
1 20-DEC-07
1 21-DEC-07
1 22-DEC-07
1 23-DEC-07
1 24-DEC-07
ops$tkyte%ORA9IR2> grant select on v to a;
Grant succeeded.
ops$tkyte%ORA9IR2> @connect a/a
ops$tkyte%ORA9IR2> set termout off
a%ORA9IR2>
a%ORA9IR2> set termout on
a%ORA9IR2> select * from ops$tkyte.v;
BAR DT
---------- ---------
1 20-DEC-07
1 21-DEC-07
1 22-DEC-07
1 23-DEC-07
1 24-DEC-07
Yes but....
A reader, December 19, 2007 - 4:46 am UTC
Hi tom,
Thanks for the example.
If you change your view definition to access the 'data' subquery more than once, you hit the problem.
Example:-
As user A, I created the following objects
SQL> create or replace package foo
2 as
3 function bar return number;
4 end;
5 /
Package created.
SQL> create or replace package body foo
2 as
3 function bar return number
4 as
5 begin
6 return 1;
7 end bar;
8 end;
9 /
Package body created.
SQL> create or replace view test
2 as
3 with tt
4 as (select sysdate, foo.bar
5 from dual
6 where 1 = foo.bar)
7 select * from tt;
View created.
Now as User B, when I run
SQL> select * from cpaterson.test;
SYSDATE BAR
--------- ----------
19-DEC-07 1
It works.
However, change the definition slightly as User A to :-
SQL> create or replace view test
2 as
3 with tt
4 as (select sysdate, foo.bar
5 from dual
6 where 1 = foo.bar)
7 select * from tt
8 union all
9 select * from tt; --I'm reading from tt more than once.
View created.
And now, when I try to read from this view as User B, I hit the error -
SQL> l
1* select * from cpaterson.test
SQL> /
select * from cpaterson.test
*
ERROR at line 1:
ORA-03001: unimplemented feature
I think it doesn't like it when you call the subquery in the With clause more than once and you've used function calls in the SQL too.
To test this, I changed the view defn again to
create or replace view test
as
with tt
as (select sysdate, 1
from dual
where 1 = 1)
select * from tt
union all
select * from tt;
And now from User B, it works pretty fine.
SQL> l
1* select * from cpaterson.test
SQL> /
SYSDATE 1
--------- ----------
19-DEC-07 1
19-DEC-07 1
If you can explain why this is happening, that would be great.
Thanks,
Chandini
December 19, 2007 - 10:40 am UTC
Ok, it is a 9i issue - fixed in 10g
In 9i, a workaround can be alter session set current_schema - it is a name resolution issue of some sort apparently. see example below.
In any event, will not reproduce in 10g, and if you wish, you may contact support and reference bug #4483232 in 9i.
ops$tkyte%ORA9IR2> create or replace view v1
2 as
3 with tt
4 as (select sysdate, foo.bar
5 from dual
6 where 1 = foo.bar)
7 select * from tt;
View created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace view v2
2 as
3 with tt
4 as (select sysdate, foo.bar
5 from dual
6 where 1 = foo.bar)
7 select * from tt
8 union all
9 select * from tt;
View created.
ops$tkyte%ORA9IR2> grant select on v1 to a;
Grant succeeded.
ops$tkyte%ORA9IR2> grant select on v2 to a;
Grant succeeded.
ops$tkyte%ORA9IR2> @connect a/a
ops$tkyte%ORA9IR2> set termout off
a%ORA9IR2>
a%ORA9IR2> set termout on
a%ORA9IR2> select * from ops$tkyte.v1;
SYSDATE BAR
--------- ----------
19-DEC-07 1
a%ORA9IR2> select * from ops$tkyte.v2;
select * from ops$tkyte.v2
*
ERROR at line 1:
ORA-03001: unimplemented feature
a%ORA9IR2> alter session set current_schema=ops$tkyte;
Session altered.
a%ORA9IR2> select * from v1;
SYSDATE BAR
--------- ----------
19-DEC-07 1
a%ORA9IR2> select * from v2;
SYSDATE BAR
--------- ----------
19-DEC-07 1
19-DEC-07 1
Thanks
A reader, December 19, 2007 - 11:12 am UTC
Thanks for your quick reply and explanation. It is always helpful when you know why some things don't work.
I initially thought it was maybe a problem with the With clause creating a MV under the covers and user B missing certain privileges.
Thanks
Chandini
Create table with IN list
bhasker, March 06, 2008 - 4:01 pm UTC
I have a query with CTAS, which has poor perf. I tried to rewrite using WITH, but it seems cost has gone UP. How can we rewrite below query?
CREATE TABLE tmp_MP_display_xflag_M
--PARALLEL 4 TABLESPACE FT_WORKAREA NOLOGGING
AS SELECT /*+ ORDERED USE_HASH(x,d) INDEX_FFS(x) PARALLEL(d,4) */
d.ROWID dis_rowid
FROM MP_global_exclude PARTITION (M20080201) x,
display PARTITION (M20080201) d
WHERE x.dsp_hash IN
(d.dsp_hash,
TRUNC (d.dsp_hash, -14),
TRUNC (d.dsp_hash, -7),
TRUNC (d.dsp_hash, -14) + MOD (d.dsp_hash, POWER (10, 7))
)
AND LOWER (d.url_name) LIKE x.rule || '%'
AND BITAND (d.xflag, 1) = 0
AND BITAND (d.xflag, 1) = 0
Thanks,
Bhasker
March 07, 2008 - 5:35 pm UTC
first, lose the hints, stop hinting. let the database do what databases do.
and then second, wow, that is the most bizarre join I've seen. You really want that bitand to happen... lower(d.c) like x.c || '%'... x.c in (d.c, f(d.c), g(d.c), h(d.c) )...
ouch - so, basically, like a big old cartesian join (your ordered hint funny enough almost looks like it would be backwards...
with data
as
(select rowid rid,
decode(r, 1,dsp_hash,
2, TRUNC (d.dsp_hash, -14),
3, TRUNC (d.dsp_hash, -7),
4, TRUNC (d.dsp_hash, -14) + MOD (d.dsp_hash, POWER (10, 7)) ) dsp_hash,
lower(url_name)
from display PARTITION (M20080201) d,
(select level r from dual connect by level <= 4)
where bitand(xflag,1) = 0
)
select distinct data.rid
from data, MP_global_exclude PARTITION (M20080201) x
where data.url_name like x.rule||'%'
and data.dsp_hash = x.dsp_hash
/now we can actually hash join on something. And if you have an index on X(rule,dsp_hash) it'll fast full scan it all by itself.
great... great...
Bhasker, March 10, 2008 - 9:14 pm UTC
Thanks a lot Tom!..
This is my first posting.. Always, i use to READ the questions and get answers for my queries.
The query is working fine and improved performance from 1 hr 23 mins to 1 hr 10 mins.. but, we're going to run same query for 10 times or so.. so we will save 100 mins..
Just wondering, if there's anything we can do to get better performance?. May be some other technique, rather than WITH Clause???
Because, i got DISPLAY table for 25 milliion records and EXCLUDE table for 250K records. Thats the reason, we gave ORDERED HINT to use small table first for JOIN.
Thanks a lot again..
Bhasker
March 10, 2008 - 9:26 pm UTC
oracle does not need the ordered hint, we actually KNOW what table is smaller if statistics are present. Your join was NO JOIN - it was horrible - please look at your original stuff and just try to understand what sort of processing would have to take place (eg: UNDERSTANDING is key to tuning, look at what you were asking the computer to do and ask yourself "could you do this efficiently", if you as thinking human being cannot - take pity on the computer. I'll update this in 10 years when the computers start to win, hands down)
one would ask the obvious question:
why do you need to run it 10 times or so
why cannot you run it ONCE
WITH Clause ... thanks..
Bhasker, March 10, 2008 - 11:40 pm UTC
Actually, we have 10 reports which work on 10 different schemas. So, same query will be running for 10 schemas.That's why i said, i need to run 10 times.
Basically, performance issue was due to table DISPLAY getting FULL TABLE SCANE four times due to IN clause.
To reduce this 4 times full table scan, i thought it was better to use WITH Clause ?. Also, i guess it would help to create an index on dsp_hash. But, ALL records in DISPLAY table partition have to be processed, so developers did not create index on dsp_hash...
WHERE x.dsp_hash IN
(d.dsp_hash,
TRUNC (d.dsp_hash, -14),
TRUNC (d.dsp_hash, -7),
TRUNC (d.dsp_hash, -14) + MOD (d.dsp_hash, POWER (10, 7))
)
but the WITH clause query you gave has taught me few more things in Oracle.. Thanks a lot! As everyone writes.. you're really great!!!
Bhasker
WITH Clause
Maverick, March 28, 2008 - 3:16 pm UTC
Tom,
From EMP Table, I was looking at the following info. basically some counts
count(empno less than 25),
count(empno between 25 and 50),
count(deptno for empno between 30 and 40)
in one query.
The tables I'm trying to do this has a couple of million records and evergrowing, so I was wondering using WITH can I accomplish this.
using WITH clause can we write a query something like this?
With data as(select * from EMP)
select count(empno) from data
where empno between 1 and 25,
select count(empno) from data
where empno between 26 and 99,
select count(empno) from data
where empno between 100 and 199;
????
Thanks,
March 31, 2008 - 7:48 am UTC
select count( case when empno < 25 then 1 end ) cnt1,
count( case when empno between 25 and 50 then 1 end ) cnt2,
count( case when empno between 26 and 99 then 1 end ) .......
Just do it in a SINGLE pass, don't read and re-read the table over and over.
And that WITH clause above - if it decided to materialize the data, you would only accomplish adding an ADDITIONAL FULL SCAN!!! and a WRITE TO TEMP of the data.
Maverick, March 31, 2008 - 8:13 am UTC
Tom, thanks for your response. couple of quick questions [just to make sure before I implement this stuff]
Doesn't "Count(*)" result in a full table scan? I want to avoid full table scans as it has millions of rows.
so, can I do
select count(case when empno<25 then 1 end ) cnt1,
count(case when empno between 26 and 50 then 1 end ) cnt2
from (select empno from emp when empno <=500)
??
[I was thinking that's what you meant, above]
Thanks for your inputs.
March 31, 2008 - 9:44 am UTC
YOU WANT A FULL SCAN - either of an index on empno (presuming empno is not null or is in an index with at least one not null attribute) OR of the TABLE.
count(*) can use an index fast full scan.
Yes, you can:
select count(case when empno<25 then 1 end ) cnt1,
count(case when empno between 26 and 50 then 1 end ) cnt2
from emp
where empno <500;
of course - hopefully you understand what this SQL statement is doing....
All I can ever do is take your inputs to me, which were simply:
...
With data as(select * from EMP)
select count(empno) from data
where empno between 1 and 25,
select count(empno) from data
where empno between 26 and 99,
select count(empno) from data
where empno between 100 and 199;
.....
(nothing about a predicate, nothing about just a small bit of data, NOTHING)
and answer based on that, if you can add a predicate - go for it.
But to get these counts you definitely do NOT NEED more than a single scan.
Follow up
Maverick, April 22, 2008 - 9:22 am UTC
Tom, I am just following up for the above question:
I am going to implement a single pass SQL and not use WITH as you mentioned [makes sense]. But is it possible to write a query like that at all? I mean syntactically is it correct?
I keep getting
ORA-00933: SQL command not properly ended
Error.
So, is it not supported?
Thanks,
April 23, 2008 - 5:53 pm UTC
so, no example??!?!?!?!?!?
query I gave you works just dandy...
ps$tkyte%ORA10GR2> connect scott/tiger
Connected.
select count(case when empno<25 then 1 end ) cnt1,
count(case when empno between 26 and 50 then 1 end ) cnt2
from emp
4 where empno <500;
CNT1 CNT2
---------- ----------
0 0
Example????
Maverick, April 24, 2008 - 9:22 am UTC
Tom, May be you missed the part I wrote "Following above questions by me" in which I had that example of WITH inline clause:
anyway I am giving it again:
With data as(select * from EMP)
select count(empno) from data
where empno between 1 and 25,
select count(empno) from data
where empno between 26 and 99,
select count(empno) from data
where empno between 100 and 199;
And I know your query works just dandy [and that's what I need and am using it]
My questions is, can we write a query like this [Syntactically] is it possible?
If possible, why I keep getting above mentioned oracle message?
Thanks,
April 28, 2008 - 12:05 pm UTC
i missed nothing.
you wrote:
...
I keep getting
ORA-00933: SQL command not properly ended
Error.
So, is it not supported?
Thanks,
....
I have no idea what query that error applied to. I had to assume you meant MY query that YOU modified, or your query - but in any case, I have no idea what query that error applies to because there is....
NO EXAMPLE.
I guess now, reading this followup, you meant YOUR query (your query that I ignored since the answer was "do not do that")
ops$tkyte%ORA9IR2> with data as (select * from scott.emp)
2 select '7000/7500', count(*) from data where empno between 7000 and 7500
3 union all
4 select '7500/8000', count(*) from data where empno between 7501 and 8000
5 /
'7000/750 COUNT(*)
--------- ----------
7000/7500 2
7500/8000 12
RE: example
Duke Ganote, April 24, 2008 - 9:32 am UTC
Maverick-- Are you looking for values in rows rather than columns? Something more like this?
WITH sample_data as ( select * from scott.emp )
select count(*), 1 as mn, 25 as mx
from sample_data where empno between 1 and 25 union all
select count(*), 26, 99
from sample_data where empno between 26 and 99 union all
select count(*), 99, null
from sample_data where empno > 99
/
COUNT(*) MN MX
--------------- ---------- ----------
0 1 25
0 26 99
14 99
What makes Oracle decide one way or the other?
oj, September 30, 2008 - 7:15 pm UTC
Hi Tom,
I have a query that I wrote for a complex report. It is a mix of hierarchial query with some analytic functions and lots of aggregates....oh and a whole lot of case stuctures..
Once the data is available,we compare two identical result sets and do some more of the things above...
When I try to use "subquery factoring", oracle seems to be doing it per the plan, but returns the results slower...
My query goes into 300 lines so am unable to post here. But will post the before and after plan in a separate post..
would this be sufficient information?
October 01, 2008 - 11:51 am UTC
sufficient for what?
you have two queries.
They are logically equivalent (return same data).
one is faster than the other.
this happens all of the time - nothing is every "always better" than something else. scalar subqueries work best for
o first rows optimization type queries - need the first rows as soon as possible
o small result sets
they are not suited for really large queries as they tend to force the optimizer into a "do it yourself nested loop"
Consider;
select t.*, (select count(*) from t2 where t2.something = t1.something) cnt
from t;
Conceptually that is evaluated similar to this:
for x in (select * from t)
loop
select count(*) into :cnt from t2 where t2.something = :X.SOMETHING;
output row
end loop
Now, if T is large and t2.something is indexed and t2.something is fairly selective (returns a small number of rows, cnt is a small number) then a scalar subquery MIGHT be useful to get the first rows really fast - just full scan a bit of T, run the scalar subquery a couple of times and there you go.
However, if T is large and you are selecting ALL ROWS - the goal is to get the last row as fast as possible - I'd almost certainly want to not consider a scalar subquery. I'd rather:
select *
from T, (select count(*) from t2 group by something) x,
where t.something = x.something(+) -- (+) only if NECESSARY of course
nice big full scans, nice efficient bulk hash join - that'd be what I wanted to see there.
What makes Oracle decide one way or the other?
oj, September 30, 2008 - 7:17 pm UTC
WITH temp_table_aggr AS
(
SELECT /* materialize */
team_buckets.agent_name,
team_buckets.bckt_name,
team_buckets.cbp,
team_buckets.lvl,
SUM(NVL(activity.loan_nums,0)) ln_cts_tot,
SUM(NVL(activity.negs,0)) negs,
SUM(NVL(activity.pos,0)) pos,
SUM(NVL(activity.nos,0)) nos,
SUM(NVL(activity.bom,0)) bom,
SUM(NVL(activity.eom,0)) eom,
SUM(NVL(activity.neg_bal,0)) neg_bal,
SUM(NVL(activity.pos_bal,0)) pos_bal,
SUM(NVL(activity.nos_bal,0)) nos_bal
FROM
(
SELECT
COUNT(VW_DFLT_LOAN_BCKT_FCT.DFLT_LOAN_KEY) loan_nums,
TEAM_KEY,
ACTVTY_TIME_KEY,
BCKT_KEY,
CASE
WHEN loan_actvty < 0 THEN
COUNT(VW_DFLT_LOAN_BCKT_FCT.DFLT_LOAN_KEY)
ELSE
0
END negs,
CASE
WHEN loan_actvty > 0 THEN
COUNT(VW_DFLT_LOAN_BCKT_FCT.DFLT_LOAN_KEY)
ELSE
0
END pos,
CASE
WHEN loan_actvty = 0 THEN
COUNT(VW_DFLT_LOAN_BCKT_FCT.DFLT_LOAN_KEY)
ELSE
0
END nos,
CASE
WHEN VW_DFLT_LOAN_BCKT_FCT.ACTVTY_TIME_KEY = DFLTDIMMGR.BUSN_DAY(20080901,'P') THEN
COUNT(VW_DFLT_LOAN_BCKT_FCT.DFLT_LOAN_KEY)
ELSE
0
END bom,
CASE
WHEN VW_DFLT_LOAN_BCKT_FCT.ACTVTY_TIME_KEY = DFLTDIMMGR.BUSN_DAY(20080924,'P') THEN
COUNT(VW_DFLT_LOAN_BCKT_FCT.DFLT_LOAN_KEY)
ELSE
0
END eom,
CASE
WHEN loan_actvty < 0 THEN
SUM(DFLT_LOAN_FCT.UNPAID_PRINCIPAL_BAL)
ELSE
0
END neg_bal,
CASE
WHEN loan_actvty > 0 THEN
SUM(DFLT_LOAN_FCT.UNPAID_PRINCIPAL_BAL)
ELSE
0
END pos_bal,
CASE
WHEN loan_actvty = 0 THEN
SUM(DFLT_LOAN_FCT.UNPAID_PRINCIPAL_BAL)
ELSE
0
END nos_bal
FROM DFLTMRTMGR.VW_DFLT_LOAN_BCKT_FCT,
DFLTDIMMGR.DFLT_LOAN_DIM,
DFLTMRTMGR.DFLT_LOAN_FCT
WHERE VW_DFLT_LOAN_BCKT_FCT.ACTVTY_TIME_KEY BETWEEN 20080901 AND 20080924
AND VW_DFLT_LOAN_BCKT_FCT.DFLT_LOAN_KEY = DFLT_LOAN_DIM.DFLT_LOAN_KEY
AND VW_DFLT_LOAN_BCKT_FCT.TIME_KEY = DFLT_LOAN_FCT.TIME_KEY
AND DFLT_LOAN_DIM.DFLT_LOAN_KEY = DFLT_LOAN_FCT.DFLT_LOAN_KEY
--(select param_val from STGEDWMGR.ctl_global_parm where param_name = 'DFLT_BATCH_DATE')
GROUP BY ACTVTY_TIME_KEY,VW_DFLT_LOAN_BCKT_FCT.TIME_KEY,TEAM_KEY,VW_DFLT_LOAN_BCKT_FCT.BCKT_KEY,loan_actvty
) activity,
(
SELECT CD_KEY,bckt_name,
TEAM_KEY,AGENT_NAME,
cbp,lvl
FROM
(
SELECT CD_KEY, cd_desc bckt_name
FROM DFLTDIMMGR.CODE_DIM
WHERE CD_CTGRY = 'BUCKET'
--AND SYSDATE BETWEEN from_eff_dt AND to_eff_dt
) buckets,
(
SELECT
TEAM_KEY,
LPAD('>',6*(LEVEL-1),'--') ||PARTICIPANT_LAST_NM||','
||PARTICIPANT_FRST_NM AGENT_NAME,
sys_connect_by_path(substr(PARTICIPANT_FRST_NM,0,1)||PARTICIPANT_LAST_NM, '/') cbp,
level lvl
FROM DFLTDIMMGR.DFLT_TEAM_DIM
START WITH PARTICIPANT_ID = '1227424'
CONNECT BY PRIOR PARTICIPANT_ID = PARENT_PARTICIPANT_ID
) team
WHERE 1 = 1
) team_buckets
WHERE team_buckets.cd_key = activity.BCKT_KEY(+)
AND team_buckets.team_key = activity.team_key(+)
GROUP BY team_buckets.agent_name,
team_buckets.lvl,
team_buckets.cbp,
team_buckets.bckt_name
)
SELECT AGENT_NAME,bckt_name,cbp,lvl,
(max(lvl) over (partition by 1)-(lvl-1)) reverse_level,
NVL(SUM(bom),0),NVL(SUM(eom),0),
NVL(SUM(tot),0),NVL(SUM(tot_negs),0),NVL(SUM(tot_pos),0),NVL(SUM(tot_nos),0),
NVL(SUM(tot_neg_bal),0),NVL(SUM(tot_pos_bal),0),NVL(SUM(tot_nos_bal),0)
FROM
(
SELECT
a.agent_name,
a.lvl,
a.cbp,
NVL(a.bckt_name,b.bckt_name) bckt_name,
case
SUBSTR(b.cbp,1,LENGTH(a.cbp))
when a.cbp then
CASE
WHEN NVL(a.bckt_name,b.bckt_name) = b.bckt_name THEN
SUM(b.eom)
ELSE
0
END
else
NULL
end eom,
case
SUBSTR(b.cbp,1,LENGTH(a.cbp))
when a.cbp then
CASE
WHEN NVL(a.bckt_name,b.bckt_name) = b.bckt_name THEN
SUM(b.bom)
ELSE
0
END
else
NULL
end bom,
case
SUBSTR(b.cbp,1,LENGTH(a.cbp))
when a.cbp then
CASE
WHEN NVL(a.bckt_name,b.bckt_name) = b.bckt_name THEN
SUM(b.ln_cts_tot)
ELSE
0
END
else
NULL
end tot,
case
SUBSTR(b.cbp,1,LENGTH(a.cbp))
when a.cbp then
CASE
WHEN NVL(a.bckt_name,b.bckt_name) = b.bckt_name THEN
SUM(b.negs)
ELSE
0
END
else
NULL
end tot_negs,
case
SUBSTR(b.cbp,1,LENGTH(a.cbp))
when a.cbp then
CASE
WHEN NVL(a.bckt_name,b.bckt_name) = b.bckt_name THEN
SUM(b.pos)
ELSE
0
END
else
NULL
end tot_pos,
case
SUBSTR(b.cbp,1,LENGTH(a.cbp))
when a.cbp then
CASE
WHEN NVL(a.bckt_name,b.bckt_name) = b.bckt_name THEN
SUM(b.nos)
ELSE
0
END
else
NULL
end tot_nos,
case
SUBSTR(b.cbp,1,LENGTH(a.cbp))
when a.cbp then
CASE
WHEN NVL(a.bckt_name,b.bckt_name) = b.bckt_name THEN
SUM(b.neg_bal)
ELSE
0
END
else
NULL
end tot_neg_bal,
case
SUBSTR(b.cbp,1,LENGTH(a.cbp))
when a.cbp then
CASE
WHEN NVL(a.bckt_name,b.bckt_name) = b.bckt_name THEN
SUM(b.pos_bal)
ELSE
0
END
else
NULL
end tot_pos_bal,
case
SUBSTR(b.cbp,1,LENGTH(a.cbp))
when a.cbp then
CASE
WHEN NVL(a.bckt_name,b.bckt_name) = b.bckt_name THEN
SUM(b.nos_bal)
ELSE
0
END
else
NULL
end tot_nos_bal
FROM
temp_table_aggr a,
temp_table_aggr b
GROUP BY a.agent_name,a.bckt_name,b.bckt_name,a.cbp,b.cbp,a.lvl
)
WHERE bckt_name IS NOT NULL AND tot <> 0
GROUP BY AGENT_NAME,bckt_name,cbp,lvl
ORDER BY cbp
October 01, 2008 - 11:52 am UTC
eh?
first, what use is posting a 15 page query - no one could digest that.
Second - how could anyone answer:
"What makes Oracle decide one way or the other?"
one way or the other about what exactly???
What makes Oracle decide one way or the other?
oj, October 01, 2008 - 12:36 pm UTC
Sorry about that. I put the query in desperation. What is happening is that the query works perfectly fine if I use two views (traditional way). As soon as I put it in this way with subquery factoring, it shows a drastically low cost but does not return the result for a long long time and then gives no rows returned.
I was sure that I must have done some copy paste mistake but re-checked it and did not find any.
Looking at a query like this, would there be a nicer way to write it? I was thinking of trying the ' Model dimension measure' construct.
October 01, 2008 - 1:17 pm UTC
why do you have hints in there. You are forcing the optimizer to do something it might not want to do.
Are you saying "this query should return rows, provably so" but does not? If so, please contact support with the test case.
What makes Oracle decide one way or the other?
oj, October 01, 2008 - 2:36 pm UTC
Thanks for all your responses.
The hint was just one of the trial and error things I did. Did not make a difference to the plan or results.
I will contact support and see if they can validate/verify.
Thanks again.
How to declare a cursor in Pro*C using the WITH clause?
rama, October 15, 2008 - 7:50 pm UTC
Hi Tom,
I am modifying an existing Pro*C process that reads in a lot of data. Right now it is declared in the form:
EXEC SQL DECLARE <cursor_name> CURSOR FOR
SELECT columns
FROM table1, table2....
WHERE <where_clause>;
How would I modify the cursor to use the WITH clause?
Thanking you
Regards
Rama.
October 17, 2008 - 8:29 pm UTC
currently, you'd have to use dynamic sql or hide the WITH construct in a view
Thanks, Tom. I thought I was doing something wrong so I thought I will check with you.
rama, October 20, 2008 - 4:07 pm UTC
Regards
result set not materialized with rownum
James Su, February 07, 2009 - 5:53 pm UTC
Dear Tom,
version 10.2.0.1.0
create table test (id number);
insert into test values (1);
insert into test values (2);
insert into test values (3);
select *
from test
,(select dbms_random.value rnd, rownum rn from dual);
ID RND RN
--------- ---------- ----------
1 .587863701 1
2 .454931209 1
3 .454931209 1
You can see dbms_random.value was called twice, even rownum can't stop it.
But this happens in sqlplus only. When I tested it in TOAD it was fine.
February 09, 2009 - 6:15 pm UTC
use with
with data as (select ... from dual)
select * from test, data;
with doesn't work
James Su, February 09, 2009 - 9:00 pm UTC
Dear Tom,
I tried with subquery but it still doesn't work:
with data as (select dbms_random.value rnd, rownum rn from dual)
select * from test, data;
ID RND RN
---------- ---------- ----------
1 .681603252 1
2 .107975268 1
3 .107975268 1
February 10, 2009 - 7:03 am UTC
materialize hint would probably do it, but as you can see, it is not going to be "assured"
why does client matter
James Su, February 10, 2009 - 11:18 am UTC
hi Tom,
But when I tried it in other clients, like:
PLSQL Developer, TOAD, and iSqlplus, it could not be reproduced (resultset was materialized).
Why does client matter?
CTAS and INSERT ... SELECT ... are both fine:
CREATE TABLE test2 AS
with data as (select dbms_random.value rnd, rownum rn from dual)
select * from test, data;
SELECT * FROM TEST2;
ID RND RN
---------- ---------- ----------
1 ,888141391 1
2 ,888141391 1
3 ,888141391 1
DELETE TEST2;
INSERT INTO TEST2
with data as (select dbms_random.value rnd, rownum rn from dual)
select * from test, data;
SELECT * FROM TEST2;
ID RND RN
---------- ---------- ----------
1 ,39375538 1
2 ,39375538 1
3 ,39375538 1
So it happens only in select in SQLPLUS, that looks weird to me.
February 11, 2009 - 9:23 am UTC
look at the plans and they'll be different.
you are relying on a specific plan to happen, and that is never assured.
In this case, you will absolutely want to
a) call dbms_random.value
b) bind that value
if you need it to be predicable
Regarding hints for inline views
Gaurav, February 24, 2009 - 8:55 am UTC
Hello Tom
I was looking around for an answer to my doubts, but didn't find it anywhere, and so here I am -with some trepidation and hoping this doesn't qualify as a "New question".
My situation is ...
I need to tune a query running into hundreds of lines containing more than a hundred GROUP BY expressions among other things. Here two fact tables (40m rows each, one partitioned) are joined , along with 8 dimension tables of varying sizes, some self-joined. All these are joined to two views, the two views again being joins involving two other fact tables and other smaller tables. The two views return 26 million rows each. The query returns millions of rows and there are LOTS of full-table scans.
I am NOT allowed to create any indexes or to materialize anything or make any substantial changes. So far only parallelizing has achieved some results.
My question is:
(1) Since, in my humble understanding, Oracle merges the text of the views into the main body of the query, can I place a parallel(table_name) hint with the table_name of the
fact tables in the two views? These two tables are not referenced in the main query.
(2)In case that doesn't work, would it work to use the view text as inline views in the main query, with the parallel hints placed in the main body?
(3)Similar to (2) above, I can possibly replace the two views in the with clause. However, can I use the parallel hint on the fact tables in the with clauses ?
In case - in 2 or 3 above, parallel hints can be placed on the tables in the with clauses or inline views - where should these be placed - in the views / inline views themselves or in the body of the main query after the SELECT statement?
It would help if you give some examples or some references.
with clause
A reader, April 24, 2009 - 10:04 pm UTC
Hi Tom,
If a front end BI tools generates a query with WITH clause, and there is a Materialized view on the base table in the oracle db (10gR1). Will Oracle use MV with query rewrite enabled?
Thanks
April 27, 2009 - 1:32 pm UTC
the front end BI tool is not relevant... The question is
can a query with WITH subquerying factoring be rewritten to use a materialized view?
Answer: absolutely yes
ops$tkyte%ORA10GR2> create table t1
2 as
3 select *
4 from all_objects;
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create materialized view mv1
2 enable query rewrite
3 as
4 select distinct owner
5 from t1
6 /
Materialized view created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t2
2 as
3 select *
4 from t1;
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create materialized view mv2
2 enable query rewrite
3 as
4 select owner, count(*)
5 from t2
6 group by owner
7 /
Materialized view created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
2 dbms_stats.gather_table_stats( user, 'T1' );
3 dbms_stats.gather_table_stats( user, 'T2' );
4 dbms_stats.gather_table_stats( user, 'MV1' );
5 dbms_stats.gather_table_stats( user, 'MV2' );
6 end;
7 /
PL/SQL procedure successfully completed.
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 distinct owner
4 from t1
5 where owner like '%X%' )
6 select count(*)
7 from t2
8 where owner in ( select * from data )
9 /
Execution Plan
----------------------------------------------------------
Plan hash value: 289368505
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 5 (
| 1 | SORT AGGREGATE | | 1 | 19 |
|* 2 | HASH JOIN SEMI | | 2 | 38 | 5 (
|* 3 | MAT_VIEW REWRITE ACCESS FULL| MV2 | 2 | 24 | 2
|* 4 | MAT_VIEW REWRITE ACCESS FULL| MV1 | 2 | 14 | 2
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MV2"."OWNER"="MV1"."OWNER")
3 - filter("MV2"."OWNER" LIKE '%X%')
4 - filter("MV1"."OWNER" LIKE '%X%')
ops$tkyte%ORA10GR2> set autotrace off
How was 75%++ reducation calculated
A reader, May 28, 2009 - 11:14 am UTC
In an early exampele in this thread, you said "See the 75%++ reduction in LIO's -- we didn't have to distinct owner from all_users two times.". My question is how this 75% reduction was calculated.
May 28, 2009 - 2:04 pm UTC
sorry, I think
.. See the 75%++ reduction in LIO's ...
should have read
see how the one query uses less than 75% the LIO of the other
or
see how we reduced the amount of logical IO to less than 75% of what it otherwise would be.
open cursor with clause
Samy, May 29, 2009 - 8:11 am UTC
Hi TOM,
can we use Open v_Cursor For With clause in PLSQL Block.
i tried it but was giving error.
Procedure SearchEmpDtls(vEmpno Varchar(10),io_Cursor in out t_cursor)
is
v_Cursor t_cursor;
Begin
Open v_Cursor For
With Empdtls as ( Select Empno , Ename , Hiredate From EMP where Empno = vEmpno )
Select Empno , Ename , Hiredate From Empdtls Where
Hiredate in ( Select Max(Hiredate) from Empdtls );
io_cursor:=v_cursor;
End;
May 29, 2009 - 8:40 am UTC
well, your code has a few mistakes
a) everyone knows empno is a number
b) you cannot have constrained types as parameters (varchar2(10) - not allowed, varchar2 - allowed)
c) t_cursor is not a type I know of
d) this obviously must be in a package
so, you give something that could never compile - even on a good day....
ops$tkyte%ORA9IR2> create table emp as select * from scott.emp;
Table created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace package my_pkg
2 as
3 type t_cursor is ref cursor;
4
5 Procedure SearchEmpDtls(vEmpno number,io_Cursor in out t_cursor);
6 end;
7 /
Package created.
ops$tkyte%ORA9IR2> show errors
No errors.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace package body my_pkg
2 as
3
4 Procedure SearchEmpDtls(vEmpno number,io_Cursor in out t_cursor)
5 is
6 v_Cursor t_cursor;
7 Begin
8
9 Open v_Cursor For
10 With Empdtls as ( Select Empno , Ename , Hiredate From EMP where Empno = vEmpno )
11 Select Empno , Ename , Hiredate From Empdtls Where
12 Hiredate in ( Select Max(Hiredate) from Empdtls );
13
14 io_cursor:=v_cursor;
15
16 End;
17 end;
18 /
Package body created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> variable x refcursor
ops$tkyte%ORA9IR2> exec my_pkg.searchempdtls( 7788, :x )
PL/SQL procedure successfully completed.
ops$tkyte%ORA9IR2> print x
EMPNO ENAME HIREDATE
---------- ---------- ---------
7788 SCOTT 09-DEC-82
WITH Clause issue
TAD259, June 16, 2009 - 2:16 pm UTC
In 10gR2 on AIX, why does this work . . .
SELECT AA.AccrualValue.DEMANDINTERESTEARNED
FROM (
SELECT DemandAccrualValue.GetAccrualValue(1000041, NULL, DATE '2010-03-31') AccrualValue
FROM DUAL
) AA
where this does not . . .
WITH AA
AS (
SELECT DemandAccrualValue.GetAccrualValue(1000041, NULL, DATE '2010-03-31') AS AccrualValue
FROM DUAL
)
SELECT AA.AccrualValue.DEMANDINTERESTEARNED
FROM AA
failing with "AA"."ACCRUALVALUE"."DEMANDINTERESTEARNED": invalid identifier.
DemandAccrualValue is, of course, a user-defined type.
Sincere thanks in anticipation.
June 16, 2009 - 2:57 pm UTC
it is of course a udt I don't have :(
as if your function :( :(
my testing does not show what you say you see - so, given no test case - I have no answer other than "you might have typed in something wrong"
ops$tkyte%ORA10GR2> drop type myScalarType;
Type dropped.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace type myScalarType
2 as object
3 ( x int,
4 y date,
5 z varchar2(30)
6 )
7 /
Type created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select *
2 from (select myScalarType( 1, sysdate, 'hello world' ) as x from dual ) d
3 /
X(X, Y, Z)
-------------------------------------------------------------------------------
MYSCALARTYPE(1, '16-JUN-09', 'hello world')
ops$tkyte%ORA10GR2> with data
2 as
3 (select myScalarType( 1, sysdate, 'hello world' ) as x from dual )
4 select *
5 from data d
6 /
X(X, Y, Z)
-------------------------------------------------------------------------------
MYSCALARTYPE(1, '16-JUN-09', 'hello world')
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select d.x.y
2 from (select myScalarType( 1, sysdate, 'hello world' ) as x from dual ) d
3 /
X.Y
---------
16-JUN-09
ops$tkyte%ORA10GR2> with data
2 as
3 (select myScalarType( 1, sysdate, 'hello world' ) as x from dual )
4 select d.x.y
5 from data d
6 /
X.Y
---------
16-JUN-09
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace function f return myScalarType
2 as
3 begin
4 return myScalarType( 1, sysdate, 'hello world' );
5 end;
6 /
Function created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select d.x.y
2 from (select f as x from dual ) d
3 /
X.Y
---------
16-JUN-09
ops$tkyte%ORA10GR2> with data
2 as
3 (select f as x from dual )
4 select d.x.y
5 from data d
6 /
X.Y
---------
16-JUN-09
To TAD259 from Toronto
Kim Berg Hansen, June 18, 2009 - 9:35 am UTC
You write:
WITH AA
AS (
SELECT DemandAccrualValue.GetAccrualValue(1000041, NULL, DATE '2010-03-31') AS AccrualValue
FROM DUAL
)
SELECT AA.AccrualValue.DEMANDINTERESTEARNED
FROM AAYou need to "alias the alias" for object referencing to work:
WITH AA
AS (
SELECT DemandAccrualValue.GetAccrualValue(1000041, NULL, DATE '2010-03-31') AS AccrualValue
FROM DUAL
)
SELECT aa_alias.AccrualValue.DEMANDINTERESTEARNED
FROM AA aa_aliasJust as Tom did in his example where he aliases "data" as "d".
Hope that helps :-)
June 18, 2009 - 3:23 pm UTC
doh, of course :)
thanks!
"UNION ALL" inside WITH or Inline View
Parthiban Nagarajan, June 26, 2009 - 7:43 am UTC
Hi Tom ...
Let us have the sample data as follows ...
create table a_tabs as select table_name from user_tables where table_name like 'A%';
create table b_tabs as select table_name from user_tables where table_name like 'B%';
-- The Parameter ...
var p_starts_with varchar2(1)
exec :p_starts_with := 'A';
-- For the query ...
select * from (
select 'A' starts_with, table_name from a_tabs
union all
select 'B' starts_with, table_name from b_tabs
) where starts_with = :p_starts_with ;
... because I'm using "UNION ALL" instead of "UNION", I believe, Oracle will select the records from "a_tabs" and won't go for "b_tabs" at all ...
Am I right ... ?
At least, is it right when I use the parameter value directly instead of using the bind variable ?
Thanks in Advance ...
-- The clean-up
drop table a_tabs;
drop table b_tabs;
June 26, 2009 - 11:03 am UTC
why didn't you test it using tkprof and see? It is what I would have to do to "show" you what happens.
ops$tkyte%ORA10GR2> select * from (
2 select 'A' starts_with, table_name from a_tabs
3 union all
4 select 'B' starts_with, table_name from b_tabs
5 ) where starts_with = :p_starts_with ;
no rows selected
ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID 4yygg48fpc7v2, child number 0
-------------------------------------
select * from ( select 'A' starts_with, table_name from a_tabs union
all select 'B' starts_with, table_name from b_tabs ) where
starts_with = :p_starts_with
Plan hash value: 4062280091
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | VIEW | | 2 | 40 | 4 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | FILTER | | | | | |
| 4 | TABLE ACCESS FULL| A_TABS | 1 | 17 | 2 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
| 6 | TABLE ACCESS FULL| B_TABS | 1 | 17 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter('A'=:P_STARTS_WITH)
5 - filter('B'=:P_STARTS_WITH)
Note
-----
- dynamic sampling used for this statement
30 rows selected.
but yes, the file steps on lines 3 and 5 would cause one or the other or neither table to be scanned - depending on the value of the bind variable.
Not materializing?
Lise, August 12, 2009 - 7:09 am UTC
Hi,
I have a query that uses a simple WITH subquery written in 9iR2.
It looks like this:
WITH policy AS (SELECT b.col1 policy_reference
FROM table_a b
WHERE b.col1 BETWEEN 1 AND 100000
)
SELECT *
FROM (SELECT a.col1
,a.col2
FROM table_b a
,table_c c
,policy
WHERE a.col1 = policy.policy_reference
AND c.col2 = a.col2
UNION ALL
SELECT c.col1
,c.col2
FROM table_c c
,policy
,table_b b
WHERE c.col1 = policy.policy_reference
AND b.col2 = c.col2) increments
,(SELECT a.col1
,SUM(a.col1) tot
,a.col2
FROM table_b a
,policy
WHERE a.col1 = policy.policy_reference
GROUP BY a.col1
,a.col2) employer_grsprmamt
WHERE employer_grsprmamt.col1 = increments.col1
AND employer_grsprmamt.col2 = increments.col2
If I try to run this it will work. However, with the business tables and data I am using, I get an ORA-00942 (table or view does not exist).
If I change the UNION ALL to UNION it works, or if I only include one of the statements in the main WHERE clause it works.
I have added in the materialize hint, but since I cannot even create the explain plan, I do not know if it is used.
I can only assume that it is not using it, and hence causing the ORA error when the WITH subquery is referenced in the inline views?
I have tried the same query in 10g and it seems to work fine.
Any suggestions would be greatly appreciated.
August 13, 2009 - 11:47 am UTC
I'd need your help to reproduce
as in "the tables"
Metalink bug
Lise, September 02, 2009 - 5:07 am UTC
Hi,
Please ignore my previous question. I found on metalink that this is a bug that has been fixed in release 10g.
Thanks
DML using subquery factoring
Ravi, November 13, 2009 - 4:28 pm UTC
Hi Tom,
Is there any limitation for UPDATE using WITH clause?
Following construct works fine (single column update)
UPDATE emp e
SET sal = ( WITH q1 AS (
SELECT empno,sal,deptno
FROM emp
),
q2 AS (
SELECT deptno from dept)
SELECT sal + 100
FROM q1,q2
where q1.deptno=q2.deptno
AND e.empno=q1.empno
)
But the following query gives the error (multi column update)
UPDATE emp e
SET (sal,comm) = ( WITH q1 AS (
SELECT empno,sal,deptno
FROM emp
),
q2 AS (
SELECT deptno from dept)
SELECT sal + 100, 100
FROM q1,q2
where q1.deptno=q2.deptno
AND e.empno=q1.empno
)
ORA-01767: UPDATE ... SET expression must be a subquery
Thanks,
Ravi
November 15, 2009 - 3:16 pm UTC
ops$tkyte%ORA10GR2> UPDATE emp e
2 SET (sal,comm) = ( WITH q1 AS (
3 SELECT empno,sal,deptno
4 FROM emp
5 ),
6 q2 AS (
7 SELECT deptno from dept)
8 SELECT sal + 100 sal, 100 comm
9 FROM q1,q2
10 where q1.deptno=q2.deptno
11 AND e.empno=q1.empno)
12 /
SET (sal,comm) = ( WITH q1 AS (
*
ERROR at line 2:
ORA-01767: UPDATE ... SET expression must be a subquery
ops$tkyte%ORA10GR2> UPDATE emp e
2 SET (sal,comm) = (
3 SELECT sal + 100 sal, 100 comm
4 FROM dual )
5 /
14 rows updated.
I would concur, looks like a parser bug, can you file with support? Let me know, if not, I will
no metalink support
Ravi, November 15, 2009 - 4:47 pm UTC
Hi Tom,
We dont have metalink account. If possible, could you please file a bug.
Thanks.
November 23, 2009 - 8:58 am UTC
filed bug 9147631
WITH Clause within a View
RAmchandra Joshi, January 21, 2010 - 8:38 am UTC
Hi Tom,
Can you please elaborate as to how oracle processes a WITH clause which is defined in a view ?
I'm currently facing a strange problem where my view definition contains a WITH clause query and I'm using this view with another WITH clause and a UNION ALL operation.
So the query format is something like
WITH v_temp as ( SELECT * FROM view_with_with_clause )
select * from v_temp
union all
select * from v_temp
But to my surprise the results are different when I run this query.
So just wanted to understand how oracle actually processes a WITH clause withing view ?
It must be sounding weird to you currently but I'm preparing a test case for you to reproduce.
Till then if you can throw some light on the processing , It would be really helpful.
Thanks in advance,
Ram.
January 21, 2010 - 10:36 am UTC
give us a for example. a test case.
... But to my surprise the results are different when I run this query. ...
different than WHAT exactly?????
if you want to see how your query is probably getting processed, run an explain plan on it.
different behaviour of with-clause with and without materialize-hint
Sokrates, March 29, 2010 - 3:53 am UTC
Tom, I noticed the following difference in behaviour of with-clause, reproduced on 11.2.0.1 and 10.2.0.2
Is it a bug or intended behaviour ?
(I vote for the 1st, as I think, the result of a query should not depend upon a hint)
11.2.0.1 > create function fdummy return varchar2 is
2 begin
3 return 'X';
4 end;
5 /
Function created.
11.2.0.1 > with a as (select fdummy b from dual)
2 select count(*), b
3 from a, dual
4 where dummy = b;
COUNT(*)
----------
B
---------------------------------------------------------------------------------------
1
X
11.2.0.1 > edit
Wrote file afiedt.buf
1 with a as (select /*+materialize */ fdummy b from dual)
2 select count(*), b
3 from a, dual
4* where dummy = b
11.2.0.1 > /
select count(*), b
*
ERROR at line 2:
ORA-00937: not a single-group group function
April 05, 2010 - 9:34 am UTC
technically - I think the first one is a bug, it should demand a group by as well. But I see what they did with the rewrite, they merged away the with subquery and flattened everything .
You do have to be careful when calling non-deterministic functions from SQL - can you imagine what would happen if all references to b are replaced with fdummy (which they were) and treated as non-constant instead of constant - and fdummy did return different values...
Force inline behaviour when using WITH clause
Dave, May 19, 2010 - 6:31 am UTC
Hi Tom,
Refactored sub queries are fantastic for performance and make some SQL sooooo much easier to read and maintain, however there are conditions where you know you would not want the CBO to materialize the subquery in the WITH clause.
So is there a technique (other than inlining the queries in the SQL again or using the undocumented /*+ inline */ hint) to force the inline rather than materialzed use of the sub queries?
Or is using the WITH clause with the only objective being to make the SQL more maintainable a bad idea?
Many thanks
May 24, 2010 - 10:38 am UTC
"WITH" does NOT mandate "materialize". It will inline queries whenever it makes sense to do so.
Re: Force inline behaviour when using WITH clause
Dave, May 20, 2010 - 3:57 am UTC
Ignore me, i'll somewhat obviously need to create a view. Sometimes I find myself looking for problems where there are none, maybe I haven't enought to do ;)
With clause performance problem
Praveen, May 21, 2010 - 2:19 pm UTC
Hi Tom,
One of my query which contains with clause is taking less than 1 sec in
dev while in production it is taking lot of time and consuming lot of
CPU memory. The amount of data is same in dev and prod.
Can you please let me know where iam going wrong in the below query :
SQL> select 'p',count(*) from sof_products
2 union all
3 select 's',count(*) from sof
4 union all
5 select 'w',count(*) from sof_work_unit
6 union all
7 select 'm',count(*) from products_master
8 union all
9 select 'c', count(*) from status
10 union all
11 select 'q',count(*) from TED_QUEUE_STATUS
12 union all
13 select 't', count(*) from prod_tab where category in
('P','C');
' COUNT(*)
- ----------
p 1694197
s 860518
w 110632
m 243
c 23
q 79
t 126
7 rows selected.
Elapsed: 00:00:00.56
SQL> set autotrace traceonly explain
SQL> variable p_category number;
SQL> variable p_display_ref_no varchar2;
SQL> variable p_customer varchar2;
SQL> variable p_request_id varchar2
SQL> variable p_status varchar2;
SQL> variable p_user_id1 varchar2;
SQL> variable p_subgrp varchar2;
SQL> SELECT orderby_assigned , orderby_submitted_date ,
display_ref_no , ref_no , Dcmfile_existing_flag ,
2 status_code , product_id , sub_status_code ,
company_name , sof_submit_date , created_by , assigned_date ,
3 created_by_user_name ,status_desc, MASTERPRODUCT_ID,
CREATED_BY_SUBGROUP, SEND_TO_SUBGROUP,
4 WORK_REQUEST_SENT_DATE, FILE_UPLOADED, STATUS_DATE,
CREATED_DATE, WORK_UNIT_ID, WORKUNIT, ASSIGNED,
5 INITIAL_DESCRIPTION, NICKNAME, CATEGORY
6 FROM (
7 WITH TEMP AS (
8 SELECT /*+ MATERIALIZE */
9 UNIQUE
10 E.orderby_assigned , E.orderby_submitted_date ,
E.display_ref_no , E.dcmfile_existing_flag , E.COMPANY_NAME,
11 E.CREATED_BY_USER_NAME,
12
DECODE(E.CATEGORY,'P',E.PRODUCT_ID||'ZZZZZZZZZZ','C',E.MASTERPRODUCT_ID)
MASTERPRODUCT_ID,
13 E.sof_submit_date, A.REF_NO, A.SEND_TO_SUBGROUP,
A.WORK_REQUEST_SENT_DATE, A.FILE_UPLOADED, A.STATUS_DATE,
14 A.CREATED_BY, A.CREATED_DATE, A.WORK_UNIT_ID, A.RELATED_ITEM,
15 DECODE(INSTR(SUBSTR(A.WORK_UNIT_ID,
LENGTH(A.RELATED_ITEM)+1),'.'),
16 0, LPAD(SUBSTR(A.WORK_UNIT_ID,LENGTH(A.RELATED_ITEM)+1),3,'0'),
17 2, '00'||SUBSTR(REPLACE(A.WORK_UNIT_ID, '.10',
'.91'),LENGTH(A.RELATED_ITEM)+1),
18 3, '0'||SUBSTR(REPLACE(A.WORK_UNIT_ID, '.10', '.91'),
LENGTH(A.RELATED_ITEM)+1),
19 4, SUBSTR(REPLACE(A.WORK_UNIT_ID, '.10', '.91'),
LENGTH(A.RELATED_ITEM)+1)) AS WORKUNIT,
20 A.ASSIGNED_DATE, A.ASSIGNED, A.STATUS_CODE, A.SUB_STATUS_CODE,
A.CREATED_BY_SUBGROUP, A.INITIAL_DESCRIPTION,
21 B.STATUS_DESC, A.NICKNAME, A.PARENT_REJECTED,
DECODE(E.CATEGORY,'P','PW','C','CW') CATEGORY
22 FROM STATUS B,
23 PRODUCTS_MASTER M,
24 SOF_PRODUCTS C,
25 SOF_WORK_UNIT A,
26 TED_QUEUE_STATUS Q,
27 PROD_TAB E
28 WHERE A.STATUS_CODE=B.STATUS_CODE
29 AND C.PRODUCT_ID = M.PRODUCT_ID
30 AND C.STATUS_CODE IS NOT NULL
31 AND Q.CATEGORY = (CASE
32 WHEN :P_CATEGORY <> 0 THEN
33 'AS'
34 WHEN :P_DISPLAY_REF_NO IS NOT NULL THEN
35 'AS'
36 WHEN :P_CUSTOMER IS NOT NULL THEN
37 'AS'
38 WHEN :P_REQUEST_ID IS NOT NULL THEN
39 'AS'
40 WHEN :P_STATUS IS NOT NULL THEN
41 'AS'
42 WHEN :P_USER_ID1 IS NOT NULL THEN
43 'AS'
44 ELSE
45 'A'
46 END
47 )
48 AND
DECODE(:P_STATUS,NULL,'X',DECODE(:p_category,0,A.STATUS_CODE||A.SUB_STAT
US_CODE,1,'X',2,'X',3,
49
A.STATUS_CODE||A.SUB_STATUS_CODE,4,'X',5,'X',6,A.STATUS_CODE||A.SUB_STAT
US_CODE,7,A.STATUS_CODE||A.SUB_STATUS_CODE,
50 8,A.STATUS_CODE||A.SUB_STATUS_CODE))
51 IN (select NVL(COLUMN_VALUE,'X') from TABLE(cast(
aes_ted_work_items_pkg.str2tbl(:P_STATUS ) as mytableType)))
52 AND A.RELATED_ITEM = C.PRODUCT_ID
53 AND A.REF_NO = C.REF_NO
54 AND Q.STATUS_CODE = A.STATUS_CODE
55 AND ((A.SEND_TO_SUBGROUP=:P_SUBGRP
56 AND A.STATUS_CODE NOT IN ('DEC','RJT')
57 )
58 OR A.CREATED_BY_SUBGROUP=:P_SUBGRP
59 )
60 AND NVL(A.ASSIGNED,'X') =
DECODE(:P_USER_ID1,NULL,NVL(A.ASSIGNED,'X'),:P_USER_ID1)
61 AND A.REF_NO= E.REF_NO
62 AND A.RELATED_ITEM = E.PRODUCT_id
63 AND E.CATEGORY IN ('P','C')
64 )
65 SELECT UNIQUE
66 T.orderby_assigned , T.orderby_submitted_date ,
T.display_ref_no , T.dcmfile_existing_flag , T.COMPANY_NAME,
67 T.CREATED_BY_USER_NAME, T.MASTERPRODUCT_ID, T.sof_submit_date,
T.REF_NO, T.SEND_TO_SUBGROUP,
68 T.WORK_REQUEST_SENT_DATE, T.FILE_UPLOADED, T.STATUS_DATE,
T.CREATED_BY, T.CREATED_DATE, T.WORK_UNIT_ID,
69 T.RELATED_ITEM PRODUCT_ID, T.WORKUNIT, T.ASSIGNED_DATE,
T.ASSIGNED, T.STATUS_CODE, T.SUB_STATUS_CODE,
70 T.CREATED_BY_SUBGROUP, T.INITIAL_DESCRIPTION, T.STATUS_DESC,
T.NICKNAME, T.CATEGORY
71 FROM TEMP T
72 UNION ALL
73 SELECT UNIQUE
74 E.orderby_assigned , E.orderby_submitted_date ,
E.display_ref_no , E.dcmfile_existing_flag , E.COMPANY_NAME,
75 E.CREATED_BY_USER_NAME,
76
DECODE(E.CATEGORY,'P',E.PRODUCT_ID||'ZZZZZZZZZZ','C',E.MASTERPRODUCT_ID)
MASTERPRODUCT_ID,
77 E.sof_submit_date, A.REF_NO, A.SEND_TO_SUBGROUP,
A.WORK_REQUEST_SENT_DATE, A.FILE_UPLOADED, A.STATUS_DATE,
78 A.CREATED_BY, A.CREATED_DATE, A.WORK_UNIT_ID, A.RELATED_ITEM,
79 DECODE(INSTR(SUBSTR(A.WORK_UNIT_ID,
LENGTH(A.RELATED_ITEM)+1),'.'),
80 0, LPAD(SUBSTR(A.WORK_UNIT_ID,
LENGTH(A.RELATED_ITEM)+1),3,'0'),
81 2, '00'||SUBSTR(REPLACE(A.WORK_UNIT_ID, '.10', '.91'),
LENGTH(A.RELATED_ITEM)+1),
82 3, '0'||SUBSTR(REPLACE(A.WORK_UNIT_ID, '.10', '.91'),
LENGTH(A.RELATED_ITEM)+1),
83 4, SUBSTR(REPLACE(A.WORK_UNIT_ID, '.10', '.91'),
LENGTH(A.RELATED_ITEM)+1)) AS WORKUNIT,
84 A.ASSIGNED_DATE, A.ASSIGNED, A.STATUS_CODE, A.SUB_STATUS_CODE,
A.CREATED_BY_SUBGROUP, A.INITIAL_DESCRIPTION,
85 B.STATUS_DESC,A.NICKNAME, DECODE(E.CATEGORY,'P','PW','C','CW')
CATEGORY
86 FROM SOF_WORK_UNIT C,
87 STATUS B,
88 SOF_WORK_UNIT A,
89 PROD_TAB E
90 WHERE A.REF_NO=C.REF_NO
91 AND A.RELATED_ITEM=C.RELATED_ITEM
92 AND A.STATUS_CODE=B.STATUS_CODE
93 AND A.WORK_UNIT_ID IN
94 ( C.PARENT_WORK_UNIT,SUBSTR(C.PARENT_WORK_UNIT, 0,
INSTR(C.PARENT_WORK_UNIT, '.', LENGTH(C.RELATED_ITEM), 1)-1))
95 AND (C.REF_NO, C.WORK_UNIT_ID, C.RELATED_ITEM) IN
96 (SELECT DISTINCT T.REF_NO,
97 T.WORK_UNIT_ID,
98 T.RELATED_ITEM
99 FROM TEMP T
100 WHERE T.REF_NO = C.REF_NO
101 AND T.WORK_UNIT_ID = C.WORK_UNIT_ID
102 AND T.RELATED_ITEM = C.RELATED_ITEM
103 )
104 AND A.REF_NO= E.REF_NO
105 AND A.RELATED_ITEM = E.PRODUCT_id
106 AND E.CATEGORY IN ('P','C')
107 );
Elapsed: 00:00:00.62
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=15 Card=2 Bytes=10
92)
1 0 VIEW (Cost=15 Card=2 Bytes=1092)
2 1 TEMP TABLE TRANSFORMATION
3 2 LOAD AS SELECT
4 3 SORT (UNIQUE) (Cost=187 Card=1 Bytes=295)
5 4 CONCATENATION
6 5 NESTED LOOPS (Cost=143 Card=1 Bytes=295)
7 6 NESTED LOOPS (Cost=143 Card=1 Bytes=287)
8 7 HASH JOIN (Cost=141 Card=1 Bytes=269)
9 8 NESTED LOOPS (Cost=121 Card=1 Bytes=267)
10 9 NESTED LOOPS (Cost=120 Card=1 Bytes=246)
11 10 NESTED LOOPS (Cost=120 Card=1 Bytes=24
0)
12 11 INLIST ITERATOR
13 12 TABLE ACCESS (BY INDEX ROWID) OF 'PROD_TAB' (TABLE (TEMP)) (Cost=7 Card=82 Bytes=12054)
14 13 INDEX (RANGE SCAN) OF 'X2PROD_TAB' (INDEX) (Cost=3 Card=33)
15 11 TABLE ACCESS (BY INDEX ROWID) OF 'SOF_WORK_UNIT' (TABLE) (Cost=2 Card=1 Bytes=93)
16 15 INDEX (RANGE SCAN) OF 'X1SOF_WORK_UNIT' (INDEX) (Cost=1 Card=1)
17 10 INDEX (RANGE SCAN) OF 'X1TED_QUEUE_STATUS' (INDEX) (Cost=0 Card=1 Bytes=6)
18 9 TABLE ACCESS (BY INDEX ROWID) OF 'STATUS' (TABLE) (Cost=1 Card=1 Bytes=21)
19 18 INDEX (UNIQUE SCAN) OF 'XPKSTATUS' (INDEX (UNIQUE)) (Cost=0 Card=1)
20 8 COLLECTION ITERATOR (PICKLER FETCH) OF 'STR2TBL' (PROCEDURE)
21 7 TABLE ACCESS (BY INDEX ROWID) OF 'SOF_PRODUCTS' (TABLE) (Cost=2 Card=1 Bytes=18)
22 21 INDEX (UNIQUE SCAN) OF 'XPKSOF_PRODUCTS' (INDEX (UNIQUE)) (Cost=1 Card=1)
23 6 INDEX (UNIQUE SCAN) OF 'XPKPRODUCTS_MASTER' (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=8)
24 5 HASH JOIN (Cost=42 Card=4 Bytes=1180)
25 24 NESTED LOOPS (Cost=21 Card=1 Bytes=293)
26 25 NESTED LOOPS (Cost=21 Card=1 Bytes=285)
27 26 NESTED LOOPS (Cost=19 Card=1 Bytes=267)
28 27 NESTED LOOPS (Cost=18 Card=1 Bytes=246)
29 28 HASH JOIN (Cost=18 Card=1 Bytes=240)
30 29 TABLE ACCESS (BY INDEX ROWID) OF 'SOF_WORK_UNIT' (TABLE) (Cost=11 Card=17 Bytes=1581)
31 30 INDEX (RANGE SCAN) OF 'X5SOF_WORK_UNIT' (INDEX) (Cost=7 Card=18)
32 29 INLIST ITERATOR
33 32 TABLE ACCESS (BY INDEX ROWID) OF 'PROD_TAB' (TABLE (TEMP)) (Cost=7 Card=82 Bytes=12054)
34 33 INDEX (RANGE SCAN) OF 'X2PROD_TAB' (INDEX) (Cost=3 Card=33)
35 28 INDEX (RANGE SCAN) OF 'X1TED_QUEUE_STATUS' (INDEX) (Cost=0 Card=1 Bytes=6)
36 27 TABLE ACCESS (BY INDEX ROWID) OF 'STATUS' (TABLE) (Cost=1 Card=1 Bytes=21)
37 36 INDEX (UNIQUE SCAN) OF 'XPKSTATUS' (INDEX (UNIQUE)) (Cost=0 Card=1)
38 26 TABLE ACCESS (BY INDEX ROWID) OF 'SOF_PRODUCTS' (TABLE) (Cost=2 Card=1 Bytes=18)
39 38 INDEX (UNIQUE SCAN) OF 'XPKSOF_PRODUCTS' (INDEX (UNIQUE)) (Cost=1 Card=1)
40 25 INDEX (UNIQUE SCAN) OF 'XPKPRODUCTS_MASTER' (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=8)
41 24 COLLECTION ITERATOR (PICKLER FETCH) OF 'STR2TBL' (PROCEDURE)
42 2 UNION-ALL
43 42 SORT (UNIQUE) (Cost=3 Card=1 Bytes=266)
44 43 VIEW (Cost=2 Card=1 Bytes=266)
45 44 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6734_C7E3A1A6' (TABLE (TEMP)) (Cost=2 Card=1 Bytes=268)
46 42 SORT (UNIQUE) (Cost=12 Card=1 Bytes=307)
47 46 NESTED LOOPS (Cost=11 Card=1 Bytes=307)
48 47 NESTED LOOPS (Cost=6 Card=1 Bytes=160)
49 48 NESTED LOOPS (Cost=5 Card=1 Bytes=139)
50 49 NESTED LOOPS (Cost=3 Card=1 Bytes=48)
51 50 VIEW (Cost=2 Card=1 Bytes=23)
52 51 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6734_C7E3A1A6' (TABLE (TEMP)) (Cost=2 Card=1 Bytes=268)
53 50 TABLE ACCESS (BY INDEX ROWID) OF 'SOF_WORK_UNIT' (TABLE) (Cost=1 Card=1 Bytes=25)
54 53 INDEX (UNIQUE SCAN) OF 'WORKUNIT_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)
55 49 TABLE ACCESS (BY INDEX ROWID) OF 'SOF_WORK_UNIT' (TABLE) (Cost=2 Card=1 Bytes=91)
56 55 INDEX (RANGE SCAN) OF 'X1SOF_WORK_UNIT' (INDEX) (Cost=1 Card=1)
57 48 TABLE ACCESS (BY INDEX ROWID) OF 'STATUS' (TABLE) (Cost=1 Card=1 Bytes=21)
58 57 INDEX (UNIQUE SCAN) OF 'XPKSTATUS' (INDEX (UNIQUE)) (Cost=0 Card=1)
59 47 INLIST ITERATOR
60 59 TABLE ACCESS (BY INDEX ROWID) OF 'PROD_TAB' (TABLE (TEMP)) (Cost=5 Card=1 Bytes=147)
61 60 INDEX (RANGE SCAN) OF 'X2PROD_TAB' (INDEX) (Cost=2 Card=2)
The above explain plan was taken in dev environment.
Thanks,
Praveen
Is this a bug in your opinion?
Alien, August 20, 2010 - 3:40 pm UTC
Hi Tom,
this is in response to the question / answer from 10th april 2010. A similar issue came up recently. And I'm wondering if this is a bug, or if there is a legal explanation:
create table t1 (num number);
insert into t1 values(1);
create table t2 (num number);
insert into t2 values(1);
with t as (select num as col from t1
union all
select num from t1
union all
select num from t1)
select num from t2
where 1 >= any (select col from t);
1
-
1
1
1
Expected was just one row. On the other hand it's arguable that the any (select ...) transforms to a join. Your opinion would be appreciated.
August 25, 2010 - 12:47 pm UTC
definitely a bug. do you have access to support to file it - you have the perfect test case.
Not a bug?
Michel Cadot, August 25, 2010 - 12:56 pm UTC
August 26, 2010 - 12:54 pm UTC
it is a bug, I don't know who they talked to - but it is a bug, it is clearly a bug - a first year sql programmer should be able to see that :)
Bug?
Duke Ganote, August 25, 2010 - 12:58 pm UTC
Curious indeed. I tried it in 10gR2 and 11gR2:
select * from v$version;
BANNER
--------------------------------------------------------------------------------
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production
with
t1 as ( select 1 as num from dual )
,
t2 as ( select 1 as num from dual )
, t as (select num as col from t1
union all
select num from t1
union all
select num from t1)
select num from t2
where 1 >= any (select col from t);
NUM
-----
1
select * from v$version;
BANNER
-----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
with
t1 as ( select 1 as num from dual )
,
t2 as ( select 1 as num from dual )
, t as (select num as col from t1
union all
select num from t1
union all
select num from t1)
select num from t2
where 1 >= any (select col from t)
NUM
-----
1
1
1
Duke Ganote, August 25, 2010 - 3:08 pm UTC
Curious, indeed. The test query:
with
"union all" as (
select 1 as col from dual union all
select 1 as col from dual union all
select 1 as col from dual
)
,
"connect by" AS (
select 1 as col from dual
connect by level <= 3
)
select count(*), 'union all' as src
from dual
where 1 >= any
(select col from "union all")
UNION ALL
select count(*), 'connect by'
from dual
where 1 >= any
(select col from "connect by")
union all
select col, 'union all source'
from "union all"
union all
select col, 'connect by source'
from "connect by"
The results in 11gR2:
COUNT(*) SRC
-------- -----------------
3 union all
3 connect by
1 union all source
1 union all source
1 union all source
1 connect by source
1 connect by source
1 connect by source
and in 10gR2:
COUNT(*) SRC
-------- -----------------
1 union all
3 connect by
1 union all source
1 union all source
1 union all source
1 connect by source
1 connect by source
1 connect by source
lovely bug
Sokrates, August 26, 2010 - 2:00 am UTC
Bug!
Michel Cadot, August 26, 2010 - 2:04 am UTC
My title was deliberately provocative, it is obvious it is a bug.
How a restriction could return more rows than the source set?
It should be like to screw on to get more water/gas than the tank can contain.
Regards
Michel
electric bugaloo
Duke Ganote, August 26, 2010 - 9:33 am UTC
I have 9iR2 available too, and of course the results are different from either 10gR2 or 11gR2:
COUNT(*) SRC
-------- -----------------
1 union all -- matches 10gR2, differs from 11gR2
1 connect by -- different than 10gR2 and 11gR2
1 union all source
1 union all source
1 union all source
1 connect by source
1 connect by source
1 connect by source
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
for the prior query:
with
"union all" as (
select 1 as col from dual union all
select 1 as col from dual union all
select 1 as col from dual
)
,
"connect by" AS (
select 1 as col from dual
connect by level <= 3
)
select count(*), 'union all' as src
from dual
where 1 >= any
(select col from "union all")
UNION ALL
select count(*), 'connect by'
from dual
where 1 >= any
(select col from "connect by")
UNION ALL
select col, 'union all source'
from "union all"
UNION ALL
select col, 'connect by source'
from "connect by"
bug -> SR
Duke Ganote, August 26, 2010 - 3:01 pm UTC
Service Request #3-2056934461
SR workaround
Duke Ganote, September 15, 2010 - 7:08 am UTC
From metalink:
<quote>
The observation remains same on 11.2 and 11.1. And the output is wrong on 11g as well as 10g.
As an workaround, please use the below parameter.
sql> alter session set optimizer_features_enable='9.2.0';
</quote>
Followed up by an email asking me "is the workaround acceptable ?"
I hardly know what to say.
September 15, 2010 - 8:21 am UTC
have you asked them for the P2 bug # that they filed for this error - if they haven't tell them to or let them know that I will (and be annoyed at them for not filing a bug for something so obvious)
Not Subquery Factoring
Jonathan Lewis, September 16, 2010 - 2:28 pm UTC
P2
Duke Ganote, February 17, 2011 - 7:02 am UTC
Oracle Support - February 17, 2011 7:46:01 AM GMT-05:00 [Notes]
Generic Note
------------------------
Hi,
I am trying to create a testcase and shall update you soon.
DGANOTE@XXXX.COM - September 15, 2010 11:24:08 AM GMT-05:00 [Update from Customer]
Really, this is an unacceptable workaround. What is the P2 bug # that you filed for this error ??
February 17, 2011 - 11:29 am UTC
huh? they *have* a small test case
P-tooey
Duke Ganote, April 26, 2011 - 3:40 am UTC
Oracle Support - April 18, 2011 12:19:55 PM GMT-04:00 [Notes]
Generic Note
------------------------
Hi Duke,
I have filed a bug 12371236 for this issue.
I shall update you as soon as lI have more information from dev.
Oracle Support - April 19, 2011 12:42:26 PM GMT-04:00 [Notes]
Generic Note
------------------------
Hi Duke,
I got the below update from bde.
<<
Please get customer to apply latest patchset in windows and this issue should
go away.
>>
Please apply latest patchset (11.2.0.2) where the issue is resolved.
Why this scalar query gives ora-904?
wm_shi, September 14, 2011 - 1:49 am UTC
SELECT (SELECT *
FROM (SELECT dummy
FROM dual
WHERE a.owner = 'x'))
FROM all_objects a;
September 14, 2011 - 7:17 pm UTC
because it is not a scalar subquery.
It is an inline view.
and scoping rules say "that goes one level down only"
Differences with FGA of factored subqueries should be mentioned
Peter Maklary, September 22, 2011 - 3:18 am UTC
Hi,
in addition to the performance aspects of using with clause (subquery factoring), a very interesting issue with FGA (Fine Grained Auditing) should be mentioned:
a select like
with empx as (select * from scott.emp) select * from empx
is NOT audited by FGA although an auditing policy for scott.emp is enabled, and select * from scott.emp is audited
An inline view version of this query, e.g.
select * from (select * from scott.emp)
is audited as expected
September 22, 2011 - 5:40 pm UTC
I am evaluating this....
WATCH OUT - some SQL clients don't like 'WITH'
Kiwi Nick, November 16, 2011 - 2:04 am UTC
I had been attempting a WITH ... SELECT statement and getting zero rows from all the queries.
I was using PL/SQL Developer 7.1.5.1398 on Windows 7 connected to Oracle 10.2.0.1.0 (Linux) or alternatively Oracle 10.2.0.3.0 (Tru64).
When I swapped to using the SQL*Plus on the Tru64 box, the WITH ... SELECT statement worked as expected.
Example query
with aa_ale as
(
select * from all_objects o
where o.owner = 'SYS'
and o.object_name between 'AA' and 'ALL'
)
select owner, object_name, object_id, object_type from aa_ale;
Sample result (the names of objects alphabetically between 'A' and 'ALL')
OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE
------- ------------- ---------- -------------
SYS ACCESS$ 93 TABLE
SYS ADMIN_DIR 46164 DIRECTORY
SYS AGGXMLIMP 5930 TYPE
SYS AGGXQIMP 5931 TYPE
SYS ALERT_QT 8786 TABLE
SYS ALERT_TYPE 8784 TYPE
but in PL/SQL Developer I was getting no rows. Just another thing to watch out for.
Some SQL clients don't like 'WITH' - WORKAROUND
Kiwi Nick, November 16, 2011 - 5:56 pm UTC
Ok, this works in PL/SQL Developer.
select * from
(
with aa_ale as
(
select * from all_objects o
where o.owner = 'SYS'
and o.object_name between 'AA' and 'ALL'
)
select owner, object_name, object_id, object_type from aa_ale
)Basically, I turned the query into a sub-query wrapped in
SELECT * FROM (...)It might also help with any other strangeness (such as experienced by "A Reader" at February 28, 2003 - 9am).
A reader, June 25, 2012 - 1:02 am UTC
Followup November 23, 2009 - 8am Central time zone: filed bug 9147631
Spur, February 21, 2013 - 9:31 pm UTC
Tom - Do you know if the bug 9147631 that you filed earlier in this thread (November 23, 2009 - 8am Central time zone) is resolved.
I am still seeing the same issue in Oracle 11.2.0.3. I could not find the bug in support.oracle.com
February 25, 2013 - 10:38 am UTC
it is still open (i filed it very low priority). You may contact support and "take it over" - which can result in a higher priority.
Which type of recursive WITH is not supported
Asim, October 03, 2022 - 3:05 pm UTC
October 04, 2022 - 9:58 am UTC
There are a few possible ways to read that statement:
You can't recursively place WITH clauses inside each other:
with subq1 as (
with subq2 as (
select * from dual
)
select * from subq2;
)
select * from subq1;
ORA-32034: unsupported use of WITH clauseAs far as I know, no other databases support this.
You can use WITH to write recursive queries, but the RECURSIVE keyword is unsupported in Oracle Database:
with recursive tree as ( ... )
This keyword is part of the SQL standard an other databases do support this.
I agree this could be clearer; I'll follow up with the doc team.
Bug 9147631
Asim, October 03, 2022 - 5:38 pm UTC
The Bug 9147631, initiated in year 2009 is still not resolved.
Its year 2022, and I have checked the UPDATE
statement on livesql version 19c
UPDATE scott.emp e
SET (e.sal,e.comm) = ( WITH q1 AS (
SELECT e2.empno,e2.sal,e2.deptno
FROM scott.emp e2),
q2 AS (
SELECT d.deptno from scott.dept d)
SELECT q1.sal + 100 sal, 100 comm
FROM q1,q2
where q1.deptno=q2.deptno
AND e.empno=q1.empno)
Still the same error
ORA-01767: UPDATE ... Set expression must be a subquery
Its strange that Oracles has not fixed this bug till now.
October 06, 2022 - 4:23 am UTC
Bugs are prioritised by customer need.
This is why we always tell *customers* to file SRs, and if necessary, reference bug numbers that *we* (Oracle employees) might have logged.
If a customer has never logged an issue with this syntax, it will be prioritised lower (and correctly so) than any customer logged issue.
Nested with
Asim, October 04, 2022 - 5:44 pm UTC
Instead of Recursive WITH, isnt the term "NEsted With" , or "Nesting WITH" more appropriate for thess types of queries.
with subq1 as (
with subq2 as (
select * from dual
)
select * from subq2;
)
select * from subq1;
ORA-32034: unsupported use of WITH clause
October 06, 2022 - 4:20 am UTC
agreed