INDEX:Adatabase indexis adata structurethat improves the speed of data retrieval operations on adatabase table. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or morecolumns of a database table, providing the basis for both rapid randomlookupsand efficient access of ordered records.Indexes are used to retrieve data from the database very fast. The users cannot see the indexes, they are just used to speed up searches/queries.CREATE INDEX SyntaxCreates an index on a table. Duplicate values are allowed:CREATE INDEX index_nameON table_name(column1, column2, ...);CREATEINDEXidx_pnameONPersons (LastName, FirstName);DROP:DROPINDEXindex_name;Creating a new index– show you how to use the CREATE INDEXstatement to createan index for one or more columns in a table.Removing an index– learn how to remove an existing index by using the DROP INDEXstatement.Unique index– use unique indexes to enforce the uniqueness of values in the index column or columns.Function-based index– speed up queries that involve expression which consists of functions.Bitmap index– use bitmap index on a column or columns that have few distinct values, or low cardinality.Creating indexes on columns in WHERE clauses or JOINs is a good idea.If a column has a large percentage of unique records, a b-tree index is usually more suitable.If a column has a low percentage of unique records,a bitmap index is usually better.
Consider creating an index on foreign key columns (which are often used in joins anyway), as they are not created automatically (in Oracle anyway).Consider creating a function-based index if the column in your WHERE clause uses a function or expression in the criteria.