How to ALTER Table, Index and More: Useful Queries

With the ALTER statement, you can add columns, add indexes, add keys, add buffer pools, and modify function parameters. Here are the SQL queries on how to use ALTER.

SQL ALTER statement: These You Can do

Here are database objects you can ALTER. The list is TABLE, INDEX, FUNCTION, MASK, TABLESPACE, SERVER, SEQUENCE, and THRESHOLD.

1. To Add a Column

Here you can add new column for an existing Table. Also, you can modify the existing column data type.

ALTER TABLE table_name ADD COLUMN name_of_column;

2. To Add Index

You cannot add new Index on existing Table. When you want to add new index, you need to drop existing ones and to create new ones.

The REGENERATE specifies that the index will be regenerated or rebuild. This command is useful if an index already created.

ALTER INDEX index_name REGENERATE;

3. You Can ADD Buffer pools

You can modify database parameters. For instance, you can add more buffer-pools. Also, you can add storage or drop storage.

ALTER DATABASE database_name new_buffer_pool;

4. You Can ALTER Criteria of a Function

You can modify the parameters (criteria) of an user defined function.

ALTER FUNCTION GET_TABLE
RESTRICT CARDINALITY 10000;

Summary

Usually, in production, the ALTER statement Admin will use it. But in the test regions, a developer can use ALTER statement.

References

Related Posts

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.