CODEWITHSUNDEEP
sqldatabaseoracle-databasedatabase-design
Akki
Akki

Reputation: 1251

Alter Physical Structure of table Oracle11gr2

I have a table

Name              Age                    RollNo.
A                  1                      10
B                  2                      20

Now I want to alter the table permanently in such a way that,After altering it should look as below

RollNo.           Name                    Age
10                 A                      1
20                 B                      2

How shall i alter this table , All i want to do is to change physical structure of the table.

Upvotes: 2

Views: 159

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

Why do you want to do it?

If it's just because you'd like to have a correct order of columns when using SELECT *, then you should not have used * in the first place. Always use the exact list of columns in your queries.

If it's because you think it would improve the performance, have you done the actual measurements? I doubt you'll find many scenarios where changing the physical column order influences performance in a significant way. There are some scenarios with chained rows where it might (see the "Row Chaining" section in this article), but that doesn't apply to narrow rows such as yours.

That being said, you could:

  • CREATE TABLE NEW_TABLE AS SELECT <different column order> FROM OLD_TABLE.
  • Recreate all the relevant constraints (such as keys, FKs), indexes and triggers/procedures on the NEW_TABLE.
  • DROP TABLE OLD_TABLE.
  • ALTER TABLE NEW_TABLE RENAME TO OLD_TABLE.

You might also want to look at the dbms_redefinition if you need to do that while accepting updates.

Upvotes: 3

TechDo
TechDo

Reputation: 18629

You can drop and create the table without loosing the data in oracle using statement 

create table YOUR_TABLE_BU as select * from YOUR_TABLE

Please go through the link - How can I create a copy of an Oracle table without copying the data? for more details. Try:

CREATE TABLE YOUR_TABLE_BU AS SELECT * FROM YOUR_TABLE;

DROP TABLE YOUR_TABLE;

CREATE TABLE YOUR_TABLE AS SELECT RollNo., Name, Age FROM YOUR_TABLE_BU;

DROP TABLE YOUR_TABLE_BU;

Upvotes: 0

Related Questions