TechnicalBits

What is Index?

• Allows users to quickly locate specific records.
• It is a performance-tuning method that is used to speed up retrieval of records by using a pointer. 
• An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.
• Can reduce disk I/O by using rapid path access method to locate the data quickly.
• Is independent of the table it indexes.
• Indexes can be created on a single column or a group of columns. When a index is created, it first sorts the data and then it assigns a ROWID for each row. 
• The users cannot see the indexes, they are just used to speed up searches/queries.

Creating an Index :

CREATE INDEX index_name ON table (column[, column]...);

CREATE INDEX idx_cust_id_ ON customers_info (customer_id);
/*---Index Created.---*/

• The column is used frequently in the WHERE clause or in a join condition.
• The column contains a wide range of values.
• The column contains a large number of null values.
• Two or more columns are frequently used together in a WHERE clause or a join condition.
• The table is large and most queries are expected to retrieve less than 2–4% of the rows.

• The table is small.
• The columns are not often used as a condition in the query.
• Most queries are expected to retrieve more than 2–4% of the rows.
• The table is updated frequently.

DROP INDEX index;

DROP INDEX idx_cust_id;/* Index dropped. */