Index In sql :- An index is a schema object that can speed up the retrieval of rows by using a pointer. Indexes can be created explicitly or automatically. If we do mot have an index on the column, then a full table scan will occur. An index provides direct and fast access to rows in a table.
Its purpose is to reduce the necessity of disk I/O by using an indexed path to locate data quickly. The index is used and maintained automatically by the Server. Once an index is created, no direct activity is required by the user. Indexes are logically and physically independent of the table they index . This means that they can be created or dropped at any time and have no effect on the base tables or other indexes. When we drop a table, corresponding indexes are also dropped.
Types Of Index In SQL -: There are two types of indexes in SQL.
- unique index-: The Server automatically creates this index when we define a column in a table to have a PRIMARY KEY or a UNIQUE KEY constraint. The name of the index is the name given to the constraint.
- Non unique index. Another types of index is non unique index as like When we can create a FOREIGN KEY column index for a join in a query to improve retrieval speed.
Creating an Index:-Create an index on one or more columns by issuing the CREATE INDEX statement as like
CREATE [UNIQUE ] INDEX index ON table (column [, column ] …);
In the Syntax
unique Server enforces unique integrity constraints in the table as a
constraint.
index is the name if the index.
table is the name of the table.
column is the name of the column in the table to be indexed.
CREATE INDEX emp_ename_idx ON emp(ename);
Index created.
The index in the above example do not enforce uniqueness i.e. the column included in the index can have duplicate values. To create a unique index, the keyword UNIQUE should be included in the CREATE INDEX statement.
CREATE UNIQUE INDEX emp_job_idx ON emp(job);
When the user defines a primary key or a unique key constraint, Server automatically creates the unique indexes on the primary key column or unique key. To create an index in our schema, we must have CREATE INDEX privileges.
More is Not Always Better:-More indexes on a table does not mean it will speed up queries. Each DML operation that is committed on a table with indexes means that the indexes must be updated. The more indexes we have associated with a table, the more effort the Server must make to update all the indexes after a DML.
When to Create an Index;
1. The column is used frequently in the WHERE clause or in a join condition.
2. The column contains a wide range of values.
3. The column contains a large numb er of null values.
4. Two or more columns are frequently used together in a WHERE clause or join condition.
5. The table is large and most queries are expected to retrieve less than 2-4% of the rows.
When Not to Create an Index
1. The table is small.
2. The columns are not often used as a condition in the query.
3. Most queries are expected to retrieve more than 2-4% of the rows.
4. The table is updated frequently. If we have one or more indexes on a table, the DML statements that access the table take more time.
Note:
1. Null values are not included in the index.
2. To optimize joins, we can create an index on the FOREIGN KEY column, which will speed up the search to match rows to the PRIMARY KEY column.
3. The optimizer does not use an index if the WHERE clause contains the IS NULL expression.
Function based Index:-
Function based indexes defined with the UPPER(column_name) or LOWER(column_name) keyword allow case insensitive searches. For example the following index:
CREATE INDEX uppercase_inx ON emp(UPPER(ename));
Facilitates processing queries such as :
SELECT * FROM emp WHERE UPPER(ename) =’KING’;
Removing an Index:- We cannot modify indexes. To change an index, we must drop it and then recreate it. Remove an index definition from the data dictionary by issuing the DROP INDEX statement. To drop an index, we must be the owner of the index or have the DROP ANY INDEX privilege.
DROP INDEX index;
DROP INDEX emp_ename_idx;
Index dropped.
By this way we can drop an Index easily.