Forum Stats

  • 3,824,847 Users
  • 2,260,430 Discussions
  • 7,896,330 Comments

Discussions

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

Carsten Kaftan
Carsten Kaftan Member Posts: 10 Bronze Badge
edited Jan 11, 2016 5:45PM in Database Ideas - Ideas

#

# 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 - ?)

#

Carsten KaftanmarkmevansulohmannManish ChaturvediMartin PreissArpit Jain -OracleEric_BedardJagadekaravinaykumar2user7904656Sven W.ApexBineDéborahNiels HeckerTom NagleLukas EderDaniel E.chrisstephensTorsten Kleiber
31 votes

Active · Last Updated

«13

Comments

  • Pravin Takpire
    Pravin Takpire Member Posts: 1,762 Gold Trophy

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

    regards

    Pravin

  • bornesel
    bornesel Member Posts: 267

    Hi,

    I cannot see any benefits oh the feature.

    Regards

    Borys

  • Carsten Kaftan
    Carsten Kaftan Member Posts: 10 Bronze Badge

    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
    Marwim Member Posts: 3,649 Gold Trophy

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

  • Carsten Kaftan
    Carsten Kaftan Member Posts: 10 Bronze Badge

    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.

    ApexBine
  • Marwim
    Marwim Member Posts: 3,649 Gold Trophy

    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.

    berx
  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy

    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

    ApexBine
  • Carsten Kaftan
    Carsten Kaftan Member Posts: 10 Bronze Badge

    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.

    ApexBineUser_SAFN8
  • Carsten Kaftan
    Carsten Kaftan Member Posts: 10 Bronze Badge

    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
    Marwim Member Posts: 3,649 Gold Trophy

    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