ORACLE SQL – ALTER TABLE ORDER BY

I have a table with two columns “COL_A” and “COL_B” which I create by inserting one row at a time:

COL_A       COL_B
20200330    49
20200413    31
20200420    19
20200511    99
20190318    73
20190401    86

I would like at the end of the program to have the table ordered by COL_A. I thought this coud be possible by running an ALTER TABLE command after I filled the table so I tried with:

PROC SQL;
     CONNECT TO ORACLE (USER=XXX ORAPW=XXX PATH=XXX);
          EXECUTE (
                ALTER TABLE SCHEMA.TABLE 
                ORDER BY COL_A

    ) BY ORACLE;
    DISCONNECT FROM ORACLE;
QUIT;

But I get

ERROR: ORACLE execute error: ORA-01735: opción ALTER TABLE no válida.

Of course I could create another table with the data of this original table ordered by COL_A, but I would like to know if I can do it in place.

I am using ORACLE SQL in SAS.

Answer

In SQL, tables represent unordered sets. You can only order the table when you are querying the data:

select t.*
from schema.table t
order by t.col_a;

If you are concerned about performance, you can add an index on (col_a) — so the index is used instead of actually sorting.

Note: The lack of ordering is also true of views. If you want an ordered result set, you need to use order by in the query.

Advertisement

Answer