Oracle index and oracle index

Oracle index and oracle index

IndexIs a schema object that contains entries of each value. This entry appears in the index column of a table or cluster and provides direct and fast access to rows.

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

Delete index: drop index name

The purpose of index creation is to speed up the query. After the index is created, the DML operation efficiency is slow, but the query efficiency is improved. When you delete a table, the corresponding index is also deleted. In addition, indexes are sorted.

The index is created to reduce physical reads, which reduces the scanning time. The where clause field is frequently used, and the index should be used. In addition, the queried data and the percentage of all data must be viewed. The larger the table, the fewer query records, the efficiency of indexes is higher .;

 

Example:

Create index upper_ix ON employees (UPPER (last_name ));

Usage:

SELECT first_name, last_name FROM employees where upper (last_name) IS NOT NULLOrder by upper (last_name );

To increase the possibility of using indexes in Oracle databases rather than performing full table scans, make sure that the value returned by the function in subsequent queries is not empty. If there is no WHERE clause, the Oracle database may perform a full table scan.

 

Note:

The field condition after the WHERE clause must be consistent with the index, otherwise it may become invalid.

For example:Create index ix_birth mployees (birthday );

Query statement: select birthday from mployees where to_char (birthday, 'yyyy-mm') = '2017-01 ';

Result: The index has no effect.