Oracle Indexing Learning

To view the execution status:

Select the code to press F5 directly, or click Tools===>>explain Plan

I. Considerations for indexing:

Indexes are useful when any single query retrieves less than or equal to 10% of the total number of rows in the table. This means that the candidate column for the index should be a column that stores a large range of values.

A good index candidate is a column that contains only a single number for each record; The poor index candidates are those columns that contain only a small range of numeric codes.

Second, create an index

Create [Unique] IndexIndex_name onTABLE_NAME (colum_name[, Colum_name ...]) tablespace table_space;--unique The value of the specified index column must be unique--index_name specifying the index name--table_name specifies which table to create an index on--column_name specifies which column to create an index on. We can create indexes on multiple columns, which are called conforming indexes--table_space Specifies the table space where the index is stored. If no tablespace is provided, then the index is stored in the user's default tablespace

Attention:

For performance reasons, you should typically store indexes and tables in different table spaces

Third, modify the index

Alter Index  to index_name_new -- index_name the original index name -- index_name_new the new index name

Iv. Deleting an index

Drop Index index_name -- index_name The name of the index

Oracle Indexing Learning