Shulou(Shulou.com)06/01 Report--

If your system needs to create an index on a large table, what factors would you consider?

1) with regard to the creation of indexes for large tables, if you consider multiple dimensions, I will consider:

1. Why do you need to create an index, that is, whether the performance after index creation can be improved?

two。 What kind of index is created, that is, the type of index

3. The impact of index creation, you need to understand the process of index creation, and if you optimize the efficiency of index creation to reduce the impact on the business

4. When will the index be created

5. Is there any other way to replace this process?

2) the following analysis process of several considerations: 1. Why do you need to create an index

a. Combined with the application query scenario, for example, whether there are conditions under which the SQL of the corresponding key business needs to use the relevant fields of the index, and whether the SQL execution frequency is large; the SQL of this part of the business is based on the size of the result set returned by the index, for example, if a table has 100 million, tens of millions of result sets are returned each time according to the corresponding conditions, then whether the index is really valid.

b. Combined with the data distribution of the table, the first is the column data selectivity. If the selectivity is not high, sometimes the cost of the index back to the table is larger than that of the full table scan, and the optimizer may choose the execution plan path without selecting the index. The data of the second column is relatively unordered, resulting in a higher cluster factor after the creation of the index, increasing the cost of returning the index to the table, and so on.

c. Combined with the changes of table data, adding an index requires the maintenance of one more index. For a table with frequent data changes, too many indexes will increase the cost of index maintenance in dml operations, especially in insert, and affect the efficiency of execution.

two。 What kind of index is created?

a. You can create a composite index if you use multiple conditions

b. If the business sql condition has a function, then consider the function index

c. If a column has a low base, do you consider selecting a bitmap index, provided that its column data is rarely updated

d. Whether the partitioned table creates a local index

3. The impact of index creation

We all know that creating an index interacts with dml operations, as well as a large number of IO operations.

First of all, it is to understand the process of creating an index in order to solve the problems caused by creating an index. I did an experiment on creating an index and tracked it through 10046. The main process of creating an index is as follows:

a. Start reading data dictionaries such as statistics, object information, etc.

b. Use share mode nowait to lock the table so that other sessions can only read the table but cannot modify it.

c. After reading some information and so on, initialize the index object information in obj$

d. Start a series of IO operations such as fetching table data, which takes a relatively long time:

e. Insert the corresponding information into the information table such as seg$,icol$,ind$

Complete the whole index sort, create the index, scan the table

From the above index creation process, the problem points that we can consider are summarized as follows:

a. This process will add shared locks and block dml operations, so when creating an index, try to choose a business idle period, and you can also consider online creation.

b. This process requires a large number of read table data and sort operations, as well as insert update operations, at which point storage IO performance needs to be considered.

c. This process produces a lot of redo, so you can consider the nologing pattern.

d. At the same time, we need to consider the space size of the index, and pay attention to the table space and temporary table space.

e. At the same time, we can use parallelism to speed up operations, which we often do.

4. When will the index be created

Don't think about it, business leisure period

5. Is there any other way to replace this process?

You can consider the way of data migration, create a new table and add an index, and then move in the rename table online.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.