Can you kindly explain me, if there is the difference between these two pieces of code;
When I need 5 last hired employees
In sql*plus I use following select statement:
SELECT ename
FROM (SELECT * FROM emp
ORDER BY hiredate DESC)
WHERE ROWNUM <= 5
In Coldfusion :
<cfquery name="query_name" datasource="source_name" maxrows=5 dbtype="ODBC">
Select ename
From emp
order by hiredate desc
</cfquery>
The result is the same. What select statement goes to Oracle? On what level is it changed?
Thank you in advance.
I cannot answer for cold fusion but -- they are PROBABLY not using the infinitely more efficient ROWNUM trick. They are probably running the query and fetching the first five rows.
You can verify that via SQL_TRACE and TKPROF. See
</code>
http://asktom.oracle.com/~tkyte/tkprof.html <code>
Here is a quick and dirty example. big_table is about 1,000,000 rows. I query:
big_table@ORA920.US.ORACLE.COM> alter session set sql_trace=true;
big_table@ORA920.US.ORACLE.COM> begin
2 for x in ( select *
3 from (select * from big_table order by object_name)
4 where rownum <= 5 )
5 loop
6 null;
7 end loop;
8 end;
9 /
big_table@ORA920.US.ORACLE.COM>
big_table@ORA920.US.ORACLE.COM> declare
2 l_cnt number default 0;
3 begin
4 for x in ( select * from big_table order by object_name)
5 loop
6 l_cnt := l_cnt+1;
7 exit when (l_cnt = 5);
8 end loop;
9 end;
10 /
Now, tkprof shows me:
select *
from (select * from big_table order by object_name)
where rownum <= 5
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.08 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 6 2.32 3.60 13002 13021 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 2.33 3.68 13002 13021 0 5
Rows Row Source Operation
------- ---------------------------------------------------
5 COUNT STOPKEY
5 VIEW
5 SORT ORDER BY STOPKEY
1000000 TABLE ACCESS FULL BIG_TABLE
********************************************************************************
select *
from
big_table order by object_name
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.06 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 5 7.68 25.42 29533 13021 565 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 7.68 25.49 29533 13021 565 5
Rows Row Source Operation
------- ---------------------------------------------------
5 SORT ORDER BY
1000000 TABLE ACCESS FULL BIG_TABLE
Basically - the where rownum <= 5 query KNEW "he only wants the top 5 rows". So, what it did was to set up a "array" of 5 slots if you will. It fetched the first 5 rows from big_table and put them sorted into this array.
Then, it continued fetching the rows from big_table and added them sorted into this 5 slot array. If it was to go into slot 6 -- the row was discarded. If it was to go into slot 2, slots 2, 3, 4 were bumped up one (to become 3, 4, 5) and row 5 that was there "disappeared".
So, the sort was easier. The amount of temp -- trivial (just 5 slots after all).
(this is conceptual ok -- that is "basically how this works")
The other query on the other hand -- read every row in big_table, sorted it with respect to every other row and then plopped it all out to temp. We read 5 rows and stopped. Tons more work -- lots more CPU and due to the increased IO we did (for temp writes) -- lots more elapsed time.
If you want to get maximum performance, you will use the database feature set as much as you can (and avoid tools that don't allow you to do so to boot!)