Transcript 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.
• Evaluate guidelines for creating and using
indexes.
15-2
Database Systems
What Is an Index?
•
•
Database object
•
Reduces disk I/O by using rapid path access
method to locate the data quickly
•
•
Independent of the table it indexes
Used by the Oracle Server to speed up the
retrieval of rows by using a pointer
Automatically used and maintained by the Oracle
Server
15-3
Database Systems
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.
15-4
Database Systems
Creating an Index: Syntax
•
Create an index on one or more columns.
CREATE INDEX index
ON table (column[, column]...);
Example
• Improve the speed of query access on the
ENAME column in the EMP table.
SQL> CREATE INDEX emp_ename_idx
2 ON
emp(ename);
Index created.
15-5
Database Systems
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.
•
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.
The column contains a large number of null
values.
15-6
Database Systems
When Not to Create an Index
Do not create an index if
•
•
The table is small.
•
Most queries are expected to retrieve more than
2–4% of the rows.
•
The table is updated frequently.
The columns are not often used as a condition in
the query.
15-7
Database Systems
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';
15-8
Database Systems
Removing an Index
•
Remove an index from the data dictionary.
SQL> DROP INDEX emp_ename_idx;
Index dropped.
•
To drop an index, you must be the owner of the
index or have the DROP ANY INDEX privilege.
15-9
Database Systems
Summary
•
Indexes are database objects that are used to
improve query retrieval speed.
•
•
Some unique indexes are created automatically.
•
The definition of the index is in the
USER_INDEXES data dictionary table.
Users can create indexes by issuing the CREATE
INDEX command.
15-10
Database Systems
Practice Overview
•
•
Creating non-unique indexes
•
Dropping indexes
Displaying data dictionary information about the
index
15-11
Database Systems
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.
15-12
Database Systems
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.
15-13
Database Systems
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.
15-14
Database Systems