Alter table drop column oracle :

In my previous articles i have given the basic idea about adding column with using Alter table in Oracle.This article will be helpful for programmers to understand the concept of Alter table drop column oracle.I would like to explain different real world examples of Alter table drop column oracle.This article will give you exact idea about what exactly the Alter table drop column oracle with real world industry examples.User will confuse in the two different statements of database management system  i.e Delete and drop. The drop and delete statements are all together different statements.

Alter table drop column oracle syntax and examples :

In this section i would like to explain the exact syntax of Alter table drop column oracle with different examples.There was no facility of dropping the columns from table before oracle 8i.The Oracle 8i provides the facility to drop the columns from oracle table.There are two types of dropping column facilities available in Oracle.I would like to explain each of this with real world industry examples with scenarios.

1.Alter table drop column oracle with Logical delete :

The user might know that huge tables are used to store the data in Oracle SQL.In huge tables the process of dropping columns from table is really very time consuming. It also consumes lot of resources and slows down the performance of application.If the application is on live server then its really very difficult to drop the table columns.

Oracle has given us the solution of Logical delete of the column. The data as well as the column is physically available in memory but it has logically unused.To achieve this oracle has provided the new feature of setting the column properly as ‘Unused’.

I would like to give you some situations where user needs to go for logical delete rather than physical delete :

Situation 1 : Tables with Huge data

There are situations where table contains millions of data and we need to drop the column. In that case direct dropping column will take a time as well as resources.We need to use the Unused feature of oracle in that case.

Situation 2 : Memory available

When the hardware and memory is available to store the unused data use this feature.

Syntax :

Alter table table_name Set Unused(Column_name)  — This syntax for single column

Oracle provides facility to make multiple columns unused.

Syntax :

Alter table table_name Set Unused(Column_name1,Column_name2…..Column_name ‘N’)

— This syntax for multiple column

Once this operation is done the unused columns will not be visible to the end users. The main issue here is memory consumption.When you get  the time user needs to physically remove all the columns with using following statement.

Alter table table_name drop unused columns;

Real Life Example :

Lets say the Employee table contains 2 million of data and user wants to remove the column named Last_name and first_name.In that case removing the columns with direct drop statement will consume more resources and it slows down the performance of application.

The User needs to use following statement to alter table drop column oracle :

Alter table Employee Set Unused(First_Name,Last_Name);

The above statement will make those two columns as ‘Unused’ .The following statement will use to drop those columns,

Alter table Employee drop unused columns;

These examples will give the idea to user about dropping the columns logically in Oracle.

Using Checkpoint option :

User can use the checkpoint option to control the undo logs from oracle.

Alter table table_name drop unused columns Checkpoint 500;

The above statement will drop the unused columns after 500 rows have been processed.

2.Alter table drop column oracle with Physical delete :

This option is most used option used by different developers to perform dropping of specific column.This option will eliminate the memory space as it physically drops the columns of the table.This is most recommended solution for dropping the specific column from the table.When the table is not so huge then this kind of solution needs to be used rather than going for logical type of dropping the columns.

Syntax 1 : For Single column

Alter table Table_name drop column Column_name;

The above statement will drop single column and release the memory space of that column as well.

Real life example :

If user wants to delete Name column from student table with its data.

Alter table Student drop column name;

The above statement will drop the column named ‘name’  from student table.

Syntax 2 : For Multiple columns

Alter table Table_name drop(Column_name_1,column_name_2….Column_name_n);

The above statement will drop multiple columns and release the memory space of that columns as well.

Real life example :

If user wants to delete Name,Surname column from student table with its data.

Alter table Student drop(name,Surname);

The above statement will drop the column named ‘name’ as well as ‘Surname’ from student table.

These are above some examples of Alter table drop column oracle.I hope you like this article.If you like this article or if you have any concerns with the same kindly comment in to comments section.