When adding a column for an existing table, Oracle always puts the column in the finish on the table. Can you really tell Oracle where it will come in the table? If that's the case, how?

The position of the column within the table ought to be trivial (unless of course you will find "page dimensions" to think about, or whatever Oracle uses to really keep data). Furthermore vital that you the customer is when the outcomes are known as, i.e. the Choose statement.

relabel YOUR_ORIGINAL_TABLE as the_NEW_TABLE

create table YOUR_ORIGINAL_TABLE nologging /* or unrecoverable */ as choose Column1, Column2, NEW_COLUMN, Column3 out of yourNEW_TABLE

Drop table YOUR_NEW_TABLE

Choose * Out Of YourUniqueDesk <<<<< now you will notice the brand new column in the center of the table.

Why would for you to do it? It's appears irrational. You shouldn't assume column ordering and merely use named column list if column order is essential.

I do not believe that you can do this without saving the information to some temporary table, shedding the table, and re-creating it. However, it truly should not matter in which the column is. As lengthy while you specify the posts you're locating inside your choose statement, you can find them nevertheless, you want.

I do not believe so - SQL Server does not allow these either. The technique I usually need to use is:

  1. Create new table that appears right (including additional column
  2. Begin transaction
  3. choose all data from old table into brand new one
  4. Drop old table
  5. Relabel new table
  6. Commit transaction.

Not quite pretty, but will get the task done.

Things I normally do is:

  1. Relabel that old table.
  2. Produce the new table with posts within the right order.
  3. Produce the constraints for your new table.
  4. Populate with data:Place into new_table choose * from re-named table.

How come an order from the posts matter? You could alter it inside your choose statement?

There's a benefit to adding new posts in the finish on the table. If there's code that naively does a "Choose *" after which parses the fields so as, you will not be breaking old code with the addition of new posts in the finish. Should you add new posts in the center of the table, then old code might be damaged.

At one job, I'd a DBA who had been super-rectal about "Never do 'SELECT *'". He was adamant that you simply always create the particular fields.

Keep in mind that, underneath the tables, all of the data within the table records are glued together. Adding a column towards the finish of the table [if it's nullable or (in later versions) not null having a default] means a big change towards the table's metadata. Adding a column in the centre would require re-writing every record for the reason that table to include the right value (or markers) for your column. In some instances, that may mean the records occupy more room around the blocks plus some records have to be migrated. In a nutshell, it is a Huge quantity of IO effort for any table associated with a real size.

You could produce a view within the table which has the posts within the preferred order and employ that view inside a DML statement just like you'd the table

No, it is not possible with an "ALTER TABLE" statement. However, you can produce a new table with similar definition as the current one, although having a different title, using the posts within the correct order in the manner you would like them. Copy the information in to the new table. Drop that old table. Relabel the brand new table to complement that old table title.

Tom Kyte comes with an article about this on AskTom link text