Never rely on an assumed order

We’ve (hopefully) all had it drummed into us enough times – you cannot assume the order of results from queries unless you explicitly include an ORDER BY statement.

Here’s another trivial example of this- I was doing a little demo script for an AskTom question.

Here’s the script running in 11.2.0.4


SQL> create table T
  2  as
  3  select
  4    rownum c1,
  5    mod(rownum,10) c2,
  6    trunc(rownum/1000) c3,
  7    mod(rownum,100) c4,
  8    trunc(rownum/100) c5,
  9    mod(rownum,1000) c6,
 10    trunc(rownum/10) c7
 11  from dual
 12  connect by level <= 100;

Table created.

SQL>
SQL> begin
  2  for i in 1 .. 7 loop
  3    execute immediate 'create bitmap index IX'||i||' on T ( c'||i||')';
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> select
  2    index_name,
  3    leaf_blocks,
  4    avg_leaf_blocks_per_key,
  5    avg_data_blocks_per_key
  6  from user_indexes
  7  where table_name = 'T';

INDEX_NAME                     LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
------------------------------ ----------- ----------------------- -----------------------
IX1                                      1                       1                       1
IX2                                      1                       1                       1
IX3                                      1                       1                       1
IX4                                      1                       1                       1
IX5                                      1                       1                       1
IX6                                      1                       1                       1
IX7                                      1                       1                       1

And here’s the same script running in 12.1.0.2


SQL> create table T
  2  as
  3  select
  4    rownum c1,
  5    mod(rownum,10) c2,
  6    trunc(rownum/1000) c3,
  7    mod(rownum,100) c4,
  8    trunc(rownum/100) c5,
  9    mod(rownum,1000) c6,
 10    trunc(rownum/10) c7
 11  from dual
 12  connect by level <= 100;

Table created.

SQL>
SQL> begin
  2  for i in 1 .. 7 loop
  3    execute immediate 'create bitmap index IX'||i||' on T ( c'||i||')';
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> select
  2    index_name,
  3    leaf_blocks,
  4    avg_leaf_blocks_per_key,
  5    avg_data_blocks_per_key
  6  from user_indexes
  7  where table_name = 'T';

INDEX_NAME                     LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
------------------------------ ----------- ----------------------- -----------------------
IX7                                      1                       1                       1
IX6                                      1                       1                       1
IX5                                      1                       1                       1
IX4                                      1                       1                       1
IX3                                      1                       1                       1
IX2                                      1                       1                       1
IX1                                      1                       1                       1

2 Comments on “Never rely on an assumed order

  1. Does selecting from an inner query which has an order by guarantee the final output to be sorted by the column in inner query?….
    Ex-
    Select * from ( select * from t order by n);

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: