1 / 14

Creating Indexes

Creating Indexes. 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.

davidhgreen
Download Presentation

Creating Indexes

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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.

More Related