Oracle Performance Analysis 7: Use of indexes

This section focuses on the use of indexes, first describes how to avoid using indexes in queries, then describes how the optimizer determines whether to use indexes, describes how to enforce the use of indexes, and finally introduces the parallel processing methods of Oracle.

Avoid using indexes

Although you have created an index, you may want to avoid using these indexes for some queries, or you might want to take a look at the situation of queries in various situations in order to do some testing and avoid using indexes or index scanning methods. Oracle provides a way to achieve these targets by using hint information in queries, as follows.

Avoid using an index

If the selectivity of the index is poor, it might be more efficient to use a different index or a full table scan, and you might consider avoiding using the index as follows:

/*+ No_index (table_name index_name) */

Indicates that the query does not use TABLE_NAME's index_name index, and here is an instance:

Select/*+ no_index (historyalarm idx_historyalarm$clear) */Position1 from  historyalarm

After the optimizer does not use the specified index, other indexes on the table can still be used, and you can specify that a set of indexes be ignored:

Select/*+ no_index (historyalarm idx_historyalarm$clear,idx_historyalarm$cleargmt) */Position1 from  Historyalarm

If you just specified the NO_INDEX hint without listing any indexes, the optimizer ignores all indexes on the specified table:

Select/*+ no_index (historyalarm) */Position1 from  historyalarm

Avoid quick scans

The syntax is as follows:

/*+ no_index_ffs (table_name index_name) */

Represents a quick scan of an index that does not use TABLE_NAME's index_name index, here is an instance:

Select/*+ no_index_ffs (historyalarm idx_historyalarm$clear) */Position1 from  historyalarm

Force table Scan

Forcing a table scan is also a way to avoid using indexes, as follows:

/*+ Full (table_name) */

Indicates that a full table scan is enforced when querying the table table_name, here is an instance:

Select/*+ Full (historyalarm) */Position1 from  historyalarm

Working with Indexes

The query optimizer calculates cost information using the index to determine whether the index is used, the selectivity of the table (by filtering) multiplied by the index clustering factor (clustering factor), which determines the cost of accessing the table through the index. Let's start with the clustering factor.

Clustering factor

The clustering factor corresponds to the Clustering_factor column of the User_indexes view, which is used to indicate how the index's leaf item corresponds to the row of the table, and the following is the calculation method for the clustering factor:
for each entry in the index, the data block of the table where the Oracle comparison entry resides, and the previous index entry data block, if the data block is different, the clustering factor plus 1, otherwise continue.
Therefore, the minimum value of the clustering factor is equal to the number of data blocks for all entries on the index (because the index on a single column or multiple columns does not contain empty data, so this will be equal to the number of data blocks that contain data in the table), and the maximum value equals the number of entries on the index (each of the two adjacent entries is on a different
As an example, we use the table T1 and T2 (see "Oracle Performance Analysis 4: Full scan of data access Methods"), the following is the clustering factor for index T2 on the lookup table T1 index idx_t1_id and idx_t2_id:

Select I.index_name, i.clustering_factor from  user_indexes i where index_name = ' idx_t1_id '    or index_name = ' idx_ t2_id ' index_nameclustering_factoridx_t1_id17idx_t2_id1700

Because the same data in the table T1 is stored sequentially, the order of index entries and the order of the data are basically consistent, so the T1 table has the lowest clustering factor, and the data in the T2 table is stored on different blocks, so the data for any two adjacent entries in the index entry are on different blocks. Therefore, the clustering factor reaches the maximum value. Therefore, the idx_t1_id index usage of the T1 table is very low, and the idx_t2_id index of the T2 table is expensive to use.
It is important to note that the clustering factor is related to the data in the table and not to the index, so rebuilding the index has no effect on it.

Force the use of indexes

When Oracle's optimizer is unable to select the correct index (the optimizer is not perfect, it does not always accurately understand the nature and distribution of the data), we can consider forcing the use of the index, which describes how to use hint information to enforce the use of the index.
It is important to note that before using the hint, you should look at the correct statistics for the object, and examine how the SQL statement is constructed, suggesting that there should be no way to go.

Using hint information

The index hint instructs the optimizer to use an index scan for a particular table, for example:

Select/*+ Index (historyalarm idx_historyalarm$clear) */Position1 from  historyalarm

This is equivalent to forcing the optimizer to use idx_historyalarm$clear or not specifying an index, when the optimizer chooses the index it considers optimal:

Select/*+ Index (historyalarm) */Position1 from  historyalarm

The flaw in this is that the optimizer may choose the wrong index, so this is usually not a good choice.
If you need to specify multiple indexes in the index hint, you can use the Index_combine hint, as follows;

Select/*+ index_combine (historyalarm idx_historyalarm$01 idx_historyalarm$02) */Position1 from  historyalarm

At this point, the optimizer uses the two lowest-cost combination of the specified index, and if the index list is not provided, the optimizer uses the best combination of indexes based on its cost estimate.
In addition to the hint information described above, there are commonly used hint information related to indexes:
1) INDEX_ASC: Specifies the index range scan in ascending order;
2) Index_desc: Specifies to scan ascending index in descending order, or to scan descending index by ascending;
3) Index_join: When two indexes contain all the columns needed to return the results of the query, use the hint to connect to the index to get all the data directly from the index, with syntax similar to index_combine;
4) INDEX_SS: Perform an index jump scan;
5) Index_ffs: Access data in a fast, full-scan index.

Hint failure

The main causes of hint failure are as follows:
1) The optimizer thinks that using hint will result in incorrect results. This is often due to the fact that there are many null values in the data, so there are some rows that do not appear in the index, causing the index scan to eventually result in incorrect results;
2) The table in the SQL statement specifies an alias, and the alias must also be used in the hint, otherwise the hint will be ignored, such as:

Select/*+ Index (H idx_historyalarm$raise) */Position1 from  historyalarm h

3) The query involves a table connection, based on the optimizer selection of the connection method, cannot use the index. This can often be solved by adding more hints (such as specifying how tables are connected).

Parallel

Parallel processing operations are supported in Oracle, and concepts related to parallel processing are described below.

Parallel

Specifies the degree of parallelism that SQL performs, which overrides the degree of parallelism set by the table itself, such as:

Select/*+ Parallel (H 4) */* from Historyalarm H

Parallel has the following levels:
1) Parallel: Always parallel execution, database computing parallelism, the value is greater than or equal to 2;
2) Parallel (default): Same as parallel;
3) Parallel (auto): The database calculates the degree of parallelism, the value is greater than or equal to 1, when the degree of parallelism is 1 means serial execution;
4) Parallel (manual): the degree of parallelism of the optimizer using the table;
5) Parallel (integer): The optimizer uses the specified degree of parallelism.

Degree of parallelism

Specifying the degree of parallelism when creating a table

CREATE table T1 parallel 4 as Select Trunc ((rownum-1)/ID, rownum value from  dba_source where rownum <= 100 00

View the parallelism of a table

Select degree from user_tables where table_name = ' T1 '

To modify the parallelism of a table

ALTER TABLE T1 parallel (degree 3)

To cancel the parallelism of a table

ALTER TABLE T1 NOPARALLEL

The method above also applies to the index.

No_parallel

The use of parallelism is prohibited in SQL.

Select/*+ No_parallel (h) */* from Historyalarm H

Parallel_index

When working with partitioned indexes, you can instruct the optimizer to use multiple concurrent parallel servers to parallelize the index operations of the type in the agent. Operations that can be parallelized include index range scan, full index scan, and index fast full scan.
The following is a parallel scan operation that specifies the partition index through the PARALLEL_INDEX hint:

Select/*+ Parallel_index (H idx_historyalarm$06 4) */Alarmclearedtime, alarmkey from  historyalarm h

The integer 4 represents the degree of parallelism of the index scan.

No_parallel_index

The use of indexed parallel scanning is prohibited.

Oracle Performance Analysis 7: Use of indexes