Index scan gives the sorted record.
SQL> column plan_plus_exp format a100
SQL> set trimspool on
SQL> create table t nologging as select * from dba_objects;
Table created.
SQL> set time on
SQL> set timing on
SQL> create index t_idx on t(owner,object_id) nologging compute statistics;
Index created.
Elapsed: 00:00:00.03
SQL> select count(*) from t;
COUNT(*)
----------
29515
Elapsed: 00:00:00.00
SQL Query 1:
SQL> set autotrace on
18:07:31 SQL> select rownum' 'owner' 'object_id from t where owner='SYS' and rownum < 11;
optimizer="CHOOSE" cost="5" card="10" bytes="18581)" cost="5" card="1093" bytes="18581)"
SQL Query 2:
SQL> select * from (select rownum' 'owner' 'object_id from t
2 where owner='SYS'
3 order by owner,object_id ) where rownum < 11;
optimizer="CHOOSE" cost="5" card="10" bytes="63394)" cost="5" card="1093" bytes="63394)" cost="5" card="1093" bytes="18581)"
The first SQL and second SQL are also giving the same result with same statistics. First SQL may not work as expected in future if I drop index obviously it will not.
But RULES OF THE GAME:
Rule #1: if you want, expect, or need sorted data, there is exactly one way to achieve that in a relational database.
"You must use order by"
Rule #2: please re-read #1 until you believe it.
The first query says "give me a random set of 10 rows for the SYS owner"
The second says "give me 10 rows for the sys owner starting at the smallest object_id in sorted order"
Wednesday, February 3, 2010
Index scan gives the sorted record ?
Subscribe to:
Post Comments (Atom)
0 comments:
Post a Comment