Indexes

 

A database index is a table that contains data values, along with a corresponding column that specifies the physical locations of the records (ROWID) that contain the associated sorted data values.

You can find rowid column for every record in each table.

 

SELECT rowid, total_hours FROM project_consultant

ORDER BY total_hours;

 

Oracle automatically creates an index on the primary key of the table.

Managing records in an indexed table can be slow process because the DBMS must modify the index every time the record values changed.

 

Creating an Index

 
CREATE INDEX index_name ON tablename (index_column);

Index name should be index_tablename_fieldname.

 

CREATE INDEX project_consultant_total_hours

ON project_consultant (total_hours);

Composite indexes can contain multiple columns that the DBMS can use for identifying the row location.

 

CREATE INDEX index_name ON tablename ( index_column1, index_column2, …);

CREATE INDEX consultant_skill_skill_id_cert

ON consultant_skill (skill_idi certification);

View Index Information Using the Data Dictionary Views

You can retrieve the names of the indexes in your user schema by querying the USER_INDEXES data dictionary view.

 

SELECT index_name

FROM user_indexes;

Dropping an Index

DROP INDEX index_name;

~ by enginzorlu on July 19, 2007.