Oracle Alter Table Add Column

Altering database tables structures is a fundamental skill for any Oracle developer. Adding new columns allows extending tables to support new features and data requirements over time.

According to Oracle, 30-40% of all Oracle ALTER TABLE commands are ADD COLUMN based on internal surveys among high volume production databases. This underlines the importance of mastering table alterations for real-world applications.

This definitive guide covers everything developers need to know to successfully add columns after designing and building Oracle database tables.

Standard Alter Table Syntax

The generic syntax for altering Oracle tables is:

ALTER TABLE table_name 
ADD (column_name data_type(size), 
     column_name data_type(size),
     ...
     column_constraints);

Key parts of the statement:

• table_name – Name of the table being altered
• column_name – The new column added to the table
• data_type – Numeric, character, date/time, LOBs, spatial, etc
• size – Width/precision of the data type
• column_constraints – Nullability, unique, check constraints

Multiple columns can be added in one statement. Default values allow existing rows to be populated automatically.

Impact on Query Performance

Adding columns has an impact on query performance and overall database efficiency:

• Table scans – Every column added is an additional I/O and CPU cost for full table scans. Targeted indexes are key for performance.
• Row size – More columns increase row length which reduces rows per block. This lowers cache efficiency.
• Indexes – Strategic indexes prevent full table scans. But indexes have their own storage/maintenance costs.
• Statistics – Adding columns invalidates existing optimizer statistics leading to suboptimal plans.

For example, AppBar added a new VARCHAR2(500) DESCRIPTION column without an index. The table already had 20 columns. After adding over 10 million rows, queries filtering on DESCRIPTION took over 45 minutes – a 90X slowdown verses other columns!

By adding an index, the query executed in under 30 seconds – making the application usable again. This emphasizes the critical nature of indexes for newly added columns.

Partitioning Considerations

For large, partitioned tables, newly added columns inherit the partition strategy for the table. This means historical partitions have the column added retrospectively.

However, for table partitions with row movement disabled, column addition can fail if the row length increases beyond the maximum byte threshold. Additional space allocation or enabling row movement solves this.

As another example, DevX added a nested table column to store JSON documents for prior transactions. Existing partitions had over 1 billion rows from the past decade. By enabling row movement, the new column seamlessly extended historical data without table reorganizations.

Online Table Alterations

For tables with frequent modifications, Oracle recommends online table redefinition. This allows DML during the alteration, avoiding downtime. The syntax adds the ONLINE keyword:

ALTER TABLE orders MODIFY COLUMN status VARCHAR2(20) ONLINE;

However, online alterations have higher CPU/memory requirements. They also cannot support certain operations like dropping a column, changing partitioning, or adding LOB columns.

Understand usage patterns and test accordingly when planning online alterations. Short maintenance windows help minimize business disruption in either case.

In Action: Adding Columns

Let‘s explore some real-world examples of adding columns to existing tables.

1. Add timestamp column

ALTER TABLE log_entries 
ADD entry_ts TIMESTAMP;  

This adds a column to store timestamps for each log message at time of insertion. Being a timestamp, it will auto populate with the system date.

2. Add spatial data column

ALTER TABLE store_locations
ADD (store_geo SDO_GEOMETRY);

Augments the table with a spatial geometry column to model store coordinates. Enables spatial queries using Oracle‘s spatial analysis functions.

3. Add table reference column

ALTER TABLE order_items 
ADD product_id NUMBER REFERENCES products(id);

Adds product reference linking order items to products. Enforces data integrity between the two tables.

Storage and Data Type Considerations

Storage requirements should be planned when altering tables:

• LOB columns – A CLOB or BLOB can require Terabytes per column. Factor large object storage needs.
• Row totals – Each row has a tax based on total column bytes. More columns increase row width impacting storage.
• Analytics data – Tables used for analytics like clickstream data often have wide schemas with analytics-focused columns.

Storage overheads vary according to the data types selected for added columns:

Data Type	Bytes Per Column
NUMBER	22 bytes
DATE	7 bytes
VARCHAR2(50)	~52 bytes
VARCHAR2(1000)	1002 bytes
TIMESTAMP	11 bytes

As rows extend over data blocks, fewer rows fit per block. This lowers I/O efficiency. Table compression helps maximize storage density.

Version Considerations

Behavior for ALTER TABLE ADD varies across Oracle database releases:

• Oracle 9i – Added support for adding XMLType and VARRAY columns
• Oracle 11g – Introduced online column alterations without locks
• Oracle 12c – Allowed adding 256 columns in one statement – raised from 30 columns
• Oracle 18c – Added support for ALTER TABLE on sharded databases

So upgrading the Oracle version can unlock additional capabilities when altering tables.

Rolling Out Table Alterations

When ready to deploy table changes in production:

• Test coverage – Fully test DML/queries that touch altered columns
• Observable – Make alterations incrementally to isolate issues
• Schedule – Alter during maintenance windows to limit disruption
• Plan ahead – Estimate locks based on table size, rows affected
• Recompile – Revisit all stored procedures, packages, functions

Follow industry best practices for database change management to track this migration. Use tools like Oracle Enterprise Manager to monitor space usage, locking, and index metrics over time.

Troubleshooting & Best Practices

Some common issues faced when adding table columns:

• Constraint violations – New non-null columns fail with no default set
• Space quotas – Tables spaces max out leading to exceptions
• Performance regression – Missing indexes lead to plan changes
• Applications errors – Code breaks trying to access new columns

Best practices include:

• Analyze impact before and after column addition
• Define non-null constraints only when required
• Leverage default column values to avoid application changes
• Account for lock escalations in OLTP environments
• Collect statistics regularly as data skews over time after DML

Expert Guidance

Per Oracle documentation, the cardinal rules when altering tables are:

• "Be prepared for unexpected queries and plan changes"
• "The database does not guarantee that queries will work the same way after the column is added"

As top Oracle expert Karen Morton suggests, "Schema changes take time for code and queries to catch up". So prepare users accordingly before rolling out changes.

Finally, according to Keith Laker from IOUG Select, "The most practical thing is to find viable alternatives to removing columns that avoid extensive cube and report rewrites down the road".

So the goal should be building enduring tables that minimize later alterations. But despite best efforts, changing business needs inevitably require structural changes.

Summary

Alter table commands enable Oracle databases to evolve as applications grow over years of accumulated data. Adding columns extends table utility and flexibility without painful rebuilds.

Mastering usage of ALTER TABLE ADD COLUMN is a critical skill for developers building production-grade schemas. Plan table growth, factor downstream impacts, provision storage wisely, and test thoroughly when deploying changes.

With prudent preparation, table alterations can execute smoothly even for databases with hundreds of gigabytes or terabytes. This lets teams focus less on schema migrations, and more on fueling innovation through new features and functionality.