Home » RDBMS Server » Performance Tuning » rownum slowing query (11g)
rownum slowing query [message #392633] Wed, 18 March 2009 12:58 Go to next message
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)?

Report message to a moderator

Re: rownum slowing query [message #392691 is a reply to message #392633] Wed, 18 March 2009 22:54 Go to previous messageGo to next message
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

Report message to a moderator

Re: rownum slowing query [message #392885 is a reply to message #392691] Thu, 19 March 2009 10:02 Go to previous messageGo to next message
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?

Report message to a moderator

Re: rownum slowing query [message #392886 is a reply to message #392885] Thu, 19 March 2009 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Seems a run-time bug as your execution plan (explain plan one?) give the correct result.
Activate a 10046 trace and post the execution plan and statistics from it.

Regards
Michel

Report message to a moderator

Re: rownum slowing query [message #392896 is a reply to message #392886] Thu, 19 March 2009 10:38 Go to previous messageGo to next message
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 #392912 is a reply to message #392896] Thu, 19 March 2009 11:22 Go to previous messageGo to next message
psk1
Messages: 30
Registered: August 2008
Location: Seattle, WA
Member
Alessandro,

table_INDEX_01 is on columns b,c,d,e,f,g.

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 messageGo to next message
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 messageGo to next message
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.

Report message to a moderator

Re: rownum slowing query [message #393100 is a reply to message #392928] Fri, 20 March 2009 05:46 Go to previous messageGo to next message
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 messageGo to next message
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)'




Report message to a moderator

Re: rownum slowing query [message #393205 is a reply to message #393184] Fri, 20 March 2009 13:41 Go to previous messageGo to next message
psk1
Messages: 30
Registered: August 2008
Location: Seattle, WA
Member
There is another thread that is very similar to this one here:

http://www.orafaq.com/forum/t/121396/0/

I'm thinking of heading in the direction of using case statements to get rid of the unions.

Report message to a moderator

Re: rownum slowing query [message #393245 is a reply to message #393205] Fri, 20 March 2009 16:22 Go to previous messageGo to next message
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

Report message to a moderator

Re: rownum slowing query [message #393249 is a reply to message #393245] Fri, 20 March 2009 16:46 Go to previous messageGo to next message
psk1
Messages: 30
Registered: August 2008
Location: Seattle, WA
Member
Wow. Nicely done. This one runs very fast (.01 seconds) even without the /*+MATERIALIZE*/ hint.

Thank you.

Report message to a moderator

Re: rownum slowing query [message #393250 is a reply to message #393245] Fri, 20 March 2009 16:51 Go to previous messageGo to next message
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

Report message to a moderator

Re: rownum slowing query [message #393552 is a reply to message #393245] Mon, 23 March 2009 05:50 Go to previous message
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

Report message to a moderator

Previous Topic: query problem (merged 4)
Next Topic: optimisation of queries
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Jun 28 00:48:26 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.