ALTER TABLE SQL QUERY

The ALTER TABLE Statement

Once the table is created, and sometimes we need to change the table structure or column properties need to be changed, or we need to remove certain columns. We need to use ALTER TABLE statement. Use the ALTER TABLE statement to perform operations like adding a new column, modify an existing column, defining a default value for the new column and drop a column.

Syntax

ALTER TABLE table_name

ADD (column datatype [DEFAULT expr], column datatype...);

ALTER TABLE table_name

MODIFY (column_name datatype [DEFAULT expr], column datatype...);

ALTER TABLE table_name

DROP (column column_name);

table_name	It is the table name
ADD|MODIFY|DROP	Modification clause
Column_name	New column name which is to be added. Existing column name which is to be modified or dropped.
datatype	I s the data type and length of the new column
DEFAULT expr	The default value for a new column

ADD
ALTER TABLE table_name

ADD (column datatype [DEFAULT expr], column datatype...);

Example
Let us now add Country column to students table.
alter table students

add Country varchar(05);

select * from students;

The new column becomes the last column. The new column is initially NULL for all the rows. We will try updating table for this column.

update students set country='NEWYORK'

where CITY='Central';

Above error occurred since the width defined was 05 and we tried to update with value of 7. This is one of the scenario in which we require modification of column data type length which is done with use of MODIFY clause as shown below syntax.

MODIFY

ALTER TABLE table

MODIFY (column datatype [DEFAULT expr], column datatype...);

We can increase the precision of a numeric column using this clause. Also can increase the size of numeric or character columns. We can decrease the width of a column only if the column contains only null values or if the table is empty i.e. has zero rows. We are restricted to change the data type only if the column contains null values. Also perform data type conversion such as converting a CHAR to the VARCHAR2 data type or a VARCHAR2 to the CHAR only if the column contains null values or if you do not change the size.

Example

Modifying VARCHAR value from 05 to 50 for column Country in students table.

alter table students

modify Country varchar(50);

update students set country='NEWYORK'

where CITY='Central';

commit;

select * from students;

DROP

ALTER TABLE table

DROP (column);

When we don’t need some columns in our table we can use  the DROP COLUMN clause to drop columns. It is not mandatory to have data in to drop a column. The table must have at least one column remaining in it after it is altered.  Only one column can be dropped at a time. Once a column is dropped, it cannot be recovered.

Example

Removing the Country column from students table.

alter table students

drop column Country;

select * from students;

SET UNUSED

The SET UNUSED option is use to mark one or more columns as unused.

DROP UNUSED COLUMNS option is used to remove the columns that are marked as unused.

ALTER TABLE table

SET UNUSED (column_name);

OR

ALTER TABLE table

SET UNUSED COLUMN column_name;

ALTER TABLE table

DROP UNUSED COLUMNS;

When a column is dropped from a table, it will also drop any other columns in that table that are marked with the SET UNUSED option. The SET UNUSED option marks one or more columns as unused so that they can be dropped on demand when a system resource is lower. Setting a column as UNUSED just marks a column for logical deletion and the columns are no longer available for DML, DDL or SELECT commands. But the data in the columns is still intact in the segment and not dropped. UNUSED is much faster and as it doesn't update the data file with the changes. The actual DROP will take place after issuing DROP UNUSED columns.

Therefore, if the intent  is to cut-off access to columns without I/O latency in production during peak business hours, then first mark it as UNUSED and then during the weekend or off peak hours, complete the physical drop of the column using DROP UNUSED.