Oracle Indexes Index

An index is a schema object that contains an entry for each value that appears in the Index column of a table or cluster and provides direct, quick access to the row.

Creating an index: Create index index name on table name (field name);

DROP INDEX: Dropping index name

The purpose of indexing is to speed up queries, and indexing will make DML operations slow, but will improve efficiency for user queries. When you delete a table, the corresponding index is also deleted. In addition, the indexes are sorted.

The index is created to reduce physical reads, and the index reduces the time to scan. In the fields that often use the WHERE clause, the index should be used, as well as the percentage of the data being queried and the total data, the larger the table, the fewer records are queried, and the more efficient the index is.

Example:

CREATE INDEX Upper_ix on Employees (upper (last_name));

Use:

SELECT first_name, last_name from Employees WHERE UPPER (last_name) are not NULL for ORDER by UPPER (last_name);

to increase the likelihood that an Oracle database uses an index instead of performing a full table scan, ensure that the value returned by the function in subsequent queries is not NULL. If there is no WHERE clause, the Oracle database may perform a full table scan.

Precautions:

The field condition after the where should be consistent with the index, otherwise it may fail.

such as: CREATE INDEX ix_birth mployees (birthday);

Query statement: Select Birthday from mployees where To_char (birthday, ' yyyy-mm ') = ' 2018-01 ';

Result: Index has no effect

Oracle Indexes Index