Help - Search - Members - Calendar
Full Version: query comparison
Oracle DBA Forums > Oracle > Oracle Forum
vinodjoshi
hi to all masters
please go through both the given queries...

SELECT * FROM EMP A WHERE 1=(SELECT COUNT(DISTINCT(SAL)) FROM EMP B WHERE B.SAL>A.SAL);
SELECT * FROM EMP WHERE SAL=(SELECT MIN(SAL) FROM (SELECT DISTINCT(SAL) FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<=2);

both queries will fetch second highest sal from emp table.
i would like to know that which one is better and why please give reason.
my other question is: which sorting is used by oracle in order by and group by clause.
thank you
burleson
Hi Vinod,

<< i would like to know that which one is better and why please give reason.

Great question, Vinod!

What is your dfeinition iof "Better"?

- The one that runs fastest?

- The one with the most throughput?

- The one that uses the least computing resources?

Please post the execution plans and let's take a look and see.

The second query concerns me because it is using ROWNUM and that may not guarantee the 2nd row every tine you runn the 2nd row query. See these tips on finding the first N rows in a table:

http://www.dba-oracle.com/t_top_n_sql.htm

***************************************
>> which sorting is used by oracle in order by and group by clause

We need to see the execution plan to find out!

Also set timing on and run the queries to see which one return the 2nd highest value the fastest.

Follow these easy steps to use autotrace:

http://www.dba-oracle.com/t_OracleAutotrace.htm

To learn a full procedure for ptimizing SQL, try the code downlkad in my book "Oracle Tuning: The Definitive Reference":

http://rampant-books.com/book_1002_oracle_...ence_2nd_ed.htm

CODE
set autotrace on;
select . . . .



http://www.dba-oracle.com/t_2nd_highest_row_value_sql.htm
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.