For the result of an SQL query to be ordered, the only valid clause is ORDER BY.
Oracle only guarantees the ordered return of rows when ORDER BY is set as the sort criteria.
I have repeated these two phrases like a mantra thousands of times.
Any other way to get the sorted data is:
- A casuality
- An illusion.
- A temporary circumstance.
- All of the above.
The examples that I will show below, of ordered results without an ORDER BY clause in the SELECT command, are the product of his imagination…
MYTH #1 – Whether the rows were inserted in a certain order.
SQL> create table objetos as select object_id, object_name, object_type 2 from dba_objects order by object_id; Table created. SQL> select * from objetos where rownum<10; OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- -------------------- ------------------- 2 C_OBJ# CLUSTER 3 I_OBJ# INDEX 4 TAB$ TABLE 5 CLU$ TABLE 6 C_TS# CLUSTER 7 I_TS# INDEX 8 C_FILE#_BLOCK# CLUSTER 9 I_FILE#_BLOCK# INDEX 10 C_USER# CLUSTER 9 rows selected.
MYTH #2 – If the rows are being accessed by an index.
SQL> create table objetos_indice as select object_id, object_name, object_type 2 from dba_objects; Table created. SQL> create index idx_object_id on objetos_indice (object_id); Index created. SQL> select * from objetos_indice where object_id between 11256 and 11260; OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- -------------------- ------------------- 11256 ALL_APPLY SYNONYM 11257 DBA_APPLY_PARAMETERS VIEW 11258 DBA_APPLY_PARAMETERS SYNONYM 11259 ALL_APPLY_PARAMETERS VIEW 11260 ALL_APPLY_PARAMETERS SYNONYM
MYTH #3 – If the table is an IOT table.
SQL> create table objetos_iot 2 (object_id, object_name, object_type, 3 constraint pk_objetos_iot primary key (object_id)) 4 organization index 5 as select object_id, object_name, object_type 6 from dba_objects; Table created. SQL> select * from objetos_iot where object_id between 11256 and 11265; OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- ------------------------------ ------------------- 11256 ALL_APPLY SYNONYM 11257 DBA_APPLY_PARAMETERS VIEW 11258 DBA_APPLY_PARAMETERS SYNONYM 11259 ALL_APPLY_PARAMETERS VIEW 11260 ALL_APPLY_PARAMETERS SYNONYM 11261 _DBA_APPLY_SOURCE_SCHEMA VIEW 11262 _DBA_APPLY_SOURCE_OBJ VIEW 11263 DBA_APPLY_INSTANTIATED_OBJECTS VIEW 11264 DBA_APPLY_INSTANTIATED_OBJECTS SYNONYM 11265 ALL_APPLY_INSTANTIATED_OBJECTS VIEW 10 rows selected.
MYTH #4 – If the table has a cluster structure beneath.
SQL> create cluster clu_objetos (object_id number) tablespace test; Cluster created. SQL> create index idx_cluster_objetos on cluster clu_objetos; Index created. SQL> create table padre_objeto (id number constraint pk_objeto primary key) cluster clu_objetos(id); Table created. SQL> insert into padre_objeto values (1); 1 row created. SQL> insert into padre_objeto values (2); 1 row created. SQL> insert into padre_objeto values (3); 1 row created. SQL> insert into padre_objeto values (4); 1 row created. SQL> insert into padre_objeto values (5); 1 row created. SQL> insert into padre_objeto values (6); 1 row created. SQL> create table hijo_objeto (id number constraint fk_objeto_padre references padre_objeto(id)) cluster clu_objetos(id); Table created. SQL> insert into hijo_objeto values (3); 1 row created. SQL> insert into hijo_objeto values (6); 1 row created. SQL> insert into hijo_objeto values (5); 1 row created. SQL> insert into hijo_objeto values (2); 1 row created. SQL> select * from hijo_objeto; ID ---------- 2 3 5 6
MYTH #5 – If the statement uses DISTINCT.
SQL> create table test_orden (texto varchar2(50));
Table created.
SQL> insert into test_orden values ('PPPPP');
1 row created.
SQL> insert into test_orden values ('FFFFF');
1 row created.
SQL> insert into test_orden values ('ZZZZZ');
1 row created.
SQL> insert into test_orden values ('AAAAA');
1 row created.
SQL> select texto from test_orden;
TEXTO
--------------------------------------------------
PPPPP
FFFFF
ZZZZZ
AAAAA
SQL> select distinct texto from test_orden;
TEXTO
--------------------------------------------------
AAAAA
FFFFF
PPPPP
ZZZZZ
MYTH #6 – If the sentence uses GROUP BY clause.
SQL> select texto from test_orden; TEXTO -------------------------------------------------- PPPPP FFFFF ZZZZZ AAAAA SQL> select texto from test_orden group by texto; TEXTO -------------------------------------------------- AAAAA FFFFF PPPPP ZZZZZ
MYTH #7 – If the query is solved doing a MERGE JOIN.
SQL> create table test_orden2 as select * from test_orden; Table created. SQL> select /*+USE_MERGE(t,t2) */ t.texto 2 from test_orden t, test_orden2 t2 3 where t.texto=t2.texto; TEXTO -------------------------------------------------- AAAAA FFFFF PPPPP ZZZZZ
It is best to try to forget these examples.
Although they seem ordered results, at any moment they could not be. Perhaps there will be ordered spans, perhaps the entire subset of rows appears apparently ordered, or may even be.
They are myths, truths that perhaps once were true. Features (such as the traditional way of doing GROUP BY) that did order the rows, but in future implementations it was replaced by a HASH GROUP BY, in which alphabetical ordering was no longer necessary.
Perhaps a query stops getting the data through a certain index.
Everything is an illusion.
All these casual arrangements, without the use of the ORDER BY clause, are just the fruit of the imagination…