[Oracle]-[Create an index]-operating principles and experiences of creating an index

1. Read the data of indexkey to the cache. If this part of data is not read to DBCache, dbfilescatterreadwrite wait events may occur. 2. Sort the data of indexkey. If sort_area_size or pga_aggregate_target is not large, disksort may be performed.

1. Read the data of the index key to the cache. If the data is not read to the database Cache, there may be a DB file scatter read write wait event. 2. Sort the data of the index key. If sort_area_size or pga_aggregate_target is not large, disk sort may be performed.

1. Read the data of the index key to the cache. If the data is not read to the database Cache, there may be a DB file scatter read write wait event.

2. Sort the data of the index key. If sort_area_size or pga_aggregate_target is not large, disk sort may be performed. We know that the efficiency of disk sorting is less than that of Cache, so the speed is definitely affected, there will be direct path read/write, and CPU time will also be consumed.

3. Create a new index segment (Supplement: block is the smallest I/O unit, extent is the smallest Oracle space allocation unit, and segment is composed of extent, the storage of tables, indexes, views, and so on in Oracle can be considered as segement), and the sorted index data is written to the created index segment. If the index is large, there will be a lot of redo log waits, such as log file sync, log buffer space, and log file parallel write.

After referring to the normal article,

Experience on creating indexes:

(1) the premise of Using parallelism to create an index is that multiple CPUs and Using parallelism to create an index under a single CPU may cause resource contention, dave once speculated that it may be CPU contention or I/O contention. The result is that it takes more time to create an index than not having to use parallelism.

(2) When creating a large table index, you can increase the PGA and temp tablespace, because sorting is usually performed in the PGA to prevent disk sorting due to insufficient space or memory, is the biggest problem. However, sometimes these parameters are not allowed to be adjusted at will. Therefore, nologging can be implemented in this way.

Experience on using indexes:

(1) index data is sorted at the second point. Using this feature, you can avoid table sorting, for example, to query sorting results such as max or min, you only need to create an index for a field to avoid using order by because the data in the index has been sorted.

(2) Using indexes at the same time can also avoid returning to the table. This method is better for selecting some index fields.

There is little talk about these aspects here. I will discuss them separately later.

Author: bisal posted on 14:18:39 Original article

Read: 73 comments: 0 view comments

Original article address: [Oracle]-[Create an index]-operating principles and experiences of creating an index. Thank you for sharing your thoughts.