Oracle SQL Optimized index monitoring

1. Monitor whether the index is used

In addition to the primary key being an integrity constraint and automatically becoming indexed, the purpose of creating a normal index is to improve the query speed, and if we create indexes that are not used, then these unused indexes will play a role in blocking performance.

Grammar:

  -- Check if an index is being used  Alter Index index_name monitoring usage;   -- Check all index usage  Select *  from v$object_usage;   -- to delete an index  that is not used Drop index index_name;

2. Factors that affect the effective use of indexes

Index is a double-edged sword, the correct use can greatly improve the query efficiency, but the wrong use will also cause serious performance problems.

(1) Function operation or operation of the index field;

(2) Incorrect use of indexed field type. The reason is the same as (1).

Example, user_id is of type int and is an indexed field

  where user_id = ' 1285 ' ;   -- the database will default to convert statements to  where to_char (user_id='1285' )

　　

Oracle SQL Optimized index monitoring