Chapter 11-Creating Other Schema Objects ---Index
Indexes是可选项，可有可无；它存在的价值就是improves the performance of some queries.
An index:
- Is a schema object Can be used by the Oracle server to speed up the retrieval of rows by using a pointer
- Can reduce disk input/output(I/O) by using a rapid path access method to locate data quickly
- Is independent of the table that it indexes
- Is used and maintained automatically by the Oracle server.
How are indexes created ?
two methods:
|-Auto
|-Manu Automatically:
A unique index is created automatically when you define a PRIMARY KEY or UNIQUE constraint in a table definition.
Manually:Users can create nonunique indexes on columns to speed up access to the rows.
Index category
|-unique index
|-nonunique index
Creating an index
Create an index on one or more columns:
CREATE [UNIQUE] [BITMAP] INDEX index ON table (column,[,column]...);
Improve the speed of query access to the LAST_NAME column in the EMPLOYEES table:
CREATE INDEX emp_last_name_idx ON employees(last_name);
Index creation guidelines
Create an index when:
- A column contains a wide range of values
- A column contains a large number of null values One or more columns are frequently used togerther in a WHERE cluase or join condition
- The table is large and most queries are expected to retrieve less than 2% to 4% the rows in the table
Do not create an index when:
- The column are not often used as a condition in the query
- The table is small or most queries are expected to retrieve more than 2% to 4% of the rows in the table
- The table is updated frequently
- The indexed columns are referenced as part of an expression.
Removing an index
Remove an index from the data dictionary by using the DROP INDEX command: DROP INDEX index
Remove the emp_last_name_idx index from the data dictionary DROP INDEX EMP_LAST_NAME_IDX;
To drop an index,you must be the owner of the index or have the DROP ANY INDEX privilege.
