This discussion is archived
13 Replies Latest reply: Jul 20, 2006 12:28 AM by APC RSS

Re-sorting of table columns

524007 Newbie
Currently Being Moderated
Hello,

I would like to know if there is an Oracle feature to change the order of columns in an existing table. The table is quite huge (several 100 millions rows).

I want to avoid to re-create the table and migrate the data.

Thank you and best regards -
Chris
  • 1. Re: Re-sorting of table columns
    ennisb Newbie
    Currently Being Moderated
    Don't think so. Why does the column order matter?
  • 2. Re: Re-sorting of table columns
    SomeoneElse Guru
    Currently Being Moderated
    to change the order of columns in an existing table
    You can SELECT them in whatever order you need. Re-arranging them physically is pointless. (SELECTing them in some order is pointless too).
  • 3. Re: Re-sorting of table columns
    500324 Newbie
    Currently Being Moderated
    Unfortunately I have had to do this in the past:

    Don't drop your table of course, but I wanted this to be complete:

    DROP TABLE REORDER;

    CREATE TABLE REORDER
    (COLUMN4 NUMBER
    ,COLUMN3 NUMBER
    ,COLUMN2 NUMBER
    ,COLUMN5 NUMBER
    ,COLUMN1 NUMBER
    ,COLUMN6 NUMBER
    );


    Now if you select it the columns come up in the order it was created
    SELECT * FROM REORDER;

    You can simply select the columns in the order you want:

    SELECT COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6 FROM REORDER;

    You can create a view that the users can use if you wish:

    CREATE OR REPLACE VIEW ORDERED_REORDER AS SELECT COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6 FROM REORDER;

    SELECT * FROM ORDERED_REORDER;


    clean up
    DROP VIEW ORDERED_REORDER;


    I have done this on SMALLER tables less than a few thousand with 100 million+ rows, I wouldn't do this without some thorough testing.

    change the name of the table
    RENAME REORDER TO ORIG_REORDER;

    create a view to look like the original table

    CREATE VIEW REORDER AS SELECT COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6 FROM ORIG_REORDER;

    Now the view looks the way you want:

    SELECT * FROM REORDER;

    You can insert etc:
    INSERT INTO REORDER (COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6)
    VALUES (1,2,3,4,5,6);
    COMMIT;


    Some apps won't allow this, they need tables and won't allow views.
    Test test test. But first, I would suggest getting a good business case for this as this is an oddball request. Those requests do exist but get it in writing and see if there isn't a better way to handle the request.
  • 4. Re: Re-sorting of table columns
    APC Oracle ACE
    Currently Being Moderated
    Re-arranging them physically is pointless.
    Not in all cases. If we have chained rows and we have frequent queries which select a subset of columns across multiple blocks but which would theoretically fit into a single block then a physical re-arrangement might not be pointless.

    I agree that the number of circumsatnces in which this applies is few but maybe the OP is in that pinch. The only option beside drop and recreate is online redefinition but I'm not sure whether that's practical with 100m rows. Never done anything like that myself. Might be worth a punt though...

    Cheers, APC
  • 5. Re: Re-sorting of table columns
    524007 Newbie
    Currently Being Moderated
    Thanx for quick reply!

    Some background information. I am using IOT in Oracle 10. The table I am talking about could potentially have more than 255 columns.

    As far as I know populated columns in IOTs after column number 255 are automatically written to the table's overflow segment. For optimal IO performance I would like to re-order the tables' column such that columns with number >=255 are not populated.

    Pls correct me if my assumption is wrong.

    Thanx!
  • 6. Re: Re-sorting of table columns
    APC Oracle ACE
    Currently Being Moderated
    You have a multi-multi-million row IOT with more than 255 columns, not all of which are used????

    Cheers, APC
  • 7. Re: Re-sorting of table columns
    447708 Newbie
    Currently Being Moderated
    That's right, more than 255 columns oracle consider it as one more segment.


    Regards,
    Satheesh Babu.S
    Bangalore.
  • 8. Re: Re-sorting of table columns
    SomeoneElse Guru
    Currently Being Moderated
    Not in all cases.
    OK.

    My current client had such a problem a while back. The way they solved it was to create a new tablespace with a larger block size.
  • 9. Re: Re-sorting of table columns
    ennisb Newbie
    Currently Being Moderated
    Behind the scenes redef must create another table last I used it.
  • 10. Re: Re-sorting of table columns
    247514 Expert
    Currently Being Moderated
    It's not segment level however. It's seperate Row pieces.
    Even that, there's no IO impact if your whole row fit into one block as concept mentioned.

    ---------------------------------------
    Row Format and Size
    Oracle stores each row of a database table containing data for less than 256 columns as one or more row pieces. If an entire row can be inserted into a single data block, then Oracle stores the row as one row piece. However, if all of a row's data cannot be inserted into a single data block or if an update to an existing row causes the row to outgrow its data block, then Oracle stores the row using multiple row pieces. A data block usually contains only one row piece for each row. When Oracle must store a row in more than one row piece, it is chained across multiple blocks.

    When a table has more than 255 columns, rows that have data after the 255th column are likely to be chained within the same block. This is called intra-block chaining. A chained row's pieces are chained together using the rowids of the pieces. With intra-block chaining, users receive all the data in the same block. If the row fits in the block, users do not see an effect in I/O performance, because no extra I/O operation is required to retrieve the rest of the row.
    ------------------------------------------------------------------------

    also something about column order
    --------------------------------------------------------------------------
    Column Order
    The column order is the same for all rows in a given table. Columns are usually stored in the order in which they were listed in the CREATE TABLE statement, but this is not guaranteed. For example, if a table has a column of datatype LONG, then Oracle always stores this column last. Also, if a table is altered so that a new column is added, then the new column becomes the last column stored.

    In general, try to place columns that frequently contain nulls last so that rows take less space. Note, though, that if the table you are creating includes a LONG column as well, then the benefits of placing frequently null columns last are lost.
  • 11. Re: Re-sorting of table columns
    Daljit Explorer
    Currently Being Moderated
    WOW GREAT explaination yingkuan, I also didn't know about this "intra-block chaining" funda and its really good to know this piece too.

    Daljit Singh
  • 12. Re: Re-sorting of table columns
    247514 Expert
    Currently Being Moderated
    lol, same here.
    I won't research about the stuff if the OP haven't asked.

    Haven't used any table has more than 255 columns anyway.
  • 13. Re: Re-sorting of table columns
    APC Oracle ACE
    Currently Being Moderated
    My current client had such a problem a while back. The way they solved it was to
    create a new tablespace with a larger block size.
    I'm sure they had some lively debates over that one ;)

    Cheers, APC