Oracle Performance Analysis 7: Use of indexes and oracle performance indexes

Oracle Performance Analysis 7: Use of indexes and oracle performance indexes

This section describes how to use indexes. First, it describes how to avoid using indexes in queries, then how to determine whether the optimizer uses indexes, and how to forcibly use indexes, finally, the parallel processing method of Oracle is introduced.

Avoid using Indexes

Although you have created indexes, you may need to avoid using these indexes for some queries, or you want to check the query conditions in various situations for some tests, we also hope to avoid using some indexing or index scanning methods. Oracle provides a way to achieve these goals, that is, to use hint information in the query, the specific situation is as follows.

Avoid using an index

If the index selection is poor, it may be more efficient to use other indexes or use full table scanning. You can avoid using this index as follows:

/*+ no_index(table_name index_name)*/

It indicates that the query does not use the index_name index of table_name. The following is an example:

select /*+ no_index(historyalarm idx_historyalarm$clear)*/ position1  from historyalarm

After the optimizer does not use the specified index, it can still use other indexes on the table. You can also specify to ignore a group of indexes:

select /*+ no_index(historyalarm idx_historyalarm$clear,idx_historyalarm$cleargmt)*/ position1  from historyalarm

If you only specify the no_index prompt without listing any indexes, the optimizer will ignore all indexes of the specified table:

select /*+ no_index(historyalarm)*/ position1  from historyalarm

Avoid quick Scanning

Syntax:

/*+ no_index_ffs(table_name index_name)*/

It indicates a quick scan of indexes that do not use the index_name index of table_name. The following is an example:

select /*+ no_index_ffs(historyalarm idx_historyalarm$clear)*/ position1  from historyalarm

Force table Scan

Forced table scan is also a way to avoid using indexes, as shown below:

/*+ full(table_name)*/

It indicates that full table scan is required when table 'table _ name' is queried. The following is an example:

select /*+ full(historyalarm)*/ position1  from historyalarm

Use Index

During the query, the optimizer calculates the cost of using the index to determine whether to use the index. The table selectivity (by filtering) is multiplied by the index clustering factor ), it determines the cost of accessing the table through the index. Next we will start with the poly factor.

Poly factor

The clustering factor corresponds to the CLUSTERING_FACTOR column in The user_indexes view, which is used to indicate how the leaf items of the index correspond to the rows in the table. The calculation method of the clustering factor is as follows:
 For each entry in the index, the data block of the table where the Oracle comparison entry is located is the same as the data block of the previous index entry. If it is a different data block, the clustering factor plus 1, otherwise, continue.
Therefore, the minimum value of the clustering factor is equal to the number of data blocks corresponding to all entries on the index (because the index on a single or multiple columns does not contain null data, therefore, this is equal to the number of data blocks that contain data in the table, and the maximum value is equal to the number of entries on the index (each two adjacent entries are on different data blocks ).
The following is an example. We use tables T1 and T2 (see "Oracle performance analysis 4: Full scan of data access methods "), the following is the clustering factor used to find the index IDX_T1_ID on table T1 and the index IDX_T2_ID on table T2:

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 table T1 is stored in order, the order of index entries is basically the same as that of data storage. Therefore, the clustering factor of table T1 reaches the lowest value; in table T2, the same data value is stored in different data blocks. Therefore, the data of any two adjacent entries in the index entries is stored in different blocks, therefore, the aggregation factor reaches the maximum value. Therefore, the IDX_T1_ID index cost for table T1 is very low, while for table T2, The IDX_T2_ID index cost is very high.
Note that the clustering factor is related to the data in the table rather than the index, so rebuilding the index has no effect on it.

Use index forcibly

When the Oracle optimizer cannot select the correct index (the optimizer is not perfect, and it does not always know the nature and distribution of data accurately), we can consider forcing the use of indexes, the following describes how to use hint information to forcibly use indexes.
Note that before using the prompt, you should first check whether the correct statistical information of the object exists and check the construction method of the SQL statement. The prompt should be optional when there is no way to go.

Use hint Information

The index prompt indicates that the optimizer uses index scanning for a specific table, for example:

select /*+ index(historyalarm idx_historyalarm$clear)*/ position1  from historyalarm

This is equivalent to forcing the optimizer to use idx_historyalarm $ clear. You can also choose not to specify the index. Then the optimizer selects the index that it considers optimal:

select /*+ index(historyalarm)*/ position1  from historyalarm

The disadvantage of 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 prompt, you can use the index_combine prompt, as shown below;

select /*+ index_combine(historyalarm idx_historyalarm$01 idx_historyalarm$02)*/ position1  from historyalarm

In this case, the optimizer uses two combinations with the lowest cost for the specified index. If the index list is not provided, the optimizer uses the best combination of indexes based on its cost estimation.
In addition to the hint information described above, there are also common index-related hint information:
1) index_asc: Specify index range scanning in ascending order;
2) index_desc: Specify to scan the index in ascending or descending order;
3) index_join: When two indexes contain all the columns required to return the query results, use this prompt to connect to the index to directly obtain all the data from the index. The syntax is similar to index_combine;
4) index_ss: performs an index skip scan;
5) index_ffs: quickly and fully scans indexes to access data.

Hint invalid

The main cause of hint failure is as follows:
1) The optimizer considers that using hint will lead to incorrect results. This is often because the data contains many null values, so there are some rows that will not appear in the index, resulting in index scanning may eventually lead to incorrect results;
2) if an alias is specified for a table in an SQL statement, aliases must be used for hint. Otherwise, hint will be ignored, for example:

select /*+ index(h idx_historyalarm$raise)*/ position1  from historyalarm h

3) the query involves table connections. indexes cannot be used based on the optimizer-selected join method. In this case, you can add more prompts (such as specifying the table connection mode ).

Parallel

Oracle supports parallel processing. The following describes concepts related to parallel processing.

Parallel

Specify the degree of parallelism for SQL Execution. This value overwrites the degree of parallelism set by the table itself, for example:

select /*+ parallel(h 4)*/ * from historyalarm h

Parallel has the following levels:
1) parallel: it is always executed in parallel. The degree of parallelism in database computing is greater than or equal to 2;
2) parallel (default): Same as parallel;
3) parallel (auto): database computing concurrency. The value must be greater than or equal to 1. When the degree of parallelism is 1, it indicates serial execution;
4) parallel (manual): The optimizer uses the degree of parallelism of the table;
5) parallel (integer): The optimizer uses the specified degree of parallelism.

Degree of Parallelism

Specify the degree of parallelism when creating a table

create table t1 parallel 4 as select trunc((rownum - 1) / 100) id, rownum value  from dba_source where rownum <= 10000

View the degree of parallelism of a table

select degree from user_tables where table_name = 'T1'

Modify the degree of parallelism of a table

alter table t1 parallel(degree 3)

Cancel the degree of parallelism of a table

alter table t1 noparallel

The above method also applies to indexes.

No_parallel

Parallel Execution is prohibited in SQL.

select /*+ no_parallel(h)*/ * from historyalarm h

Parallel_index

When processing the partition index, You can instruct the optimizer to use multiple concurrent servers to parallelize the index operations in the agent. Parallel operations include index range scanning, full index scanning, and quick and full index scanning.
The following shows the parallel scan operation for specifying the partition index through the parallel_index prompt:

select /*+ parallel_index(h idx_historyalarm$06 4)*/ alarmclearedtime, alarmkey  from historyalarm h

Integer 4 indicates the degree of parallelism of index scanning.

No_parallel_index

Parallel Indexing is prohibited.

Oracle index problems, deleting and re-indexing and Index Analysis

1. It should be feasible. Will it save time and try it.

2. About 400 thousands or 500 thousands of data is stored every month, and only the data of the last four months is saved.

It takes about three or four hours to create these seven indexes each time;

It takes too long to rebuild the index for 2 million of the data. It is strange.

3. It is estimated that the index will be dropped first, and then the create index will avoid the impact of the index on the insertion efficiency when data is inserted.

Oracle index problems

It is not a simple question for you to understand why it remains the same. First, you can create an appropriate index, which is not just a B-tree index.
Index category:
1. B-tree
2. unique index unique
3. Compound
4. Functions
5. Bitmap
6. Anti-Key
7. local and global
1. The table's primary key and foreign key must have an index;
2. Indexes should be available for tables with more than 300 of the data volume;
3. For tables that are often connected to other tables, indexes should be created on the connection fields;
4. fields that frequently appear in the Where clause, especially those in large tables, should be indexed;
5. The index should be built on highly selective fields;
6. Indexes should be built on small fields. Do not create indexes for large text fields or even extra-long fields;
7. The establishment of a composite index requires careful analysis. Try to replace it with a single field index:
A. correctly select the primary column field in the composite index, which is generally A highly selective field;
B. Do the fields of the composite index often appear in the Where clause in the AND mode? Are there very few or even no single-field queries? If yes, a composite index can be created; otherwise, a single field index is considered;
C. If the fields contained in the composite index are often contained in the Where clause, they are divided into multiple single-field indexes;
D. If the composite index contains more than three fields, consider the necessity and reduce the composite fields;
E. If there are both single-field indexes and composite indexes on these fields, you can delete composite indexes;
8. Do not create too many indexes for tables that frequently perform data operations;
9. Delete useless indexes to avoid negative impact on the execution plan;
The above are some general judgment bases for indexing. In a word, the establishment of indexes must be careful. The necessity of each index should be carefully analyzed and a basis should be established. Because too many indexes and inadequate or incorrect indexes have no benefits for performance: Each index created on a table increases storage overhead, indexes also increase processing overhead for insert, delete, and update operations. In addition, too many composite indexes generally have no value when there is a single-field index. On the contrary, they also reduce the performance when data is added and deleted, especially for frequently updated tables, the negative impact is greater.

In addition, I want to know why you need to paste your SQL statement? It also depends on the execution plan to achieve the best optimization.