To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
In relational database theory, there is no concept to the order of rows or columns in any table. A table is a "set" of data and there is no order in a set. As such, there is no ANSI or ISO SQL standard which defines an ordering of columns or rows. So RDBMS vendors are left to implement these sorts of things however they want. Oracle likes to put the newly added column "last". This is just the way Oracle decided to handle adding a new column.
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.
|
