Re: Rearrange columns

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 19 Jan 2007 07:19:33 -0800
Message-ID: <1169219973.110385.173730@38g2000cwa.googlegroups.com>

On Jan 19, 5:57 am, "gazzag" <gar..._at_jamms.org> wrote:
> MRCarver wrote:
> > What is the preferred way for rearranging columns in Oracle 10g XE. I
> > can't seem to find a way to do this..
>
> > Regards,
>
> > A Oracle Newbie
> > MR CarverWhy do you want to do this? Column order is irrelevant in a RDBMS.
>
> However, if you _really_ feel the urge to:
>
> CREATE TABLE <new_table>
> AS SELECT <column5>, <column2>, <column1>, <column4>, <column5>
> FROM <original_table>;
>
> RENAME <original_table> TO <original_table_bak>;
>
> RENAME <new_table> TO <original_table>;
>
> HTH
>
> -g

The preferred method of rearranging the columns are

1- just change the order of the columns in the SELECT list
2- create a view with the desired order
3- use dbms_redefinition to redefine the table in the new order
4- CTAS to recreate the table however you also need to manually rename

the constraints, indexes, and triggers on the table, if any.

HTH -- Mark D Powell -- Received on Fri Jan 19 2007 - 09:19:33 CST