Oct 282013
 

If you want the colums in your tables to appear in a different order, you can clone the table, drop the old and rename the new:

-- hard way
create table i_want_to_reorder (
  is_confusing              integer,
  because_the_current_order integer
);

create table reordered_columns as 
  select because_the_current_order,
         is_confusing
  from   i_want_to_reorder;

drop table i_want_to_reorder purge;

rename reordered_columns to i_want_to_reorder;

drop table i_want_to_reorder purge;

Alternatively, you can do this much quicker (especially if the table is “large”) by just creating a view with the new column order:

-- easy way
create table i_want_to_reorder (
  is_confusing              integer,
  because_the_current_order integer
);

rename i_want_to_reorder 
to i_want_to_reorder_tab;

create view i_want_to_reorder as
  select because_the_current_order,
         is_confusing
  from   i_want_to_reorder_tab;

drop view i_want_to_reorder;
drop table i_want_to_reorder_tab purge;

If your application is setup nicely and has synonyms to your table (rather than connecting directly) then you just need to repoint the synonyms (rather than renaming). Of course, this always begs the question why do you need to re-order the column list – I can’t think of any (good) reasons.

UPDATE As pointed out by Matthias in the comments, there’s the 12c approach: make columns invisiable and visible again:

-- 12c way
create table i_want_to_reorder (
  is_confusing              integer,
  because_the_current_order integer
); 

alter table i_want_to_reorder 
modify ( is_confusing invisible );

alter table i_want_to_reorder 
modify ( is_confusing visible );

desc i_want_to_reorder

BECAUSE_THE_CURRENT_ORDER NUMBER(38)
IS_CONFUSING	          NUMBER(38)

  2 Responses to “Column Re-ordering: The Hard Way and the Easy Way”

  1. in 12c, we also can


    sokrates@12.1 > create table i_want_to_reorder (
    is_confusing integer,
    because_the_current_order integer
    ); 2 3 4

    Table created.

    sokrates@12.1 > alter table i_want_to_reorder modify ( is_confusing invisible );

    Table altered.

    sokrates@12.1 > alter table i_want_to_reorder modify ( is_confusing visible );

    Table altered.

    sokrates@12.1 > desc i_want_to_reorder
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    BECAUSE_THE_CURRENT_ORDER NUMBER(38)
    IS_CONFUSING NUMBER(38)

    see http://tkyte.blogspot.de/2013/07/12c-silly-little-trick-with-invisibility.html

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)