order of table columns: alter table add/modify ... position before/after ...

Carsten Kaftan

#

# A supplementary annotation (at a current score of -40):

# As there were some comments regarding the usefulnes of such a feature,

# I added an example at the bottom of this text.

#

I would like to resubmit the not very popular "Need a command to reorder columns in a table" of Torsten Kleiber with a different aspect: The position of table columns ultimately is not relevant, but a quick survey is very much simplified by a clearly arranged table layout (usage of "select * from …", etc.).

At the moment it is very laborious to keep evolving tablestructures well laid out. I would appreciate very much an extension of the "alter table"-syntax like the following:

ALTER TABLE [ schema. ] table

  { MODIFY | ADD }

  ( column_name [...]

    [position { FIRST | LAST | { BEFORE | AFTER } column_name }]

  )

P.S.

MySQL allows ALTER TABLE ADD COLUMN AFTER COLUMN.

#

# Consider a table with address-entries and further informations

#

# NAME            DOMICILE         LAST_CONTACT  COMMENT

# "Willi Wiberg"  "82152 Habsburg" 12-Dec-14     "VIP"

#

# Later, you want to differentiate between given and family name and

# separate the ZIP-Code from the place name.

# The altered table would look like this (in "natural column order"):

#

# NAME            DOMICILE         LAST_CONTACT  COMMENT  FIRST_NAME ZIP_CODE

# "Wiberg"        "Habsburg"       12-Dec-14     "VIP"    "Willi"    82152

#

# With positioned and renamed columns the layout would be much clearer:

#

# FAMILY_NAME FIRST_NAME ZIP_CODE CITY       LAST_CONTACT  COMMENT

# "Wiberg"    "Willi"    82152    "Habsburg" 12-Dec-14     "VIP"  

#

# Reordering of columns can be a useful feature for every table which

# contains "groups" of columns belonging together with regard to content.

#

# (I really do have difficulties to understand why it is so difficult

# to see benefits and possible applications of reordering columns - ?)

#

Pravin Takpire

Again I will ask why you need this kind of feature and where you are going to use it.

regards

Pravin

bornesel

Hi,

I cannot see any benefits oh the feature.

Regards

Borys

Carsten Kaftan

Again I will ask why you need this kind of feature and where you are going to use it.

regards

Pravin

Hello Pravin, hello Borys,

very often tables contain "groups" of columns, belonging together with regard to contents. A very simplified example would be a table, created with the following columns:

FILE01_DATE, FILE01_NAME, FILE02_DATE, FILE02_NAME

Later, you want to add error-handling and add the necessary columns:

FILE01_DATE, FILE01_NAME, FILE02_DATE, FILE02_NAME, FILE01_STATUS, FILE02_STATUS

For sorting purposes in the data dictionary ("select * from DBA_TAB_COLUMNS order by OWNER, TABLE_NAME, COLUMN_ID"), a quick survey ("select * from TABLE_NAME"), etc. it would be very beneficial to be able to position the columns according to related contents:

FILE01_DATE, FILE01_NAME, FILE01_STATUS, FILE02_DATE, FILE02_NAME, FILE02_STATUS

Regards,

Carsten

Marwim

Whenever you need to sort your columns then simply create a view.

Carsten Kaftan

Whenever you need to sort your columns then simply create a view.

Thanks for sharing this deep insight; now finally I know how to get around modifying or renaming or reordering tables and columns.

Marwim

Thanks for sharing this deep insight; now finally I know how to get around modifying or renaming or reordering tables and columns.

More than once my manager asked me to reorder columns because he "needs" them in a certain order in Toad. Even if I did so, the next manager wants another order. Within a program the column order is irrelevant  and managers come and go :-)

Since we use SQL Developer instead of Toad now, I simply wrote some shared reports with the columns in the "correct" order and my manager now calls the report instead of the table, happy that he even gets asked for parameters.

Martin Preiss

I guess it's not exactly what you are looking for but in 12c you can change the (logical) order of columns by setting them invisible and visible again - Tom Kyte gave an example in http://www.oracle.com/technetwork/issue-archive/2014/14-may/o34asktom-2162246.html

Carsten Kaftan

More than once my manager asked me to reorder columns because he "needs" them in a certain order in Toad. Even if I did so, the next manager wants another order. Within a program the column order is irrelevant  and managers come and go :-)

Since we use SQL Developer instead of Toad now, I simply wrote some shared reports with the columns in the "correct" order and my manager now calls the report instead of the table, happy that he even gets asked for parameters.

Yes, views are great for reporting or data-representation (and, if applicable, to keep users happy, because they see what they expect). But often even relational data has an clear immanent pattern, and I would like the table to reflect this structure.

Besides, I like to keep it simple and avoid inserting additional layers if not necessary.

Carsten Kaftan

I guess it's not exactly what you are looking for but in 12c you can change the (logical) order of columns by setting them invisible and visible again - Tom Kyte gave an example in http://www.oracle.com/technetwork/issue-archive/2014/14-may/o34asktom-2162246.html

That´s much better than DBMS_REDEFINITION just to reorder columns — with this workaround I could do very well. I didn't realise this use case of invisible columns; cheers to Tom Kyte and you :-)

I still don´t understand why straight column-reordering is evil, but now I won´t brood any more over this mystery.

Marwim

That´s much better than DBMS_REDEFINITION just to reorder columns — with this workaround I could do very well. I didn't realise this use case of invisible columns; cheers to Tom Kyte and you :-)

I still don´t understand why straight column-reordering is evil, but now I won´t brood any more over this mystery.

I just found a use case where it might be useful to insert columns at a certain place

https://jonathanlewis.wordpress.com/2015/02/19/255-columns/

Though I never came anywhere near 255 columns