Mastering Database Indexes: Creation, Usage, and Optimization

1. Creating Indexes

2. Objectives • Distinguish between the indexes that are created automatically and those that are created manually. • Identify the uses for indexes. • Explain the index structure and why it improves query speed. • Create a non-unique index. • Remove an index from the data dictionary. • Evaluate guidelines for creating and using indexes.

3. What Is an Index? • Database object • Used by the Oracle Server to speed up the retrieval of rows by using a pointer • Reduces disk I/O by using rapid path access method to locate the data quickly • Independent of the table it indexes • Automatically used and maintained by the Oracle Server

4. How Are Indexes Created? • Automatically A unique index is created automatically when you define a PRIMARY KEY or UNIQUE constraint in a table definition. • Manually Users can create non-unique indexes on columns to speed up access time to the rows.

5. Creating an Index: Syntax • Create an index on one or more columns. Example • Improve the speed of query access on the ENAME column in the EMP table. CREATE INDEX index ON table (column[, column]...); SQL> CREATE INDEX emp_ename_idx 2 ON emp(ename); Index created.

6. When to Create an Index • 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. • More indexes do not always speed up queries.

7. When Not to Create an Index Do not create an index if • 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.

8. Confirming Indexes • The USER_INDEXES data dictionary view contains the name of the index and its uniqueness. • The USER_IND_COLUMNS view contains the index name, the table name, and the column name. SQL> SELECT ic.index_name, ic.column_name, 2 ic.column_position col_pos,ix.uniqueness 3 FROM user_indexes ix, user_ind_columns ic 4 WHERE ic.index_name = ix.index_name 5 AND ic.table_name = 'EMP';

9. Removing an Index • Remove an index from the data dictionary. • To drop an index, you must be the owner of the index or have the DROP ANY INDEX privilege. SQL> DROP INDEX emp_ename_idx; Index dropped.

10. Summary • Indexes are database objects that are used to improve query retrieval speed. • Some unique indexes are created automatically. • Users can create indexes by issuing the CREATE INDEX command. • The definition of the index is in the USER_INDEXES data dictionary table.

11. Practice Overview • Creating non-unique indexes • Displaying data dictionary information about the index • Dropping indexes

12. Practice 1 • Create a sequence to be used with the primary key column of the DEPARTMENT table. The sequence should start at 60 and have a maximum value of 200. Have your sequence increment by ten numbers. Name the sequence DEPT_ID_SEQ. • Write a script to display the following information about your sequence: sequence name, maximum value, increment size, and last number. Execute your script.

13. Practice 2 • Write an interactive script to insert a row into the DEPARTMENT table. Be sure to use the sequence you created for the ID column. Create a customized prompt to enter the department name. Execute the script and add two departments named Education and Administration. Confirm your additions.

14. Practice 3 • Create a non-unique index on the foreign key column in the EMPLOYEE table. • Display the indexes and uniqueness that exist in the data dictionary for the EMPLOYEE table.