Re-order columns of table in Oracle

sql

database-performance

sql-optimization

oracle-database

byAlex Kataev·Dec 12, 2024

For column reorganization in Oracle, clone your table in the new order and then move data across. Remember, Oracle does not support direct rearrangement operations. So let's get it done:

CREATE TABLE new_table AS
SELECT col1, col3, col2 FROM old_table; -- column shuffle
DROP TABLE old_table; -- old table, old problems
RENAME new_table TO old_table; -- the circle of life

Remember to reapply all indexes, constraints, and permissions after the process. And of course, have a backup ready in case things south.

Wizarding with invisible columns

Oracle, since its 12c version, gave an infernal power of invisibility to any database column that desired it. An invisible column, once revealed to the Muggles, appears at the tail end of your table structure.

ALTER TABLE your_table MODIFY col_name INVISIBLE; -- Hocus pocus!
ALTER TABLE your_table MODIFY col_name VISIBLE; -- Ta-da!

Column visibility can be toggled in your desired sequence. This can act as a stealthy move to shuffle columns with minimal disruptions.

Logic is your Hogwarts

The art of placing table columns logically can help your database deliver efficient data response, particularly for unindexed or commonly accessed columns. A non-null column before nullable ones is a smart choice for maximum performance.

Make sure to group related columns. This grouping can greatly improve the clarity and understanding of your database.

Contain the storm

When your table needs a major restructuring, Oracle's DBMS_Redefinition can help recreate the table without causing a blizzard in your user interactions. This can be a knight in shining armor when dealing with large production databases where performance and uptime are paramount.

The Oracle's Prophecy

Anticipate future shifts to prevent the headache of constant restructuring. Identifying the patterns and usage trends can help in strategizing column orders that accommodate growth and evolving needs.

Post-reorder charm

After a successful shuffle, remember to test your spells and document any changes in performance. If you encounter any invalid code or broken objects like constraints or triggers, try a counter-spell. A little meticulousness can go a long way for the integrity and reliability of your magical database.

Simplify with magical VIEW

In case your reordering is just to give your eyes a break and not for performance, consider crafting a VIEW that magically rearranges the data without disturbing the physical table.

Magical examples to the rescue

Time to demonstrate some of Oracle's charms through SQL examples.

-- Initial wand movements
SELECT col1, col2, col3 FROM magic_table;

-- The rearrangement charm
CREATE VIEW magic_view AS
SELECT col1, col3, col2 FROM magic_table;

-- The reveal!
SELECT * FROM magic_view;