SQL CREATE INDEX Statement and Types of Indexes

When the user fires a SELECT statement to search for a particular record, the oracle engine must first locate the table on the hard disk. The Oracle engine reads the information and locates the starting location of a table’s record on the current storage media.

The Oracle engine then performs a sequential search to locate records that match user defined criteria. An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows.

When there are thousands of records in a table, retrieving information will take a long time. Therefore indexes are created on columns which are accessed frequently, so that the information can be retrieved quickly. Indexes can be created on a single column or a group of columns

Indexes in databases are very similar to indexes in libraries. Indexes allow locating information within a database fast, much like they do in libraries. If all books in a library are indexed alphabetically then you don’t need to browse the whole library to find particular book. Instead you’ll simply get the first letter from the book title and you’ll find this letter’s section in the library starting your search from there, which will narrow down your search significantly.

So by using the method of INDEX you can save your important time. Oracle also provides a facility of INDEX to perform the fast access of data from the database. Index in sql is created on existing tables to retrieve the rows quickly.

When an index is created, it first sorts the data and then it assigns a ROWID for each row.

 ROWID Format

 An extended ROWID needs 10 bytes of storage on disk and is displayed by using 18 characters. As we know rowed is the physical address of the row. Its format is OOOOOOFFFBBBBBBBRRR. It consists of the following components:

 1. Data object number: It is assigned to each data object, such as table or index when it is created, and it is unique within the database. The number is 000000.

 2. Relative file number: It is unique to each file within a tablespace. Its number is FFF. Data files are the files used by the Oracle engine to store user data.

 3. Block number: It represents the position of the block, containing the row, within the file. Its number is HBBBBBR.

 4. Row number: It identifies the position of the row directory slot in the block header. Its row number is RRR. Each record in the data block is given a unique record number. The unique record number assigned to the first record in each data block is 0.

 Benefits

SQL indexes are used because they can provide the following benefits / functions:

• Rapid access of information

• Efficient access of information

• Enforcement of uniqueness constraints

 Types of Indexes

 Oracle provides four types of Indexes.

Clustered Index

 A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values.

Nonclustered Index 

A nonclustered index can be defined on a table or view with a clustered index or on a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value. The rows in the index are stored in the order of the index key values, but the data rows are not guaranteed to be in any particular order unless a clustered index is created on the table.

 Unique Index

 A unique index ensures that the index key contains no duplicate values and therefore every row in the table or view is in some way unique. Both clustered and nonclustered indexes can be unique.

 Non Unique Index

It does not restrict the duplicate column values from being stored in the table. It improves the performance.

Creating an Index

An Index can be created on a single column or a combination of columns in a database table. A table index is a database structure that arranges the values of one or more columns in a database table in specific order. The table index has pointers to the values stored in specified column or combination of columns of the table. These pointers are ordered depending on the sort order specified in the index.

Syntax: CREATE INDEX [index_name] ON [table_name] (column_namel, column_name2 ...);

From the Syntax, [index_name] is the name given to the Index. [table_name] is the name given to the table on which the index is created. [Column_namel n] are the columns to be used for indexing.

• Take the example of EMPLOYEES (see in View’s topic) where the information is not stored in any order and we want to search Salary of the employee whose name is ‘Jagdish’. Searching will be difficult if we manually search the record by scanning all the rows. So we need indexing to make searching fast. Use the following command to search the record.

SQL> CREATE INDEX RAMAN_DHILLON ON EMPlOYEES(NAME); 
Index Created. 

 Explanation: On execution it will create an index ‘RAMAN_DHILLON’ on the column ‘NAME’. After making the index Oracle will perform the searching fast with more speed. It sorts the Name in ascending order after fetch from each row.

 Creating an Unique Index

 A unique index ensures that the index key contains no duplicate values and therefore every row in the table or view is in some way unique. Both clustered and nonclustered indexes can be unique.

Syntax:CREATE UNIQUE INDEX index_name ON table_name (column_namel, column_name2 ...);
SQl> CREATE UNIQUE INDEX VINOD ON EMPlOYEES(NAME);

 Explanation: It will create a unique index. For a unique index, one ROWID exists for each data value but in a non-unique index the ROWID is included in the key of sorter order.

 Composite Indexes

 When an index is created by using multiple columns then it is known as Composite index .

 • Create an index on the NAME AND SALARY columns of the employees table.

SQL> CREATE INDEX RAMAN_DHILLON ON EMPlOYEES(NAME,SAlARY);
Index created.

 • Create a Unique Composite Index on NAME and SALARY.

SQL> CREATE UNIQUE INDEX RAMAN_DHILLION ON EMPlOYEES(NAME,SALARY);
Index created

Dropping Indexes

The SQL DROP INDEX statement is the SQL command that removes an entire SQL index. You may drop an index permanently when it is no longer useful or temporarily. If the index is harming or not helping performance it could be dropped.

Indexes may slow down the loading of data because they must be maintained during the data load process. For high performance loads, an index could be dropped for the duration of a load and then recreated. You cannot use the DROP INDEX statement to remove an index that has a PRIMARY KEY or UNIQUE constraint. To remove the constraint and then delete the index, use ALTER TABLE with the DROP CONSTRAINT clause.

Syntax: DROP INDEX [INDEX_NAME];
Example: SQL> DROP INDEX RAMAN_DHILLON;

NOTE:

1. Even though sql indexes are created to access the rows in the table quickly, they slow down DML operations like INSERT, UPDATE, DELETE on the table, because the indexes and tables both are updated along when a DML operation is performe, So use indexes only on columns which are used to search the table frequently

2. It is not required to create indexes on table which have less data.

3. In oracle database you can define up to sixteen (16) columns in an INDEX.