Home » SQL & PL/SQL » SQL & PL/SQL » How many rows are processed in this Query ? (Any)
How many rows are processed in this Query ? [message #435494] Wed, 16 December 2009 08:00 Go to next message
sudhir.sbt
Messages: 22
Registered: February 2009
Location: San Jose
Junior Member

Hello Gurus,

I have one question here. Below is the query to get the nth salary from the employees table.

SELECT DISTINCT (a.salary)
FROM Employees A
WHERE &n = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);

Can you please tell for any input to the query, does it processes all the records?

For e.g.
When the query is written like below

SELECT DISTINCT (a.salary)
FROM EMPloyees A
WHERE 3 = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);

Then do all the records in the tables get processed? OR

The query start processing and when the correlated subquery returns 3 that at that point it will finish processin and not proces other records. Or will it process all the rows and then return 3.
Re: How many rows are processed in this Query ? [message #435498 is a reply to message #435494] Wed, 16 December 2009 08:04 Go to previous messageGo to next message
sudhir.sbt
Messages: 22
Registered: February 2009
Location: San Jose
Junior Member

please see the attached file for sample data in the employees table.

~Sudhir
Re: How many rows are processed in this Query ? [message #435499 is a reply to message #435494] Wed, 16 December 2009 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use EXPLAIN PLAN and you will see.

Regards
Michel
Re: How many rows are processed in this Query ? [message #435500 is a reply to message #435494] Wed, 16 December 2009 08:07 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
First off, when posting code can you please use code tags - see the orafaq forum guide if you're not sure how.

An explain plan would tell you if it's processing all rows or not. However that's not the standard way of doing a top-n query in oracle, this is:

SELECT salary
FROM (SELECT salary
      FROM emp
      ORDER BY salary DESC)
WHERE rownum = 3;


EDIT: typo

[Updated on: Wed, 16 December 2009 08:08]

Report message to a moderator

Re: How many rows are processed in this Query ? [message #435507 is a reply to message #435500] Wed, 16 December 2009 08:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> SELECT sal
  2  FROM (SELECT sal
  3         FROM emp
  4         ORDER BY sal DESC)
  5  WHERE rownum = 3;

no rows selected

Something is missing in your code. There is no rownum 3 if you do not select rownum 1 and 2.
Maybe you meant:
SQL> select sal 
  2  from (
  3  SELECT sal, rownum rn
  4  FROM (SELECT sal
  5         FROM emp
  6         ORDER BY sal DESC)
  7  ) WHERE rn = 3;
       SAL
----------
      3000

1 row selected.

But this is not the correct answer when you have several rows with same salary (in standard EMP table the third salary is 2950).

regards
Michel

[Updated on: Wed, 16 December 2009 08:26]

Report message to a moderator

Re: How many rows are processed in this Query ? [message #435513 is a reply to message #435494] Wed, 16 December 2009 08:55 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
Yeah I meant the 2nd example, need coffee obviously.
I wasn't concerning myself with duplicates since I was only pointing out that the OPs approach wasn't the standard format for top-n in oracle.
Re: How many rows are processed in this Query ? [message #435547 is a reply to message #435499] Wed, 16 December 2009 12:12 Go to previous messageGo to next message
sudhir.sbt
Messages: 22
Registered: February 2009
Location: San Jose
Junior Member

Michel Cadot wrote on Wed, 16 December 2009 19:36
Use EXPLAIN PLAN and you will see.

Regards
Michel


thanks for the reply, but in explian plan too how can we find it.?

Is it through the "optimizer" column value ALL_ROWS or ... ?

Re: How many rows are processed in this Query ? [message #435549 is a reply to message #435547] Wed, 16 December 2009 12:18 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Performance Tuning Guide
Chapter 19 Using EXPLAIN PLAN

Regards
Michel
Previous Topic: Compare output files
Next Topic: Formatting excel generated from PL/SQL
Goto Forum:
  


Current Time: Wed Dec 07 14:46:37 CST 2016

Total time taken to generate the page: 0.11148 seconds