-1

Can i able to reorder the column positions in oracle?

If yes, then how i'm going to do that?

i'm also tried this

ALTER TABLE PANEL ADD ID NUMBER GENERATED BY DEFAULT AS IDENTITY FIRST;
meha
  • 17
  • 4
  • 1
    _Why_ do you want to do this? – Tim Biegeleisen Apr 01 '23 at 12:17
  • for reset constrain i found the solution like, we need to drop that column and after perform truncate again add that table with constrain. So, if i'm doing that then this column added as last position and i need that at first location – meha Apr 01 '23 at 12:19
  • 2
    Why does the position matter to the table? You can *display* the columns in any order you want. – pmdba Apr 01 '23 at 12:47
  • You do not need to recreate table to modify a constraint, just disable/drop it, perform modifications of the data and enable/create again. Constraints can be added/removed by `alter table`, there are very rare cases when you need to recreate a table – astentx Apr 01 '23 at 20:03

1 Answers1

1

If you wish to reorder the columns as they are stored sequentially in blocks, and as they would appear when doing SELECT *, you must recreate the table.

  1. Create a new table and insert data from old table into new (use CREATE TABLE AS SELECT to do this in one step)
  2. Add any indexes/constraints/grants
  3. Drop the old table
  4. Rename the new table to the old table's name.

But in most cases there is no real need to do this, as physical column order and order of presentation by queries are not correlated. It only matters for SELECT * or for INSERT VALUES without specifying column lists, neither of which are good programming practices. [I suppose if you did have a table over 255 columns (bad idea) you could put the most accessed columns in the first 255 and push any rarely access columns to later positions to decrease the amount of row chain reads, but a better solution is to break the table up and keep under 255 cols at all times]

Paul W
  • 5,507
  • 2
  • 2
  • 13
  • 1
    "you must recreate the table" is misleading. See the linked duplicate but you can do it without recreating the table by making the columns invisible and then making them visible again one-by-one in the correct order. If you did want to recreate the table then `DBMS_REDEFINITION` will also do it – MT0 Apr 01 '23 at 14:31
  • dbms_redefnition physically recreates the segment, so does what I outlined above for you, just a convenient wrapper. marking columns visible, invisible may change the visible column sequence but it does not change the physical storage sequence in blocks, which is of interest if you are dealing with row chaining. Physically recreating the table is the only way to truly, entirely change the order in every sense. – Paul W Apr 01 '23 at 16:40
  • Building and maintaining logical column orders can be important if you want to build a good looking and more easily understandable schema. In practice, database developers and users are most likely to interact with tables through ad hoc `SELECT * FROM ...` queries. The `*` shouldn't be left in production queries, but the `*` is still the primary interface for people to learn how to use a schema. Column order is like indentation - it doesn't matter to the compiler, but we should write our programs for other people, not for machines. – Jon Heller Apr 19 '23 at 03:56