OraFAQ Forum: SQL & PL/SQL » Rownum inside 'Exists' subquery

Home » SQL & PL/SQL » SQL & PL/SQL » Rownum inside 'Exists' subquery

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Rownum inside 'Exists' subquery [message #205216]

Thu, 23 November 2006 23:07

vgs2005
Messages: 123
Registered: April 2005

Senior Member

Does the rownum here really help??

SELECT * 
  FROM table_A
 WHERE EXISTS (SELECT 1
                 FROM table_B
                WHERE table_A.col1 = table_B.col1
                  AND ROWNUM = 1);

I think EXISTS works in a way that after it finds a match, it stops searching.
Thanks in advance...

[Updated on: Thu, 23 November 2006 23:11]

Report message to a moderator

Re: Rownum inside 'Exists' subquery [message #205241 is a reply to message #205216]

Fri, 24 November 2006 00:53

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

You are right. Rownum does not help. EXISTS will short-circuit as soon as it finds the first row.
You can prove this for yourself by tracing a SQL with and without the ROWNUM clause and looking at the rows processed against each step of the plan in TK*Prof.

Ross Leishman

Report message to a moderator

Previous Topic:	Getting the UNIX file size from PLSQL
Next Topic:	Index Creation on Table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 02 08:51:16 CST 2024