FROM USER_TAB_COLUMNS

WHERE TABLE_NAME 'EMP'

Regards
Waseem

The column numbers are used so that you can do the order by without keying in all the column names in the order by clause.

For eg.

select empno ename from emp order by empno; -- qry 1

could be replaced by

select empno ename from emp order by 1; -- qry 2

If you wanted to change the order as may be order by ename asc that would have required doing...

select empno ename from emp order by ename; -- qry 3

instead of just changing the qry to ...

select empno ename from emp order by 2; -- qry 4

Just worry about the time spent to select from a table based on different orders of ascending and descending of different columns which have long names!!! If it is a one time activity we would go for column names but for our quick results from our query I guess it would be much quicker (to type) if we just enlist the column numbers (which we would only for our own frequent use such as in unit testing).

Also assuming the table (say log_table) has a column such as when (i.e. the timestamp) and you wanted only records for today but in desc order so that you know the progress of say some procedure which does some processing and in turn logs it in our table say log_table. In such a case the column position of timestamp column always remains the same unless the table is "altered" or "dropped & re-created". Assuming the log_table has a structure such as:

log_process varchar2(255)
when date
log_message varchar2(255)

Here the query to get today's records but all in descending order would be:

select * from log_table
where when > trunc(sysdate)
order by 2 desc; -- qry 5

Please note that the 2 above is nothing but the column_id value of user_tab_cols for the when column of log_table.

It could be confirmed by :

select column_id from user_tab_cols where table_name 'LOG_TABLE' and column_name 'WHEN';

There is a serious disadvantage with using column numbers in the order by clause.

Suppose the column when was dropped and may be just re-added to have the following new structure.

log_process varchar2(255)
log_message varchar2(255)
when date

And now if your qry still remains the above: i.e.

select * from log_table
where when > trunc(sysdate)
order by 2 desc; -- qry 5

Then though you would get only today's records from the log_table they would be ordered only by log_process thus giving you unexpected output.

So use it only if no one else would modify the selected columns selected expressions or the table column positions.

Ideally while small unit tests the column numbers are used and when the code is actually integrated with other pieces the column names are the best to use.

Hope it helps!

- Rahul Katke.