Usage modes of Oracle Indexes

Usage modes of Oracle Indexes

For some large SQL statements, most tuning scenarios have the feeling of being cool-fed. If the data is screened by millions or tens of millions, full table scan is an extremely resource-consuming process. However, if an index scan is performed, the performance may be improved by hundreds of times. The index access modes are as follows. In fact, you may not pay much attention to some details. Different application scenarios can be targeted and the efficiency may be higher.

Oracle Index instance description-Basics

Oracle | PL/SQL Unique index (Unique Constraint) Usage

Example of Oracle full-text index performance advantages

Restore non-critical Oracle files, redo, temporary files, index files, and password files

Oracle index tablespace data file loss and Reconstruction

Oracle implements function-based indexing

Oracle index Suppression

Oracle index reconstruction script

You can create the following test tables to summarize them.

SQL> create table a as select object_id, object_name, object_type from dba_objects;
Table created.

SQL> desc
Name Null? Type
-------------------------------------------------------------------------------------------------
OBJECT_ID NUMBER
OBJECT_NAME VARCHAR2 (128)
OBJECT_TYPE VARCHAR2 (19)

SQL> analyze table a compute statistics;
Table analyzed.

SQL> create unique index ind_a on a (object_id); -- we have created a unique index.
Index created.

SQL> set autot traceonly exp

View the execution plan and use index uniqe scan, which is the fastest index access mode.

We only output the value of the index column. The result is expected to be indexed, but the result is full table scan to see why.

We only need to modify the attributes of some columns to eliminate the interference of null and perform index scanning. At this time, we need to perform a quick full index scan. This index scan does not involve sorting, so it is faster.

If you want to sort index columns, you can use full index scanning. The following execution plan shows the differences between quick scan and Full scan.

If the interval value of the index column is involved, you can use the interval scan. For example, the commonly used between condition will go through the interval scan.

Skip index scanning may be slightly difficult to understand.

You can give a simple example to simulate it. We create a table a and tilt the data distribution of some fields.

SQL> drop index ind_a;

Index dropped.

SQL> create index ind_a on a (object_type, object_id, object_name );

Index created.

SQL> analyze table a compute statistics for all indexed columns;

Table analyzed.

SQL> select object_id from a where object_type = 'index partition' and rownum <2; -- we randomly extract a record for testing. Object_id is 5639

OBJECT_ID

----------

5639

The data distribution is as follows.

When object_id is used for query, the Skip index scan is performed. Although the index column is (object_type, object_id, object_name), object_id can be used to screen a very small proportion of data.