This discussion is archived
6 Replies Latest reply: Dec 8, 2012 3:37 AM by Haider Hussain RSS

column between 2 existing columns

Haider Hussain Newbie
Currently Being Moderated
Hi ,

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.

Thanks,
Haider
  • 1. Re: column between 2 existing columns
    EdStevens Guru
    Currently Being Moderated
    Haider Hussain wrote:
    Hi ,

    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.

    Thanks,
    Haider
    the best suggestion is "don't bother modifying the table 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
  • 2. Re: column between 2 existing columns
    vlethakula Expert
    Currently Being Moderated
    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.

    locksnlatches.blogspot.com/2010/07/change-column-position.html
  • 3. Re: column between 2 existing columns
    VijayaraghavanKrishnan Pro
    Currently Being Moderated
    Hi Hussain,

    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.
    {thread:id=405314}


    Thanks,
    Vijay
  • 4. Re: column between 2 existing columns
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    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.
  • 5. Re: column between 2 existing columns
    jgarry Guru
    Currently Being Moderated
    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.
  • 6. Re: column between 2 existing columns
    Haider Hussain Newbie
    Currently Being Moderated
    Thanks everyone

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points