Home » RDBMS Server » Performance Tuning » rownum slowing query (11g)
rownum slowing query [message #392633] |
Wed, 18 March 2009 12:58 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
psk1
Messages: 30 Registered: August 2008 Location: Seattle, WA
|
Member |
|
|
Here's a puzzling situation I recently came across:
I have a query that runs very fast (.01 seconds):
SELECT a,b,c,d,e,f,g
FROM (
SELECT a,b,c,d,e,f,g
FROM table
WHERE b > some_func(:1)
ORDER BY b)
WHERE rownum=1
UNION ALL
SELECT a,b,c,d,e,f,g
FROM (
SELECT a,b,c,d,e,f,g
FROM table
WHERE (b = :1 AND c > some_func(:2))
ORDER BY c)
WHERE rownum=1
Its execution plan looks like this:
Plan hash value: 1170929204
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 202 | 856 (3)| 00:00:11 |
| 1 | UNION-ALL | | | | | |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 209K| 20M| 838 (1)| 00:00:11 |
| 4 | TABLE ACCESS BY INDEX ROWID| table | 209K| 9M| 838 (1)| 00:00:11 |
|* 5 | INDEX RANGE SCAN | table_INDEX_01 | 37746 | | 70 (0)| 00:00:01 |
|* 6 | COUNT STOPKEY | | | | | |
| 7 | VIEW | | 4369 | 430K| 18 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| table | 4369 | 209K| 18 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | table_INDEX_01 | 786 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=1)
5 - access("b">"some_func"(:1))
6 - filter(ROWNUM=1)
9 - access("b"=TO_NUMBER(:1) AND "c">"some_func"(:2))
Now... if I try and retrieve the first row from the previous query. The performance goes down the drain. The following
query takes anywhere from 1.5 seconds to 20 seconds.
SELECT * FROM (
SELECT a,b,c,d,e,f,g
FROM (
SELECT a,b,c,d,e,f,g
FROM table
WHERE b > some_func(:1)
ORDER BY b)
WHERE rownum=1
UNION ALL
SELECT a,b,c,d,e,f,g
FROM (
SELECT a,b,c,d,e,f,g
FROM table
WHERE (b = :1 AND c > some_func(:2))
ORDER BY c)
WHERE rownum=1
ORDER BY b,c,d,e,f,g)
WHERE rownum=1;
The execution plan for this one looks like this:
Plan hash value: 1915780033
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 101 | 858 (1)| 00:00:11 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 2 | 202 | 858 (1)| 00:00:11 |
|* 3 | SORT ORDER BY STOPKEY | | 2 | 202 | 857 (3)| 00:00:11 |
| 4 | UNION-ALL | | | | | |
|* 5 | COUNT STOPKEY | | | | | |
| 6 | VIEW | | 209K| 20M| 838 (1)| 00:00:11 |
| 7 | TABLE ACCESS BY INDEX ROWID | table | 209K| 9M| 838 (1)| 00:00:11 |
|* 8 | INDEX RANGE SCAN | table_INDEX_01 | 37746 | | 70 (0)| 00:00:01 |
|* 9 | COUNT STOPKEY | | | | | |
| 10 | VIEW | | 4369 | 430K| 19 (6)| 00:00:01 |
|* 11 | SORT ORDER BY STOPKEY | | 4369 | 209K| 19 (6)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID| table | 4369 | 209K| 18 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | table_INDEX_01 | 786 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
3 - filter(ROWNUM=1)
5 - filter(ROWNUM=1)
8 - access("b">"some_func"(:1))
9 - filter(ROWNUM=1)
11 - filter(ROWNUM=1)
13 - access("b"=TO_NUMBER(:1) AND "c">"some_func"(:2))
Can anybody explain this behavior? The first query (the fast one) will always return two or less rows. Why does
the performance plummet when trying to sort these two rows and return the top one (the second query)?
|
|
|
Re: rownum slowing query [message #392691 is a reply to message #392633] |
Wed, 18 March 2009 22:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
So, "ROWNUM slowing query", hey?
But you added TWO clauses to your original SQL:
- WHERE ROWNUM = 1
- ORDER BY b,c,d,e,f,g
Hint: it's not the ROWNUM that's slowing it down.
Ross Leishman
|
|
|
Re: rownum slowing query [message #392885 is a reply to message #392691] |
Thu, 19 March 2009 10:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
psk1
Messages: 30 Registered: August 2008 Location: Seattle, WA
|
Member |
|
|
You're absolutely right. Removing the order by clause yields very good performance (.01 seconds).
My question then is, why does it take so long to sort the two results from the union-ed subqueries?
|
|
|
|
Re: rownum slowing query [message #392896 is a reply to message #392886] |
Thu, 19 March 2009 10:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/129340.jpg) |
Alessandro Rossi
Messages: 166 Registered: September 2008 Location: Rome
|
Senior Member |
|
|
Quote: |
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 202 | 856 (3)| 00:00:11 |
| 1 | UNION-ALL | | | | | |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 209K| 20M| 838 (1)| 00:00:11 |
| 4 | TABLE ACCESS BY INDEX ROWID| table | 209K| 9M| 838 (1)| 00:00:11 |
|* 5 | INDEX RANGE SCAN | table_INDEX_01 | 37746 | | 70 (0)| 00:00:01 |
|* 6 | COUNT STOPKEY | | | | | |
| 7 | VIEW | | 4369 | 430K| 18 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| table | 4369 | 209K| 18 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | table_INDEX_01 | 786 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
|
If my supposition is right, in the first explain plan the optimizer doesn't sort the result of subqueries involved in union all because it scans the tables through index table_INDEX_01, that should be the index on b column, and then it just needs to merge previous results already ordered ( like with a single pass of a merge sort algorithm ).
In the second query it can't take the advantage of gathering subqueries results in the right order so it needs to sort all the rows, with all the needed time.
Probably an index on columns b,c,d,e,f,g may give to the second query the same performances as the first one.
Bye Alessandro
[Updated on: Thu, 19 March 2009 10:41] Report message to a moderator
|
|
|
|
Re: rownum slowing query [message #392925 is a reply to message #392912] |
Thu, 19 March 2009 12:03 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/129340.jpg) |
Alessandro Rossi
Messages: 166 Registered: September 2008 Location: Rome
|
Senior Member |
|
|
But I made two mistakes there.
I tested by myself that the optimizer is unable to evaluate those consideration about merging already ordered subqueries involved in union all, instead of executing a complete and much slower sort process.
But anyway with this one
SELECT * FROM (
SELECT a,b,c,d,e,f,g
FROM (
SELECT a,b,c,d,e,f,g
FROM table
WHERE b > some_func(:1)
or (b = :1 AND c > some_func(:2))
ORDER BY b,c,d,e,f,g)
WHERE rownum=1;
you'll have the same results much quicker.
Bye Alessandro
[Updated on: Thu, 19 March 2009 12:05] Report message to a moderator
|
|
|
Re: rownum slowing query [message #392928 is a reply to message #392633] |
Thu, 19 March 2009 12:16 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
psk1
Messages: 30 Registered: August 2008 Location: Seattle, WA
|
Member |
|
|
Alessandro,
This query too runs extremely slow.
Interesting that you should post this solution as it looks almost identical to the query I originally started with. My idea was that if I could explicitly tell Oracle how to execute this query, it would run it quicker. Therefore, I decided to break up the WHERE constraint into its individual constituents. This worked for most queries, but some (such as the one I originally posted), run painfully slow.
Also, I think you are missing a parenthesis from your query.
|
|
|
Re: rownum slowing query [message #393100 is a reply to message #392928] |
Fri, 20 March 2009 05:46 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/129340.jpg) |
Alessandro Rossi
Messages: 166 Registered: September 2008 Location: Rome
|
Senior Member |
|
|
Splitting a single query into two queries usually doubles the total execution time.
I wanted to make some test for you and here follows what happens when using a single query. I don't think it is identical to your original solution because other ways you were not going to change it in that way.
SQL> create table tab as
2 select trunc(dbms_random.value*100000) as a,
3 trunc(dbms_random.value*10000) as b,
4 trunc(dbms_random.value*10000) as c,
5 trunc(dbms_random.value*10000) as d,
6 trunc(dbms_random.value*10000) as e,
7 trunc(dbms_random.value*10000) as f,
8 trunc(dbms_random.value*10000) as g
9 from dual
10 connect by rownum <= 100000
11 /
Table created.
Elapsed: 00:00:02.18
SQL> create index tab_idx on tab (
2 b,c,d,e,f,g
3 )
4 /
Index created.
Elapsed: 00:00:00.28
SQL>
SQL> var v1 number
SQL> var v2 number
SQL>
SQL> begin
2 dbms_stats.gather_table_stats(user,'tab',cascade=>true);
3 :v1 := 944;
4 :v2 := 895;
5 end;
6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.62
SQL>
SQL>
SQL>
SQL> set autotrace on
SQL> set timing on
SQL> set linesize 32767
SQL> set pagesize 9999
SQL>
SQL> alter system flush buffer_cache
2 /
System altered.
Elapsed: 00:00:02.23
SQL>
SQL> SELECT *
2 FROM (
3 SELECT a,b,c,d,e,f,g
4 FROM (
5 SELECT a,b,c,d,e,f,g
6 FROM tab
7 WHERE b > :v1
8 or (b = :v1 AND c > :v2)
9 ORDER BY b,c,d,e,f,g
10 )
11 WHERE rownum=1
12 )
13 /
A B C D E F G
---------- ---------- ---------- ---------- ---------- ---------- ----------
32532 944 2133 38 7471 4075 3021
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 2610534696
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 91 | 5 (0)| 00:00:01 |
| 1 | VIEW | | 1 | 91 | 5 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 2 | 182 | 5 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TAB | 5001 | 136K| 5 (0)| 00:00:01 |
|* 5 | INDEX FULL SCAN | TAB_IDX | 2 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=1)
5 - filter("B">TO_NUMBER(:V1) OR "B"=TO_NUMBER(:V1) AND "C">TO_NUMBER(:V2))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
49 consistent gets
49 physical reads
0 redo size
635 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> alter system flush buffer_cache
2 /
System altered.
Elapsed: 00:00:00.12
SQL> SELECT * FROM (
2 SELECT a,b,c,d,e,f,g
3 FROM (
4 SELECT a,b,c,d,e,f,g
5 FROM tab
6 WHERE b > :v1
7 ORDER BY b)
8 WHERE rownum=1
9 UNION ALL
10 SELECT a,b,c,d,e,f,g
11 FROM (
12 SELECT a,b,c,d,e,f,g
13 FROM tab
14 WHERE (b = :v1 AND c > :v2)
15 ORDER BY c)
16 WHERE rownum=1
17 ORDER BY b,c,d,e,f,g)
18 WHERE rownum=1
19 /
A B C D E F G
---------- ---------- ---------- ---------- ---------- ---------- ----------
32532 944 2133 38 7471 4075 3021
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 1149497469
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 91 | 112 (2)| 00:00:02 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 2 | 182 | 112 (2)| 00:00:02 |
|* 3 | SORT ORDER BY STOPKEY | | 2 | 182 | 111 (4)| 00:00:02 |
| 4 | UNION-ALL | | | | | |
|* 5 | COUNT STOPKEY | | | | | |
| 6 | VIEW | | 5000 | 444K| 107 (0)| 00:00:02 |
| 7 | TABLE ACCESS BY INDEX ROWID | TAB | 5000 | 136K| 107 (0)| 00:00:02 |
|* 8 | INDEX RANGE SCAN | TAB_IDX | 900 | | 6 (0)| 00:00:01 |
|* 9 | COUNT STOPKEY | | | | | |
| 10 | VIEW | | 1 | 91 | 4 (25)| 00:00:01 |
|* 11 | SORT ORDER BY STOPKEY | | 1 | 28 | 4 (25)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID| TAB | 1 | 28 | 3 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | TAB_IDX | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
3 - filter(ROWNUM=1)
5 - filter(ROWNUM=1)
8 - access("B">TO_NUMBER(:V1) AND "B" IS NOT NULL)
9 - filter(ROWNUM=1)
11 - filter(ROWNUM=1)
13 - access("B"=TO_NUMBER(:V1) AND "C">TO_NUMBER(:V2) AND "C" IS NOT NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
485 consistent gets
471 physical reads
0 redo size
635 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> drop table tab purge
2 /
Table dropped.
Elapsed: 00:00:00.06
SQL>
As you can see the first explain plan very simple: an index scan for the first row with b column greather or equal than :v1 that satisfies the entire query predicate. A cost of 5 and with execution time of 3 seconds.
The seconds explain plan describes two table scans ( using index ) then two sorts with stopkey ( ...a minimum search ) and then one more final sort with stopkey. In my opinion it's very bad considering that the same result can be achieved with a much simpler query.
Quote: |
My idea was that if I could explicitly tell Oracle how to execute this query, it would run it quicker.
|
Your idea was to tell Oracle how to execute it slower. You wrote a SQL with two subqueries in place of one and that is why the database did it slowly.
If you still want to see how the execution time is spent by the database take Michel suggestion and trace your session for event 10046.
Bye Alessandro
[Updated on: Fri, 20 March 2009 05:53] Report message to a moderator
|
|
|
Re: rownum slowing query [message #393184 is a reply to message #393100] |
Fri, 20 March 2009 12:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
psk1
Messages: 30 Registered: August 2008 Location: Seattle, WA
|
Member |
|
|
The query I originally posted...
=====================
PARSING IN CURSOR #3 len=577 dep=0 uid=109 oct=3 lid=109 tim=3177397094716 hv=2596064571 ad='7ffd1d41458' sqlid='bmru2nudbtm9v'
SELECT * FROM (
SELECT a,b,c,d,e,f,g
FROM (
SELECT a,b,c,d,e,f,g;
FROM table
WHERE Rec_Type > some_func(:1)
ORDER BY b)
WHERE rownum=1
UNION ALL
SELECT a,b,c,d,e,f,g
FROM (
SELECT a,b,c,d,e,f,g
FROM table
WHERE (b = :1 AND c > :2)
ORDER BY c)
WHERE rownum=1
ORDER BY b,c,d,e,f,g) WHERE rownum=1
END OF STMT
PARSE #3:c=0,e=154,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=3177397094713
EXEC #3:c=0,e=190,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=3177397095047
WAIT #3: nam='SQL*Net message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=3177397095078
*** 2009-03-20 09:20:03.271
FETCH #3:c=2921875,e=2921439,p=0,cr=34032,cu=0,mis=0,r=1,dep=0,og=1,tim=3177400016559
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=34032 pr=0 pw=0 time=0 us)'
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=0 op='VIEW (cr=34032 pr=0 pw=0 time=0 us cost=22761 size=202 card=2)'
STAT #3 id=3 cnt=1 pid=2 pos=1 obj=0 op='SORT ORDER BY STOPKEY (cr=34032 pr=0 pw=0 time=0 us cost=22760 size=202 card=2)'
STAT #3 id=4 cnt=2 pid=3 pos=1 obj=0 op='UNION-ALL (cr=34032 pr=0 pw=0 time=2921316 us)'
STAT #3 id=5 cnt=1 pid=4 pos=1 obj=0 op='COUNT STOPKEY (cr=4 pr=0 pw=0 time=0 us)'
STAT #3 id=6 cnt=1 pid=5 pos=1 obj=0 op='VIEW (cr=4 pr=0 pw=0 time=0 us cost=808 size=21180003 card=209703)'
STAT #3 id=7 cnt=1 pid=6 pos=1 obj=87900 op='TABLE ACCESS BY INDEX ROWID table_INDEX (cr=4 pr=0 pw=0 time=0 us cost=808 size=10275447 card=209703)'
STAT #3 id=8 cnt=1 pid=7 pos=1 obj=90039 op='INDEX RANGE SCAN IDX_table_INDEX_01 (cr=3 pr=0 pw=0 time=0 us cost=73 size=0 card=37746)'
STAT #3 id=9 cnt=1 pid=4 pos=2 obj=0 op='COUNT STOPKEY (cr=34028 pr=0 pw=0 time=0 us)'
STAT #3 id=10 cnt=1 pid=9 pos=1 obj=0 op='VIEW (cr=34028 pr=0 pw=0 time=0 us cost=21952 size=114332707 card=1132007)'
STAT #3 id=11 cnt=1 pid=10 pos=1 obj=0 op='SORT ORDER BY STOPKEY (cr=34028 pr=0 pw=0 time=0 us cost=21952 size=55468343 card=1132007)'
STAT #3 id=12 cnt=1490503 pid=11 pos=1 obj=90043 op='INDEX FULL SCAN IDX_table_INDEX_05 (cr=34028 pr=0 pw=0 time=10567 us cost=8216 size=55468343 card=1132007)'
*** 2009-03-20 09:20:15.037
WAIT #3: nam='SQL*Net message from client' ela= 11763778 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=3177411780766
=====================
The query as modified by Alessandro:
PARSING IN CURSOR #3 len=316 dep=0 uid=109 oct=3 lid=109 tim=3178441274744 hv=3416727931 ad='7ffd19ffd18' sqlid='bd3mqtm5uf8bv'
SELECT a,b,c,d,e,f,g
FROM (
SELECT a,b,c,d,e,f,g
FROM table
WHERE a> some_func(:1) OR (b= :1 and c> :2)
ORDER BY a,b,c,d,e,f,g) where rownum=1
END OF STMT
PARSE #3:c=0,e=322,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=3178441274741
=====================
PARSING IN CURSOR #4 len=309 dep=1 uid=0 oct=3 lid=0 tim=3178441276900 hv=4216473919 ad='7ffe76f35f8' sqlid='2syvqzbxp4k9z'
select u.name, o.name, a.interface_version#, o.obj# from association$ a, user$ u, obj$ o where a.obj# = :1 and a.property = :2 and a.statstype# = o.obj# and u.user# = o.owner#
END OF STMT
PARSE #4:c=0,e=33,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=3178441276898
EXEC #4:c=0,e=79,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=3178441277075
FETCH #4:c=0,e=31,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=3178441277130
STAT #4 id=1 cnt=0 pid=0 pos=1 obj=0 op='NESTED LOOPS (cr=1 pr=0 pw=0 time=0 us)'
STAT #4 id=2 cnt=0 pid=1 pos=1 obj=0 op='NESTED LOOPS (cr=1 pr=0 pw=0 time=0 us cost=3 size=65 card=1)'
STAT #4 id=3 cnt=0 pid=2 pos=1 obj=0 op='NESTED LOOPS (cr=1 pr=0 pw=0 time=0 us cost=2 size=48 card=1)'
STAT #4 id=4 cnt=0 pid=3 pos=1 obj=426 op='TABLE ACCESS BY INDEX ROWID ASSOCIATION$ (cr=1 pr=0 pw=0 time=0 us cost=1 size=16 card=1)'
STAT #4 id=5 cnt=0 pid=4 pos=1 obj=427 op='INDEX RANGE SCAN ASSOC1 (cr=1 pr=0 pw=0 time=0 us cost=1 size=0 card=1)'
STAT #4 id=6 cnt=0 pid=3 pos=2 obj=37 op='INDEX FULL SCAN I_OBJ2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=32 card=1)'
STAT #4 id=7 cnt=0 pid=2 pos=2 obj=47 op='INDEX RANGE SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)'
STAT #4 id=8 cnt=0 pid=1 pos=2 obj=22 op='TABLE ACCESS BY INDEX ROWID USER$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=17 card=1)'
=====================
PARSING IN CURSOR #4 len=192 dep=1 uid=0 oct=3 lid=0 tim=3178441277372 hv=3136611056 ad='7ffe76f09b8' sqlid='2xyb5d6xg9srh'
select a.default_cpu_cost, a.default_io_cost from association$ a where a.obj# = :1 and a.property = :2
END OF STMT
PARSE #4:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=3178441277370
EXEC #4:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=3178441277451
FETCH #4:c=0,e=6,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=3178441277474
STAT #4 id=1 cnt=0 pid=0 pos=1 obj=426 op='TABLE ACCESS BY INDEX ROWID ASSOCIATION$ (cr=1 pr=0 pw=0 time=0 us cost=1 size=12 card=1)'
STAT #4 id=2 cnt=0 pid=1 pos=1 obj=427 op='INDEX RANGE SCAN ASSOC1 (cr=1 pr=0 pw=0 time=0 us cost=1 size=0 card=1)'
EXEC #3:c=0,e=4280,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,tim=3178441279174
WAIT #3: nam='SQL*Net message to client' ela= 3 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=3178441279220
WAIT #3: nam='db file sequential read' ela= 16473 file#=5 block#=1136806 blocks=1 obj#=90039 tim=3178441295917
WAIT #3: nam='db file sequential read' ela= 4974 file#=5 block#=1128805 blocks=1 obj#=90039 tim=3178441300992
WAIT #3: nam='db file sequential read' ela= 18941 file#=5 block#=599537 blocks=1 obj#=87900 tim=3178441320025
WAIT #3: nam='db file sequential read' ela= 5139 file#=5 block#=599521 blocks=1 obj#=87900 tim=3178441325513
WAIT #3: nam='db file sequential read' ela= 4099 file#=5 block#=599505 blocks=1 obj#=87900 tim=3178441329877
WAIT #3: nam='db file sequential read' ela= 5292 file#=5 block#=599489 blocks=1 obj#=87900 tim=3178441335388
WAIT #3: nam='db file sequential read' ela= 1020 file#=5 block#=1128806 blocks=1 obj#=90039 tim=3178441336607
WAIT #3: nam='db file sequential read' ela= 4140 file#=5 block#=599473 blocks=1 obj#=87900 tim=3178441340873
WAIT #3: nam='db file sequential read' ela= 4189 file#=5 block#=599457 blocks=1 obj#=87900 tim=3178441345256
WAIT #3: nam='db file sequential read' ela= 5421 file#=5 block#=599441 blocks=1 obj#=87900 tim=3178441350876
WAIT #3: nam='db file sequential read' ela= 934 file#=5 block#=1128807 blocks=1 obj#=90039 tim=3178441351988
WAIT #3: nam='db file sequential read' ela= 7691 file#=5 block#=599696 blocks=1 obj#=87900 tim=3178441359751
WAIT #3: nam='db file sequential read' ela= 850 file#=5 block#=599680 blocks=1 obj#=87900 tim=3178441360862
..
.
This continues for a long time...
..
..
WAIT #3: nam='db file sequential read' ela= 1164 file#=5 block#=540087 blocks=1 obj#=87900 tim=3178658516257
WAIT #3: nam='db file sequential read' ela= 1183 file#=5 block#=540071 blocks=1 obj#=87900 tim=3178658517746
WAIT #3: nam='db file sequential read' ela= 878 file#=5 block#=1153502 blocks=1 obj#=90039 tim=3178658518744
WAIT #3: nam='db file sequential read' ela= 1030 file#=5 block#=540055 blocks=1 obj#=87900 tim=3178658519977
FETCH #3:c=15828125,e=217240776,p=73397,cr=148353,cu=0,mis=0,r=1,dep=0,og=1,tim=3178658520042
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=148353 pr=73397 pw=73397 time=0 us)'
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=0 op='VIEW (cr=148353 pr=73397 pw=73397 time=0 us cost=1 size=101 card=1)'
STAT #3 id=3 cnt=1 pid=2 pos=1 obj=87900 op='TABLE ACCESS BY INDEX ROWID table_INDEX (cr=148353 pr=73397 pw=73397 time=0 us cost=1 size=80322074 card=1639226)'
STAT #3 id=4 cnt=2097040 pid=3 pos=1 obj=90039 op='INDEX FULL SCAN IDX_table_INDEX_01 (cr=16806 pr=16737 pw=16737 time=177399 us cost=1 size=0 card=3)'
|
|
|
|
Re: rownum slowing query [message #393245 is a reply to message #393205] |
Fri, 20 March 2009 16:22 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
@Alessandro, notice in your rewritten query that although it is avoiding the sort, it is performing a FULL index scan rather than a range scan.
The OP's problem looks like an optimizer bug to me. Each of the sub-queries TREATED SEPARATELY will use the index to sort, but when they are combined, filtered, and sorted, only one of them uses the index. I would consider raising an SR with Oracle.
Meanwhile, you could try tricking Oracle into running them separately.
WITH q1 AS (
SELECT /*+MATERIALIZE*/ a,b,c,d,e,f,g
FROM (
SELECT a,b,c,d,e,f,g
FROM table
WHERE b > some_func(:1)
ORDER BY b)
WHERE rownum=1
)
, q2 AS (
SELECT /*+MATERIALIZE*/ a,b,c,d,e,f,g
FROM (
SELECT a,b,c,d,e,f,g
FROM table
WHERE (b = :1 AND c > some_func(:2))
ORDER BY c)
WHERE rownum=1
)
SELECT * FROM (
SELECT * FROM q1
UNION ALL
SELECT * FROM q2
)
ORDER BY b,c,d,e,f,g
WHERE rownum=1;
Ross Leishman
|
|
|
|
Re: rownum slowing query [message #393250 is a reply to message #393245] |
Fri, 20 March 2009 16:51 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
psk1
Messages: 30 Registered: August 2008 Location: Seattle, WA
|
Member |
|
|
I should note also that with the /*+MATERIALIZE*/ hint, it runs significantly slower than without it.
original query : 2.80 seconds
With MATERIALIZE: .30 sec
Without MATERIALIZE: .01 sec
|
|
|
Re: rownum slowing query [message #393552 is a reply to message #393245] |
Mon, 23 March 2009 05:50 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/129340.jpg) |
Alessandro Rossi
Messages: 166 Registered: September 2008 Location: Rome
|
Senior Member |
|
|
Quote: |
notice in your rewritten query that although it is avoiding the sort, it is performing a FULL index scan rather than a range scan.
|
Oh yes I really missed it. But anyway an index range scan may be forced by an explicit access predicate for b column, but it isn't the best execution plan. What surprised me is that there is no way on a multiple columns index to begin a index range scan involving more than one column.
I made more test to find a way to drive the optimizer to choose an index range scan to look for values on (b,c) greater than (:v1,:v2) in order to make an optimal execution plan but there is no way to do it.
The only thing I've been able to find is that for a table with random or quite variant values for b and c columns a query with an explicit predicate on b performs really well, but if there is not enough variance for values on b column the range scan will loose more time on blocks with b=:v1 and c<=:v2 than the two splitted subqueries.
Here is a sample.
With constant b column
SQL> set autotrace on
SQL> set timing on
SQL> set linesize 32767
SQL> set pagesize 9999
SQL> def size=100000
SQL> whenever sqlerror exit rollback
SQL> create table tab as
2 select trunc(dbms_random.value*&size) as a,
3 1 as b,
4 --trunc(dbms_random.value*&size) as b,
5 rownum+1 as c,
6 --trunc(dbms_random.value*&size) as c,
7 trunc(dbms_random.value*&size) as d,
8 trunc(dbms_random.value*&size) as e,
9 trunc(dbms_random.value*&size) as f,
10 trunc(dbms_random.value*&size) as g
11 from dual
12 connect by rownum <= &size
13 /
old 2: select trunc(dbms_random.value*&size) as a,
new 2: select trunc(dbms_random.value*100000) as a,
old 4: --trunc(dbms_random.value*&size) as b,
new 4: --trunc(dbms_random.value*100000) as b,
old 6: --trunc(dbms_random.value*&size) as c,
new 6: --trunc(dbms_random.value*100000) as c,
old 7: trunc(dbms_random.value*&size) as d,
new 7: trunc(dbms_random.value*100000) as d,
old 8: trunc(dbms_random.value*&size) as e,
new 8: trunc(dbms_random.value*100000) as e,
old 9: trunc(dbms_random.value*&size) as f,
new 9: trunc(dbms_random.value*100000) as f,
old 10: trunc(dbms_random.value*&size) as g
new 10: trunc(dbms_random.value*100000) as g
old 12: connect by rownum <= &size
new 12: connect by rownum <= 100000
Table created.
Elapsed: 00:00:01.73
SQL>
SQL> create index tab_idx on tab (
2 b,c,d,e,f,g
3 )
4 /
Index created.
Elapsed: 00:00:00.25
SQL>
SQL> var v1 number
SQL> var v2 number
SQL>
SQL> begin
2 dbms_stats.gather_table_stats(user,'tab',cascade=>true);
3 :v1 := 1;
4 :v2 := &size;
5 end;
6 /
old 4: :v2 := &size;
new 4: :v2 := 100000;
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.31
SQL>
SQL> alter system flush buffer_cache
2 /
System altered.
Elapsed: 00:00:03.06
SQL>
SQL> SELECT *
2 FROM (
3 SELECT a,b,c,d,e,f,g
4 FROM (
5 SELECT a,b,c,d,e,f,g
6 FROM tab
7 WHERE b > :v1
8 or (b = :v1 AND c > :v2)
9 ORDER BY b,c,d,e,f,g
10 )
11 WHERE rownum=1
12 )
13 /
A B C D E F G
---------- ---------- ---------- ---------- ---------- ---------- ----------
14351 1 100001 94981 96465 50317 92919
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2610534696
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 91 | 3 (0)| 00:00:01 |
| 1 | VIEW | | 1 | 91 | 3 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 1 | 91 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TAB | 100K| 3125K| 3 (0)| 00:00:01 |
|* 5 | INDEX FULL SCAN | TAB_IDX | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=1)
5 - filter("B">TO_NUMBER(:V1) OR "B"=TO_NUMBER(:V1) AND "C">TO_NUMBER(:V2))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
3 physical reads
0 redo size
640 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> alter system flush buffer_cache
2 /
System altered.
Elapsed: 00:00:00.04
SQL>
SQL> SELECT * FROM (
2 SELECT a,b,c,d,e,f,g
3 FROM (
4 SELECT a,b,c,d,e,f,g
5 FROM tab
6 WHERE b > :v1
7 ORDER BY b
8 )
9 WHERE rownum=1
10 UNION ALL
11 SELECT a,b,c,d,e,f,g
12 FROM (
13 SELECT a,b,c,d,e,f,g
14 FROM tab
15 WHERE (b = :v1 AND c > :v2)
16 ORDER BY c
17 )
18 WHERE rownum=1
19 ORDER BY b,c,d,e,f,g
20 )
21 WHERE rownum=1
22 /
A B C D E F G
---------- ---------- ---------- ---------- ---------- ---------- ----------
14351 1 100001 94981 96465 50317 92919
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 4217420525
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 91 | | 1010 (1)| 00:00:13 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 2 | 182 | | 1010 (1)| 00:00:13 |
|* 3 | SORT ORDER BY STOPKEY | | 2 | 182 | | 1009 (2)| 00:00:13 |
| 4 | UNION-ALL | | | | | | |
|* 5 | COUNT STOPKEY | | | | | | |
| 6 | VIEW | | 100K| 8886K| | 997 (1)| 00:00:12 |
|* 7 | SORT ORDER BY STOPKEY | | 100K| 3125K| 9M| 997 (1)| 00:00:12 |
|* 8 | TABLE ACCESS FULL | TAB | 100K| 3125K| | 121 (2)| 00:00:02 |
|* 9 | COUNT STOPKEY | | | | | | |
| 10 | VIEW | | 5000 | 444K| | 12 (9)| 00:00:01 |
|* 11 | SORT ORDER BY STOPKEY | | 5000 | 156K| | 12 (9)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID| TAB | 5000 | 156K| | 11 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | TAB_IDX | 900 | | | 6 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
3 - filter(ROWNUM=1)
5 - filter(ROWNUM=1)
7 - filter(ROWNUM=1)
8 - filter("B">TO_NUMBER(:V1))
9 - filter(ROWNUM=1)
11 - filter(ROWNUM=1)
13 - access("B"=TO_NUMBER(:V1) AND "C">TO_NUMBER(:V2) AND "C" IS NOT NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
3 physical reads
0 redo size
640 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> alter system flush buffer_cache
2 /
System altered.
Elapsed: 00:00:00.04
SQL>
SQL> SELECT *
2 FROM (
3 SELECT a,b,c,d,e,f,g
4 FROM (
5 SELECT a,b,c,d,e,f,g
6 FROM tab
7 WHERE b >= :v1
8 and (
9 (b= :v1 and c > :v2)
10 or b > :v1
11 )
12 ORDER BY b,c,d,e,f,g
13 )
14 WHERE rownum=1
15 )
16 /
A B C D E F G
---------- ---------- ---------- ---------- ---------- ---------- ----------
14351 1 100001 94981 96465 50317 92919
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 1239423028
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 91 | 3 (0)| 00:00:01 |
| 1 | VIEW | | 1 | 91 | 3 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 1 | 91 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TAB | 1 | 32 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | TAB_IDX | | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=1)
5 - access("B">=TO_NUMBER(:V1) AND "B" IS NOT NULL)
filter("B">TO_NUMBER(:V1) OR "B"=TO_NUMBER(:V1) AND "C">TO_NUMBER(:V2))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
3 physical reads
0 redo size
640 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> drop table tab purge
2 /
Table dropped.
Elapsed: 00:00:00.03
SQL>
With random values on b column
SQL> set autotrace on
SQL> set timing on
SQL> set linesize 32767
SQL> set pagesize 9999
SQL> def size=100000
SQL> whenever sqlerror exit rollback
SQL> create table tab as
2 select trunc(dbms_random.value*&size) as a,
3 --1 as b,
4 trunc(dbms_random.value*&size) as b,
5 --rownum+1 as c,
6 trunc(dbms_random.value*&size) as c,
7 trunc(dbms_random.value*&size) as d,
8 trunc(dbms_random.value*&size) as e,
9 trunc(dbms_random.value*&size) as f,
10 trunc(dbms_random.value*&size) as g
11 from dual
12 connect by rownum <= &size
13 /
old 2: select trunc(dbms_random.value*&size) as a,
new 2: select trunc(dbms_random.value*100000) as a,
old 4: trunc(dbms_random.value*&size) as b,
new 4: trunc(dbms_random.value*100000) as b,
old 6: trunc(dbms_random.value*&size) as c,
new 6: trunc(dbms_random.value*100000) as c,
old 7: trunc(dbms_random.value*&size) as d,
new 7: trunc(dbms_random.value*100000) as d,
old 8: trunc(dbms_random.value*&size) as e,
new 8: trunc(dbms_random.value*100000) as e,
old 9: trunc(dbms_random.value*&size) as f,
new 9: trunc(dbms_random.value*100000) as f,
old 10: trunc(dbms_random.value*&size) as g
new 10: trunc(dbms_random.value*100000) as g
old 12: connect by rownum <= &size
new 12: connect by rownum <= 100000
Table created.
Elapsed: 00:00:02.20
SQL>
SQL> create index tab_idx on tab (
2 b,c,d,e,f,g
3 )
4 /
Index created.
Elapsed: 00:00:00.29
SQL>
SQL> var v1 number
SQL> var v2 number
SQL>
SQL> begin
2 dbms_stats.gather_table_stats(user,'tab',cascade=>true);
3 :v1 := 1;
4 :v2 := &size;
5 end;
6 /
old 4: :v2 := &size;
new 4: :v2 := 100000;
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.76
SQL>
SQL> alter system flush buffer_cache
2 /
System altered.
Elapsed: 00:00:00.15
SQL>
SQL> SELECT *
2 FROM (
3 SELECT a,b,c,d,e,f,g
4 FROM (
5 SELECT a,b,c,d,e,f,g
6 FROM tab
7 WHERE b > :v1
8 or (b = :v1 AND c > :v2)
9 ORDER BY b,c,d,e,f,g
10 )
11 WHERE rownum=1
12 )
13 /
A B C D E F G
---------- ---------- ---------- ---------- ---------- ---------- ----------
91281 3 46640 62170 39291 29847 98049
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 2610534696
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 91 | 5 (0)| 00:00:01 |
| 1 | VIEW | | 1 | 91 | 5 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 2 | 182 | 5 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TAB | 5000 | 166K| 5 (0)| 00:00:01 |
|* 5 | INDEX FULL SCAN | TAB_IDX | 2 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=1)
5 - filter("B">TO_NUMBER(:V1) OR "B"=TO_NUMBER(:V1) AND "C">TO_NUMBER(:V2))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
3 physical reads
0 redo size
640 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> alter system flush buffer_cache
2 /
System altered.
Elapsed: 00:00:00.03
SQL>
SQL> SELECT * FROM (
2 SELECT a,b,c,d,e,f,g
3 FROM (
4 SELECT a,b,c,d,e,f,g
5 FROM tab
6 WHERE b > :v1
7 ORDER BY b
8 )
9 WHERE rownum=1
10 UNION ALL
11 SELECT a,b,c,d,e,f,g
12 FROM (
13 SELECT a,b,c,d,e,f,g
14 FROM tab
15 WHERE (b = :v1 AND c > :v2)
16 ORDER BY c
17 )
18 WHERE rownum=1
19 ORDER BY b,c,d,e,f,g
20 )
21 WHERE rownum=1
22 /
A B C D E F G
---------- ---------- ---------- ---------- ---------- ---------- ----------
31977 3 73307 70652 7171 25629 85684
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 1149497469
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 91 | 129 (2)| 00:00:02 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 2 | 182 | 129 (2)| 00:00:02 |
|* 3 | SORT ORDER BY STOPKEY | | 2 | 182 | 128 (4)| 00:00:02 |
| 4 | UNION-ALL | | | | | |
|* 5 | COUNT STOPKEY | | | | | |
| 6 | VIEW | | 5000 | 444K| 124 (0)| 00:00:02 |
| 7 | TABLE ACCESS BY INDEX ROWID | TAB | 5000 | 166K| 124 (0)| 00:00:02 |
|* 8 | INDEX RANGE SCAN | TAB_IDX | 900 | | 7 (0)| 00:00:01 |
|* 9 | COUNT STOPKEY | | | | | |
| 10 | VIEW | | 1 | 91 | 4 (25)| 00:00:01 |
|* 11 | SORT ORDER BY STOPKEY | | 1 | 34 | 4 (25)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID| TAB | 1 | 34 | 3 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | TAB_IDX | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
3 - filter(ROWNUM=1)
5 - filter(ROWNUM=1)
8 - access("B">TO_NUMBER(:V1) AND "B" IS NOT NULL)
9 - filter(ROWNUM=1)
11 - filter(ROWNUM=1)
13 - access("B"=TO_NUMBER(:V1) AND "C">TO_NUMBER(:V2) AND "C" IS NOT NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
550 consistent gets
547 physical reads
0 redo size
639 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> alter system flush buffer_cache
2 /
System altered.
Elapsed: 00:00:00.17
SQL>
SQL> SELECT *
2 FROM (
3 SELECT a,b,c,d,e,f,g
4 FROM (
5 SELECT a,b,c,d,e,f,g
6 FROM tab
7 WHERE b >= :v1
8 and (
9 (b= :v1 and c > :v2)
10 or b > :v1
11 )
12 ORDER BY b,c,d,e,f,g
13 )
14 WHERE rownum=1
15 )
16 /
A B C D E F G
---------- ---------- ---------- ---------- ---------- ---------- ----------
91281 3 46640 62170 39291 29847 98049
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 1239423028
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 91 | 3 (0)| 00:00:01 |
| 1 | VIEW | | 1 | 91 | 3 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 2 | 182 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TAB | 2 | 68 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | TAB_IDX | 45 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=1)
5 - access("B">=TO_NUMBER(:V1) AND "B" IS NOT NULL)
filter("B">TO_NUMBER(:V1) OR "B"=TO_NUMBER(:V1) AND "C">TO_NUMBER(:V2))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
3 physical reads
0 redo size
640 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> drop table tab purge
2 /
Table dropped.
Elapsed: 00:00:00.06
SQL>
By the way sometime, like in the last posted case, the slitted subqueries don't return the excepted result. Probably it's just because the query looking for b > :v1 appears before than one looking for b=:v1 and c>:v2 and the order by clause is interpreted to sort the already ordered second subquery.
Bye Alessandro
|
|
|
Goto Forum:
Current Time: Fri Sep 06 18:33:19 CDT 2024
|