Introduction to the advantages and disadvantages of Oracle Database Indexes

Introduction to the advantages and disadvantages of Oracle Database indexes the following articles mainly introduce the advantages and disadvantages of Oracle Database indexes. We all know that Oracle Database indexes can be divided into clustered indexes and non-clustered indexes, and unique indexes. Its advantage is its ease of query, which makes it easier to query when the data volume is large. Disadvantage: the query needs to be re-sorted to reduce the efficiency. The index creation efficiency of a physical Oracle database is low. Only one index can be created. Why create an Oracle database index? This is because creating an index can greatly improve the system performance. First, you can create a unique index to ensure the uniqueness of each row of data in the database table. Second, it can greatly speed up data retrieval, which is also the main reason for creating an index. Www.2cto.com 3. It can accelerate the connection between tables, especially for Data Reference integrity. Fourth, when using grouping and sorting clauses for data retrieval, it can also significantly reduce the time for grouping and sorting in queries. Fifth, by using indexes, you can use the optimizer during the query process to improve system performance. Some may ask: why not create an index for each column in the table because increasing Indexes has so many advantages? Although such an idea has its own rationality, it also has its own one-sidedness. Although tb indexes have many advantages, it is unwise to add indexes to every column in the table. This is because adding indexes also has many disadvantages. First, it takes time to create and maintain indexes. This time increases with the increase of data volume. Second, indexes occupy physical space. In addition to data tables, each index occupies a certain amount of physical space. To create a clustered index, the required space is larger. Third, when adding, deleting, and modifying data in the table, the index must also be dynamically maintained, which reduces the Data Maintenance speed. The www.2cto.com index is created on some columns in the database table. Therefore, when creating an Oracle database index, you should carefully consider which columns can create indexes and which Columns cannot create indexes. In general, you should create an index on these columns. For example, you can speed up the search for columns that frequently need to be searched, force the uniqueness of the column and the data arrangement structure in the organization table. These columns are usually used in connected columns and are mainly foreign keys, which can speed up the connection; create an index on a column that often needs to be searched by range. Because the index has been sorted, the specified range is continuous. Create an index on a column that frequently needs to be sorted because the index has been sorted, in this way, the sorting of indexes can be used to speed up the sorting query time. indexes are often created on the columns in the WHERE clause to accelerate the condition judgment speed. Similarly, Oracle Database indexes should not be created for some columns. In general, these columns that should not be indexed have the following characteristics: first, indexes should not be created for those columns that are rarely used in queries or referenced. This is because, since these columns are rarely used, there is an index or no index, and the query speed cannot be improved. On the contrary, the addition of indexes reduces the system maintenance speed and space requirements. Second, indexes should not be added to columns with only few data values. This is because these columns have very few values, such as gender columns in the personnel table. In the query results, the data rows in the result set account for a large proportion of the data rows in the table, that is, the proportion of data rows to be searched in the table is large. Adding indexes to www.2cto.com does not significantly speed up retrieval. Third, indexes should not be added for columns defined as text, image, and bit data types. This is because the data volume of these columns is either large or small. Fourth, when the modification performance is far greater than the retrieval performance, you should not create an index. This is because the modification performance and retrieval performance are inconsistent. When an index is added, the search performance is improved, but the modification performance is reduced. When the index is reduced, the modification performance is improved and the retrieval performance is reduced. Therefore, when the modification performance is much higher than the retrieval performance, you should not create an Oracle database index. Author chen11-1