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

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);

Yes, because that is a special optimization we used for sorted subsets