INDEXES
Indexing a table is an ‘access strategy’, that is, a way to sort and search records in the table. Indexes are essential to improve the speed with which the record/s can be located and retrieved from the table.
An index is a schema object
Is used by oracle server to speed up the retrieval of rows by using a pointer
Is independent of the table it indexes
Is used and maintained automatically by the oracle server.
ADDRESS FIELD IN THE INDEX
The address field of an index is called ROWID. ROWID is an internal generated and maintained, binary value, which identifies a record. The information in the ROWID columns provides Oracle engine the location of the table and a specific record in the Oracle database.
The ROWID format used by the Oracle is as follows:
BBBBBBB.RRRR.FFFF
CREATION OF SIMPLE DUPLICATE INDEX
SYNTAX: -
CREATE INDEX index_name ON tablename(columnname);
EXAMPLE: -
SQL> CREATE INDEX client_name ON banking1 (name);
Index created.
DROPING OF EXISTING INDEXES
When an index is no longer needed in the database, the developer can remove it with the use of the DROP INDEX command. If the index is used in relation to primary-key or unique constraint, then the index will no longer continue to enforce that uniqueness constraint.
SYNTAX: -
DROP INDEX index_name;
EXAMPLE: -
SQL> DROP INDEX client_name;
Index dropped.
Indexing a table is an ‘access strategy’, that is, a way to sort and search records in the table. Indexes are essential to improve the speed with which the record/s can be located and retrieved from the table.
An index is a schema object
Is used by oracle server to speed up the retrieval of rows by using a pointer
Is independent of the table it indexes
Is used and maintained automatically by the oracle server.
ADDRESS FIELD IN THE INDEX
The address field of an index is called ROWID. ROWID is an internal generated and maintained, binary value, which identifies a record. The information in the ROWID columns provides Oracle engine the location of the table and a specific record in the Oracle database.
The ROWID format used by the Oracle is as follows:
BBBBBBB.RRRR.FFFF
CREATION OF SIMPLE DUPLICATE INDEX
SYNTAX: -
CREATE INDEX index_name ON tablename(columnname);
EXAMPLE: -
SQL> CREATE INDEX client_name ON banking1 (name);
Index created.
DROPING OF EXISTING INDEXES
When an index is no longer needed in the database, the developer can remove it with the use of the DROP INDEX command. If the index is used in relation to primary-key or unique constraint, then the index will no longer continue to enforce that uniqueness constraint.
SYNTAX: -
DROP INDEX index_name;
EXAMPLE: -
SQL> DROP INDEX client_name;
Index dropped.