Since column order is meaningless in relational database theory, it is always a good idea to explicitly specify the order you want columns returned in your SELECT statement. Instead of:
SELECT * FROM table;
One should always do:
SELECT column1, column2, … , columnN FROM table;
This way, you are explicitly stating the order the columns appear in the resultset. There are many reasons you want to explicitly state the columns in the order you want to see them in your application's SQL statements. Those reasons include, but are not limited to:
- A database reorg cannot guarantee the order of the columns will stay the same. Explicitly denoting the column order in the SELECT clause will guarantee that your application does not break.
- If someone adds a column to the table, the * in the SELECT clause can break some applications that won't know how to handle the new column. If you enumerate your columns in the SELECT clause, your application code will not break when a column is added to the table.
- While you may like the column order of the table (if it exists), some other part of the application may like the columns in another order. Explicitly enumerating those columns in the SELECT clause guarantees that your resultset returns columns in the order you require.
As far as I know, Oracle will not be implementing any feature to guarantee column order in their databases.
Dig Deeper on Oracle and SQL
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading