Skip to Main Content
  • Questions

  • A question on performance of query using rownum=1 in subquery

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Girish.

Asked: May 02, 2016 - 5:54 am UTC

Last updated: May 03, 2016 - 9:04 am UTC

Version: 11.2.0.4.0

Viewed 1000+ times

You Asked

Hi,

I have below question performance of query when rownum=1 in subquery

Input data

TAB2 7086016 rows
TAB1 217299 rows
TAB_VIEW 175050 row
V_VIEW is view joining tables TAB_VIEW and TAB( a very small table with few hundred rows)

QUERY
select * from TAB1
WHERE EXISTS
(SELECT 1
FROM TAB2
WHERE tab2.col1 = tab1.col1
AND rownum = 1;
AND EXISTS (SELECT 1 FROM V_VIEW WHERE V_VIEW.COL2 = TAB2.COL3 AND V_VIEW.col4 = 1110
)
)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3496 172.49 180.16 19453 13678243 0 52418
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3498 172.52 180.19 19453 13678243 0 52418

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
52418 52418 52418 FILTER (cr=13678243 pr=19453 pw=0 time=195281406 us)
175050 175050 175050 TABLE ACCESS FULL TAB1 (cr=9351 pr=0 pw=0 time=525281 us cost=1337 size=29058300 card=175050)
52418 52418 52418 COUNT STOPKEY (cr=13668892 pr=19453 pw=0 time=178867324 us)
52418 52418 52418 NESTED LOOPS SEMI (cr=13668892 pr=19453 pw=0 time=177562417 us cost=9 size=36 card=2)
6108765 6108765 6108765 INDEX RANGE SCAN PK_TAB2 (cr=515214 pr=19449 pw=0 time=16470678 us cost=3 size=32 card=2)(object id 157906)
52418 52418 52418 VIEW PUSHED PREDICATE VW_SQ_1 (cr=13153678 pr=4 pw=0 time=151320299 us cost=3 size=2 card=1)
52418 52418 52418 NESTED LOOPS (cr=13153678 pr=4 pw=0 time=117832264 us cost=3 size=19 card=1)
52418 52418 52418 TABLE ACCESS BY INDEX ROWID TAB_VIEW (cr=13101260 pr=3 pw=0 time=83556900 us cost=3 size=15 card=1)
6108765 6108765 6108765 INDEX UNIQUE SCAN PK_TAB_VIEW (cr=6992495 pr=3 pw=0 time=34622395 us cost=2 size=0 card=1)(object id 159405)
52418 52418 52418 INDEX UNIQUE SCAN PK_TAB (cr=52418 pr=1 pw=0 time=290035 us cost=0 size=4 card=1)(object id 157517)

When rownum=1 is removed performance improved to 2 secs

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3496 2.15 2.17 495 36880 0 52418
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3498 2.18 2.20 495 36880 0 52418

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
52418 52418 52418 HASH JOIN RIGHT SEMI (cr=36880 pr=495 pw=0 time=2149687 us cost=9232 size=31333950 card=175050)
70594 70594 70594 VIEW VW_SQ_1 (cr=27529 pr=495 pw=0 time=1161393 us cost=5993 size=4686240 card=360480)
70594 70594 70594 HASH JOIN (cr=27529 pr=495 pw=0 time=960213 us cost=5993 size=12616800 card=360480)
2947 2947 2947 VIEW index$_join$_006 (cr=746 pr=88 pw=0 time=144949 us cost=651 size=45465 card=3031)
2947 2947 2947 HASH JOIN (cr=746 pr=88 pw=0 time=136718 us)
2947 2947 2947 INDEX RANGE SCAN TAB_VIEW_I3 (cr=12 pr=0 pw=0 time=4096 us cost=12 size=45465 card=3031)(object id 159408)
217299 217299 217299 INDEX FAST FULL SCAN PK_TAB_VIEW (cr=734 pr=88 pw=0 time=359006 us cost=798 size=45465 card=3031)(object id 159405)
7086016 7086016 7086016 NESTED LOOPS (cr=26783 pr=407 pw=0 time=30184507 us cost=5323 size=141720320 card=7086016)
1 1 1 INDEX UNIQUE SCAN PK_TAB (cr=1 pr=0 pw=0 time=18 us cost=0 size=4 card=1)(object id 157517)
7086016 7086016 7086016 INDEX FAST FULL SCAN PK_TAB2 (cr=26782 pr=407 pw=0 time=10425299 us cost=5323 size=113376256 card=7086016)(object id 157906)
175050 175050 175050 TABLE ACCESS FULL TAB1 (cr=9351 pr=0 pw=0 time=348257 us cost=1337 size=29058300 card=175050)

My questions:

1) When we use rownum=1 in subquery will filter predicate push tables place

2) In general, what are implications on performance when rownum=1 used inside a subquery like the one shown in example query ?



and Chris said...

Please use code tags when posting execution plans! This preserves the original formatting. It's tricky to figure out how everything lines up without this.

Anyway, onto your questions:

1. Yes, push predicates can take place. You can see it's happened in your query from the following line:

VIEW PUSHED PREDICATE VW_SQ_1

2. Rownum prevents Oracle unnesting subqueries:

"The optimizer can unnest most subqueries, with some exceptions. Those exceptions include hierarchical subqueries and subqueries that contain a ROWNUM pseudocolumn, one of the set operators, a nested aggregate function, or a correlated reference to a query block that is not the immediate outer query block of the subquery"

Emphasis mine:

http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries008.htm#SQLRF52358

This can stop the optimizer rewriting your query in a better way. For more details on unnesting see:

https://blogs.oracle.com/optimizer/entry/optimizer_transformations_subquery_unesting_part_1

In general adding "rownum = 1" to exists subqueries is unnecessary. This restricts optimizer paths.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Girish Jahagirdar, May 03, 2016 - 9:23 am UTC

Thanks much appreciated
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.