This content has been marked as final. Show 6 replies
Haider Hussain wrote:the best suggestion is "don't bother modifying the table itself'.
I have a table (my_tab) having 2 columns (Col1 and Col 3). I wish to add a new column (Col2) between Col1 and Col3.
Two ways I could think are: 1. Create a view on the table having the column sequence as Col1,Col2,Col3
2. Just like point 1, introduce Col2 while writing theselect query for the table.
Could you please suggest some other methods of doing this , sayat table level itself.
As you already see, you can select the columns in any order you want. And if there is benefit, the query can be put in a view.
But there is no point at all in modifying the table itself.
Edited by: EdStevens on Dec 7, 2012 11:53 AM
column order is not important.
You can recreate the table(And build all indexes, constraints). Refer the below link how to do it using dbms_redefinition.
Why do you want to reorder the column as Ed stated select the query in the order you want.
Go through the below thread for more info.
column order is not important.Column order is not important in theory and most of practical cases like where are not many columns and not too many rows.
But sometimes it is important.
For example when there are many columns (>256) and row comprises of multiple row pieces (chaining). It is better to have frequently accessed columns in beginning of a row, first row pieces.
Also, Oracle scans rows to find a value of particular column. If you call
SELECT COUNT(COL254) FROM TBL;
Oracle will go through all values from COL1 to COL254 in every row.
It may be expensive with huge number of rows.
Looking back at the OP, particularly the part about creating a view to get the columns in the right order, I think the answer ought to be "the order of columns returned is just like any other sql, not guaranteed unless you have an order by." So don't use select *, rather, explicitly name the columns in the order desired to select.