without using an ORDER BY, the data returned from a sorted hash cluster is no more sorted than the data returned by an IOT. Meaning - it ain't sorted without an ORDER BY.

It might accidentally sometimes come back sorted - but there is definitely NO ASSURANCE or even a little hint of a promise that the data will be ordered by anything.

Note that even in your own examples - you have an ORDER BY - the optimizer is free to remove the order by step - but you ARE NOT free to not use the order by if you want the data ordered by something!!!!!

EVER.

see the following comment by Duke - short answer is:

if you want data sorted, you better use order by - or it isn't guaranteed to be sorted.

because they added support for order by in a view in version 8.1.5.

I am not a fan of it personally. I myself would not rely on it - since if you use the view "in general", it doesn't have to be ordered. As soon as you join - all bets are off.

Proof:

ops$tkyte%ORA11GR2> create table t1 ( x int );

Table created.

ops$tkyte%ORA11GR2> insert into t1 values ( 1 );

1 row created.

ops$tkyte%ORA11GR2> insert into t1 values ( 2 );

1 row created.

ops$tkyte%ORA11GR2> insert into t1 values ( 3 );

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table t2 ( y int );

Table created.

ops$tkyte%ORA11GR2> insert into t2 values ( 1 );

1 row created.

ops$tkyte%ORA11GR2> insert into t2 values ( 2 );

1 row created.

ops$tkyte%ORA11GR2> insert into t2 values ( 3 );

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace view v1 as select * from t1 order by x;

View created.

ops$tkyte%ORA11GR2> create or replace view v2 as select * from t2 order by y DESC;

View created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace on explain
ops$tkyte%ORA11GR2> select * from v1, v2 where v1.x = v2.y;

         X          Y
---------- ----------
         3          3
         2          2
         1          1


Execution Plan
----------------------------------------------------------
Plan hash value: 2678552646

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     3 |    78 |     9  (34)| 00:00:01 |
|   1 |  SORT ORDER BY        |      |     3 |    78 |     9  (34)| 00:00:01 |
|*  2 |   HASH JOIN           |      |     3 |    78 |     8  (25)| 00:00:01 |
|   3 |    VIEW               | V1   |     3 |    39 |     4  (25)| 00:00:01 |
|   4 |     SORT ORDER BY     |      |     3 |    39 |     4  (25)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| T1   |     3 |    39 |     3   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL  | T2   |     3 |    39 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("V1"."X"="Y")

two views with competing order by's....