Wednesday, July 19, 2006

Changing column order

Somebody on OTN asked the question:

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.

I have seen this question a couple of time on the net over the last little while. I posted the answer below, and thought I might as well put it on the blog as well. The thread continued on after the initial question to include a bunch of more detail that made my answer not the correct answer. I guess I should have simply asked "Why" instead of trying to figure out what the person really wanted.

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.

3 comments:

pimolisa said...

This isn't an answer for how to change the order of table columns. You just create a view with the desired order. Just to clarify why the people probably want to change the order of columns. For example a new column comes in the production db but it is better that this new column comes after the 2nd column and not the last. Because semantically this new column fits matches the second column for instance. In that case, for a better look on the table (not the view) the people want to see that column on another place. I personally use Hibernate and using a view makes side effects and is an overhead.

Adi said...

Oes Tsetnoc one of the ways in which we can learn seo besides Mengembalikan Jati Diri Bangsa. By participating in the Oes Tsetnoc or Mengembalikan Jati Diri Bangsa we can improve our seo skills. To find more information about Oest Tsetnoc please visit my Oes Tsetnoc pages. And to find more information about Mengembalikan Jati Diri Bangsa please visit my Mengembalikan Jati Diri Bangsa pages. Thank you So much.
Oes Tsetnoc | Semangat Mengembalikan Jati Diri Bangsa

Jiri said...

PIMOLISA,
if you read carefully, the answer is at the top
first you drop the original table
then you recreate it with the desired order (eah record must have the original column name and type behind it, such as this:

DROP TABLE XTBL_BOXES;
CREATE TABLE TBL_BOXES
(COLUMN1 VARCHAR2(4000 BYTE),
LBX_NUMBER VARCHAR2(4000 BYTE),
LBX_NAME VARCHAR2(4000 BYTE),
WG VARCHAR2(4000 BYTE),
SECTION VARCHAR2(4000 BYTE),
REASSOCIATION_TYPE VARCHAR2(4000 BYTE),
CRITICAL VARCHAR2(4000 BYTE),
DATE_ADDED DATE,
DATE_MODIFIED DATE);