Oracle database indexes created to do three appropriate

Jan 18 posted in Oracle Database (RSS), 66 views.

In the Oracle database, create an index is a relatively simple. However, to a reasonable index is relatively difficult to create. The author believes that when creating an index appropriate to do three, namely, in the appropriate table, the appropriate number of columns to create the appropriate indexes. While this can be summed up in one phrase index optimization of the basic principles, but to do that, then database administrators need to make great efforts. Specifically, do you need to have the following three due to several demands.

1, according to the size of the table to create the index.

While creating an index to the table can improve query efficiency. However, the database administrator should be noted that the index also requires a certain overhead. This is not to say to all of the tables to create an index, then you can improve database performance. This perception is wrong. On the contrary, if the just-do, to all of the tables are created the index, then the performance of the database but will have a negative impact. Because at this time the cost of misuse of the index may have been far greater than the resulting performance gains. So I believe that the database administrator first needs to be done, in order to fit the table to build an index, rather than indexing all of the tables.

In general, do not need to create an index on the table is relatively small. Such as an ERP system, database, department tables used to store information on the business sector. The part of the general business also more than a dozen up to no more than 100. Records for the 100 people who may be relatively more. But for the computer, it gave him sese teeth are not enough. So, for similar small table does not need to create an index. Because even if the index was established, its performance will not be greatly improved. In contrast the cost of indexing, such as the maintenance costs, etc., than this should be great. In other words, than get paid more than a clear violation of common sense.

Another is for large tables, not necessarily to build the index. Some of the table is a relatively large number of records very many. However, this time for this table indexing and a certain degree of fit. If the system has a table, which is mainly used to save some of the changes to the information in the database. This information is often only to database administrators. At this point for this table, then indexing, but inappropriate. Because this form rarely used only in trouble when it need to see. Second, even if the view of its need to check the record does not, there may be the recent week's update records and so on. For some large tables, indexing is sometimes and often can not achieve the expected results. But also in playing table indexing the cost of its index larger than the average over the table. So in the end it does to a large table indexing? In my view, the key is how the two aspects. First, the need to look at this big table, often need to query the number of records. Generally speaking, if you frequently need to query the data does not exceed 10% to 15%, then there is no need for indexing necessary. Because at this time may be greater than the cost of indexing to improve the performance of the multi-. This ratio is only an experience of data. If the database administrator will need to arrive at a more precise conclusion, then we need to conduct test analysis. Namely, a database administrator will need to test the full table scan of time, to see if the query than the index after the longer or shorter. If it is long, then illustrate the need for indexing. But if not, then the whole table scan or fast speed. At this point there is no need to establish an index of.

In short, in considering whether the indexing for the table, the generally small table does not need indexing. As for the playing table, then the need for practical analysis of the actual situation. To put it simply, it may, the ratio of general to determine. If you want to exact a little, you can full-table scan performance analysis, to determine the index as expected, after it really improved the database performance.

2, according to the characteristics set out to create the index.

The characteristics of the column is different from the effect of creating an index is also different. Database administrators need to know for which set out to create an index can play a less effective results. At the same time also need to know which columns to create an index for the play but the effect is less effective. This will help them understand how in the end to the kind of fields for indexing.

According to the author's experience, often for the following characteristics of the column to create an index can play a more significant results. If for some less duplicate content column, especially for those who define the unique constraint column. To create an index on these columns can often play a very good results. If null values for some columns and non-Null value column mixing, if users need to regularly check records of all non-Null value column, it is best to set the index. If you often need to multi-table join queries, in connection with the column and set the index can be achieved more with less.

Can be seen that the index is set up properly, not only with the framework of the database design, and even went to the enterprise's economic business-related. To this end, for some software packages, although a start has already been done database administrator the index optimization. However, with the subsequent increase in economic data, this index results will be increasingly compromised. This is mainly due to influence of the table records to the Index Tuning effect. Therefore, I propose you a database administrator, even with the big software company, packaged software, also need from time to time, such as one year, the database index optimization. The removable removed, the adjustment of the adjustment to improve database performance.

If there is a table in the database is used to store user information. Which field ID number, which is a unique field. In the database design, to create an index to this field. But when put into use this database, users enter the less the user's identity card number. But usually also basically do not follow the numbers to search. On months when the record for some time, the ID number on the index field not only failed to improve database query performance, has become a chicken ribs. There are many NULL values for these columns, but not always check the value of records of all non-NULL column, the database administrator must be determined, even if the removal of these columns on the index.

So the index optimization and adjustment is a dynamic process, not to say that after a good database design does not require an adjusted. Database administrators often need to record changes to make the appropriate changes. To improve the indexing effect.
Third, the number of indexes to create a table right?

Although, to create an index on the table no limit to the number, but never better. In other words, to create an index of the matter, 1 +1 "2 are not valid. Sometimes, to create the index the more it may get counter-productive. Then a table, in the end to create the index much better? This is not a clear standard. But requires the use of a database administrator and database based on the actual situation in the record to be judged.

Generally speaking, the more indexes the table, the sooner the pace of their inquiries. However, the table will be updated to reduce the speed. This is mainly because the table update (for example, insert a record into the table) speed, but as the index increases. This is mainly because, in the update records at the same time the need to update the relevant index information. To this end, in the end the number of indexes in the table to create the right, you need to update at this speed and query speed to achieve an equilibrium point between. As for some of the data warehouse or decision-making database systems, which are mainly used to search. Relevant records in the database initialization is often poured into the time. At this point, set a little more than an index, you can improve database query performance. At the same time because the records are not very updated, so the index more and more cases, it will not affect the update speed. Even in the beginning, when the need to import large amounts of data, this time the index can also be disabled first out. To wait until after the data import, and then enable the index. Can be indexed in this way to reduce the impact of the data update. On the contrary, if those tables often need to update records, such as some transactional applications, the data update operation is a routine matter. If a table at this time to create too many indexes, it will affect the update speed. Due to more frequent update operation, so their negative effects to enhance the query efficiency than larger multi-. At this point the need to limit the number of indexes, only the necessary fields to create an index.

I optimize the database in peacetime, often based on the use of these tables to set up an index for the column. Can query related to a dynamic view, take a look at the table for this operation is to update operations (including update, delete, insert, etc.) accounted for the proportion of large, or large proportion of query operation. When the index has been affected too much the speed of update operation, then the database administrator will need to disable some of the index in order to improve database performance.

In short, in the appropriate table, the appropriate columns on the establishment of an appropriate index. This sentence contains a lot of meaning, the above was simply a part of. As the saying goes, master, self-cultivation on their own. I refer here to be able to go beyond that. The index of the contents of a number of specific optimization still need you the reader to experience in their daily work and summary.