Re-order columns of table in Oracle

Question

I have a table with 50  columns and I need to swap the order of the first two columns  What is the best way to accomplish this using Oracle  Assume the table name is ORDERDETAILS and as it is  the first two columns are ITEM ID and ORDER ID  Once the rename is complete  the table name should still be ORDERDETAILS but the first two columns will be ORDER ID and ITEM ID  FWIW  column types and the rest of the columns and their order is irelevent   Correct me if I m wrong  but I think the general steps are    Rename the existing table  Drop the primary key constraint  Re-create the table with the correct column order  List item Run INSERT INTO    SELECT to move the data from temp to the table in step  3  Drop the temp table    I have little experience with Oracle so perhaps I m missing a step or two   Does a primary key imply an index in Oracle  Does dropping the primary key also drop the index    SQL examples are much appreciated   EDIT  Un-sincere thanks to those who question why it needs done instead of providing help  To answer your question as to why it needs done  I m following orders from someone else who says I need to do it this way and the order of the columns DOES matter  My thoughts opinions on this are irrelevent

User · Answer

I followed the solution above from Jonas and it worked well until I needed to add a second column. What I found is that when making the columns visible again Oracle does not necessarily set them visible in the order listed in the statement.

To demonstrate this follow Jonas' example above. As he showed, once the steps are complete the table is in the order that you'd expect. Things then break down when you add another column as shown below:

Example (continued from Jonas'):

Add another column which is to be inserted before column C.

ALTER TABLE t ADD (b2 INT);

Use the technique demonstrated above to move the newly added B2 column before column C.

ALTER TABLE t MODIFY (c INVISIBLE, d INVISIBLE, e INVISIBLE);
ALTER TABLE t MODIFY (c VISIBLE, d VISIBLE, e VISIBLE);

DESCRIBE t;

Name
----
A
B
B2
D
E
C

As shown above column C has moved to the end. It seems that the ALTER TABLE statement above processed the columns in the order D, E, C rather than in the order specified in the statement (perhaps in physical table order). To ensure that the column is placed where desired it is necessary to make the columns visible one by one in the desired order.

ALTER TABLE t MODIFY (c INVISIBLE, d INVISIBLE, e INVISIBLE);
ALTER TABLE t MODIFY c VISIBLE;
ALTER TABLE t MODIFY d VISIBLE;
ALTER TABLE t MODIFY e VISIBLE;

DESCRIBE t;

Name
----
A
B
B2
C
D
E

User · Answer

Use the View for your efforts in altering the position of the column  CREATE VIEW CORRECTED POSITION  AS  SELECT co1 1  col 3  col 2  FROM UNORDERDED POSITION  should help   This requests are made so some reports get produced where it is using SELECT   FROM  table name   Or  some business has a hierarchy approach of placing the information in order for better readability from the back end   Thanks Dilip

User · Answer

It s sad that Oracle doesn t allow this  I get asked to do this by developers all the time    Here s a slightly dangerous  somewhat quick and dirty method    Ensure you have enough space to copy the Table Note any Constraints  Grants  Indexes  Synonyms  Triggers  um   maybe some other stuff - that belongs to a Table - that I haven t thought about  CREATE TABLE table right columns AS SELECT column1 column3  column2 FROM table wrong columns  -- Notice how we correct the position of the columns    DROP TABLE table wrong columns   ALTER TABLE table right columns RENAME TO table wrong columns   Now the yucky part  recreate all those items you noted in step 2 above Check what code is now invalid  and recompile to check for errors   And next time you create a table  please consider the future requirements

User · Answer

Look at the package DBMS Redefinition   It will rebuild the table with the new ordering   It can be done with the table online     As Phil Brown noted  think carefully before doing this   However there is overhead in scanning the row for columns and moving data on update   Column ordering rules I use  in no particular order     Group related columns together  Not NULL columns before null-able columns  Frequently searched un-indexed columns first  Rarely filled null-able columns last  Static columns first  Updateable varchar columns later  Indexed columns after other searchable columns    These rules conflict and have not all been tested for performance on the latest release   Most have been tested in practice  but I didn t document the results   Placement options target one of three conflicting goals  easy to understand column placement  fast data retrieval  and minimal data movement on updates

User · Answer

Since the release of Oracle 12c it is now easier to rearrange columns logically   Oracle 12c added support for making columns invisible and that feature can be used to rearrange columns logically   Quote from the documentation on invisible columns      When you make an invisible column visible  the column is included in the table s column order as the last column    Example  Create a table   CREATE TABLE t       a INT      b INT      d INT      e INT      Add a column   ALTER TABLE t ADD  c INT     Move the column to the middle   ALTER TABLE t MODIFY  d INVISIBLE  e INVISIBLE   ALTER TABLE t MODIFY  d VISIBLE  e VISIBLE     DESCRIBE t   Name ---- A B C D E   Credits  I learned about this from an article by Tom Kyte on new features in Oracle 12c

[sql] Re-order columns of table in Oracle

Examples related to sql

Examples related to oracle

Examples related to rename