Constraints are Business rules that are implemented while entering data into the database. For Example, Employee Name cannot be numeric. Telephone number cannot be character data or Social Security Number cannot be NULL.
Once created, constraints can be dropped, disabled (temporarily not enforced), enabled (enforced again), or renamed. Make these changes to constraints using an ALTER TABLE statement. Care must be taken in disabling UNIQUE or PRIMARY KEY constraints because disabling these constraints results in the supporting index being dropped (unless "KEEP INDEX" is also specified).
To drop a constraint, use an ALTER TABLE statement with the constraint name, like this:
ALTER TABLE employees DROP CONSTRAINT validate_hire_date;
Because there can be only one PRIMARY KEY constraint on a table, you can drop it by simply specifying DROP PRIMARY KEY without actually using the constraint’s name. If FOREIGN KEY constraints reference your PRIMARY KEY or UNIQUE constraint, you need to drop these dependent constraints before or in conjunction (using the CASCADE keyword) with the PRIMARY KEY constraint:
ALTER TABLE employees DROP PRIMARY KEY CASCADE;
To rename a constraint, give the old and new names:
ALTER TABLE employees RENAME CONSTRAINT validate_hire_date TO hire_date_check;
When bulk loading data into a table, it is often more efficient to disable FOREIGN KEY and CHECK constraints, load the data, and then re-enable these constraints, like this:
ALTER TABLE employees DISABLE CONSTRAINT mgr_emp_fk; -- bulk load the table
ALTER TABLE employees ENABLE CONSTRAINT mgr_emp_fk;
Disabling either a PRIMARY KEY or a UNIQUE constraint may drop the supporting index and therefore may not be desirable for a load process that uses that index.
Indexes are optional data structures built on tables. Indexes can improve data retrieval performance by providing a direct access method instead of the default full table scan retrieval method. You can build Btree or bitmap indexes on one or more columns in a table. An index key is defined as one data value stored in the index. A Btree index sorts the keys into a binary tree and stores these keys together with the table’s ROWIDs. In a bitmap index, a bitmap is created for each key. There is a bit in each bitmap for every ROWID in the table, forming the equivalent of a two-dimensional matrix. The bits are set if the corresponding row in the bitmap exists.
Btree indexes are the default index type, can be unique or non-unique, and are appropriate for medium-to-high cardinality columns—those having many distinct values. Btree indexes support row-level locking and so are appropriate for multiuser, transactional applications. The indexes supporting a PRIMARY KEY or UNIQUE constraints are Btree indexes.
Bitmap indexes, on the other hand, are best for multiple combinations of low- to medium-cardinality columns (you cannot create a unique bitmap index), and they do not support row-level locking. Bitmap indexes are best in environments in which changes to data are limited and controlled, such as many data warehousing applications. Because bitmap indexes cannot efficiently make changes to the indexed data, they are often dropped prior to data loading and then re-created after a data load.
To create an index, you first need a table. In the CREATE INDEX statement, you tell the database the name of the new index, which table to create the index on, and which columns to include. If multiple columns will be included, use a comma-delimited list. To create a Btree index on the DEPT_NBR column of the EMPLOYEES table used in the preceding sections, use a CREATE INDEX statement, like this:
CREATE INDEX emp_dept_nbr ON employees (dept_nbr) TABLESPACE indx;
A unique index requires the additional keyword UNIQUE, like this:CREATE UNIQUE INDEX dname_uix ON departments (dept_name);
If you frequently access employees by seniority, you can create a multicolumn index on both department number and hire date, like this:
CREATE INDEX emp_seniority ON employees (dept_nbr, hire_date) TABLESPACE indx;
To create three single-column bitmap indexes on the STATE, REGION, and METRO_AREA columns of the GEOGRAPHY table, execute the following:
CREATE BITMAP INDEX state_bix ON geography (state);
CREATE BITMAP INDEX region_bix ON geography (region);
CREATE BITMAP INDEX metro_bix ON geography (metro_area);
To drop an index, use a DROP INDEX statement, like these:
DROP INDEX emp_seniority; DROP INDEX state_bix;
You can perform several maintenance actions on an index, including rebuilding an index, moving it to a new tablespace, coalescing it, or renaming the index. All these actions are performed with different clauses of an ALTER INDEX statement. To rebuild an index, which will shrink its size and possibly reduce the Btree depth (making it more efficient), use a REBUILD clause, like this.
ALTER INDEX emp_seniority REBUILD;
To move an index from one tablespace to another, specify a new tablespace in conjunction with REBUILD:
ALTER INDEX uniq_payroll_id REBUILD TABLESPACE hr_indx;
Coalescing an index is like a quick-and-dirty rebuild. Instead of re-creating the Btree, a coalesce combines entries in nearby leaf blocks with the intent of freeing space from some of the leaf blocks. The space and resources required for a coalesce is much less than the full rebuild. But when you coalesce an index, you cannot move the index or reduce the depth of the Btree. Here is an example of coalescing an index:
ALTER INDEX uniq_payroll_id COALESCE;
Renaming an index is beneficial if you have a poorly named index. It also comes in handy if you need to take care of some high-availability maintenance actions that include staging the new version of an index using a temporary name and then in a short deployment window renaming the old and new indexes. Here is an example of renaming an index:
ALTER INDEX sys_c001428 RENAME TO employee_pk;