Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.4K Intelligent Advisor
- 75 Insurance
- 537.7K On-Premises Infrastructure
- 138.7K Analytics Software
- 38.6K Application Development Software
- 6.1K Cloud Platform
- 109.6K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.3K Infrastructure Software
- 105.4K Integration
- 41.6K Security Software
order of table columns: alter table add/modify ... position before/after ...

#
# 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 - ?)
#
Comments
-
Again I will ask why you need this kind of feature and where you are going to use it.
regards
Pravin
-
bornesel Solution Architect - Database & Middleware & Engineered Systems & Oracle ACE GermanyMember Posts: 267
Hi,
I cannot see any benefits oh the feature.
Regards
Borys
-
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
-
Whenever you need to sort your columns then simply create a view.
-
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.
-
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.
-
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
-
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.
-
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.
-
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