Select the appropriate option for Oracle to create an index

Some beginners often do not use the option when creating indexes in Oracle databases. In fact, sometimes you can use some options in a suitable situation to increase the index creation speed. For example, to import data in large batches, we often cancel indexing to increase the insertion speed. Then, after the data is imported, re-create the index. In this process, if you can use some options, you can shorten the index creation time. A wide range of options are provided in the Oracle database. The following are common options.

Option 1: NOSORT, record sorting Option

By default, when an index is created in a table, records in the table are sorted and then indexed. However, when there are many records, This sorting job takes a lot of time, which increases the index creation time (the sorting job is completed in the index creation job ). Sometimes, when we import data, for example, when we use the insert into statement to insert data, we also use the Order by clause to sort the index fields. In this case, if you sort the index again during the index creation process, you will have to put off your pants and fart. To this end, if the data in the table has been sorted (sorted by index field) when you re-create the index, you do not need to re-sort the data when creating the index. At this time, when creating an index, the database administrator can use the NOSORT option to tell the database system that it does not need to re-sort the records in the table.

After this option is adopted, if the records in the table are arranged in order, the index will not be re-ordered when the index is re-created, which can increase the index creation time, saves cache space for sorting in memory. On the contrary, if the records in the table are not sorted by the index keyword, the system will prompt the error message and refuse to create the index if the NOSORT keyword is used. Therefore, when NOSORT is available, the database administrator can use it with confidence. In fact, when this option cannot be used, the database will also explicitly inform you. Therefore, there are few side effects. The database administrator only needs to remove this option and execute it again. However, it should be noted that if there are few records in the table, the effect of using the NOSORT option is not very obvious. When the insert into clause is used to batch import data and the Order by clause is used to sort the index keywords, The NOSORT option is usually used to achieve better results.

Option 2: NOLOGGING, whether to record log information

When creating an index, the system stores the relevant information in the log information. If there are many records in the table, you need to record the information to the log files one by one, which will obviously increase the workload of the database. This increases the index creation time. Therefore, we can use the NOLOGGING option if necessary during index creation so that the database does not generate any redo log information during index creation. In this case, when there are many records in the table, the speed can be significantly improved.

However, by default, this option is not used when the database creates an index, and related information is saved to the redo log. This reduces the efficiency of index creation, but in case of any accidents, you can use the redo log for restoration. Therefore, it is difficult for the database administrator to choose. On the one hand, data security and index creation speed. Based on the author's experience, as long as the database server is relatively stable and the database's constraint mechanism is relatively complete, there will be no problems during the index creation process, you can use this option with confidence.

However, if the database has been in use for several years. Later, the index needs to be re-built for some reason. In this case, the database administrator cannot control many factors during database usage. In this case, we recommend that you do not use this option to create an index for this type of database. This is because the probability of an error is relatively higher. Therefore, it is necessary to improve data security at the expense of index creation rate. In case of any problems, you can redo the log to restore the data in time to reduce losses for enterprise users.

Option 3: compute statistics, whether to generate STATISTICS

If the Administrator uses this option when creating an index, the database will generate statistics about the index at a very small cost during the index creation process, store the information in the data dictionary. This avoids the analysis and statistics of indexes in the future, and the optimizer can randomly use these statistics when optimizing SQL statements to determine whether to generate an execution plan using the index. Generally, when an index is generated, the cost of calculating the index information is the smallest. Both time consumption and hardware resource consumption are very small. Therefore, it is very useful to count the relevant index information during the index creation process.

However, this option is not used by the database by default. This is mainly because the index information of some transaction processing systems often needs to change. If the related information is collected during index creation. This information will soon become obsolete as the index is adjusted. Therefore, this option is not used by default. This option does not work under any circumstances. However, if the database system is a decision support system. Its data and indexes remain stable for a period of time. You can use this option when creating an index. In this case, the statistical information can be generated at the minimum cost during index generation for the convenience of the optimizer. When deploying database applications, I usually do not enable this option for transactional database systems. However, for some decision-making database systems or data warehouses, I like to use this option when creating indexes. This helps improve the performance of the database. This is because the optimizer can directly use this statistical information when generating an execution plan. Therefore, the database can determine the execution plan to be adopted in the shortest time. In addition, the index statistics are referenced in the execution plan formulation. Therefore, the generated execution plan may be more reasonable under the same conditions.

Option 4: ONLINE and DML operations and index creation operations

By default, the database system does not allow DML operations and index creation operations at the same time. That is to say, during index creation, other users are not allowed to perform any DML operations on the tables involved. This is mainly because the base table is locked when you perform DML operations on the base table. Therefore, you cannot create an index on the base table before the DDL transaction of the base table is submitted, that is, the base table is not unlocked. And vice versa. Obviously, this ONLIE option is not used in the database at this time, followed by DML operations and index creation operations at the same time, mainly starting from the efficiency of index creation. Avoid Conflicts between two jobs, thus increasing the running time of a job.

But sometimes we have to allow them to operate simultaneously. If you may not be able to leave the database system for a moment, you need to perform DML operations on the basic database tables at all times. For some reason, when the database administrator needs to re-create an index, the ONLINE option must be added to the index creation statement. Let them run at the same time. At this time, although the index creation time may be extended, it can ensure that the user's DML operations can be performed normally. Sometimes it is worthwhile to sacrifice this price. Users cannot wait, while our database administrator can wait for a while.

Of course, if the user does not have such high timeliness for this DML operation. If the database administrator creates an index when an employee does not use the database at night, this option is not required. While restricting users to DML operations on basic tables, it also improves the efficiency of database index creation.

Option 5: PARALLEL: Create an index for multiple service processes

By default, the Oracle database system does not use this option. This does not mean that this option is unavailable, but because in most cases, the database server used by an enterprise to deploy an Oracle database usually only has a single CPU. In this case, the database system uses a service process to create an index.

If your server has multiple CPUs, you can use this option when creating an index. If this option is used, the database uses multiple service processes to create indexes in parallel to increase the index creation speed. Therefore, it is much faster for multiple services to create indexes concurrently and create indexes for a single service under the same conditions. Therefore, if the server has multiple CPUs and requires many indexes or a large number of records in the basic table, this option can greatly improve the efficiency of index creation.