Wednesday, February 3, 2010

Index scan gives the sorted record ?


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"


0 comments:

Post a Comment