Advantages and disadvantages of Oracle Indexes

From: http://database.ctocio.com.cn/116/12198616.shtml
The followingArticleThis article mainly introduces the advantages and disadvantages of Oracle Database indexes. If you want to know the advantages and disadvantages of Oracle Database indexes, you can use the following articles to understand its practical application and functions.

, The following is a detailed introduction of the article ......

The following articles mainly introduce the advantages and disadvantages of Oracle database indexing. If you want to know the advantages and disadvantages of Oracle database indexing, you can use the following articles to provide some practical applications and functions.

For more information, see the following.
 
Database indexes include clustered indexes, non-clustered indexes, and unique indexes.
 
Advantage: It facilitates query and makes sorting easier when the data volume is large.
 
Disadvantage: the query needs to be re-sorted to reduce the efficiency. Disadvantages of physical indexes: Low indexing efficiency. Only one index can be created.
 
More convincing:
 
Why create an 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 an Oracle database table.
 
Second, it can greatly speed up data retrieval, which is also the main reason for creating an index.
 
Third, it can accelerate the connection between tables, especially in achieving 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 indexes have many advantages,

It is unwise to add an index for each 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.
 
Indexes are created on certain columns in Oracle database tables. Therefore, when creating an index, you should carefully consider which columns can create an index and which Columns cannot create an index. Generally

Create an index. For example, you can speed up the search for columns that frequently need to be searched;
 
In a column that acts as a primary key, the uniqueness of the column is forced and the data arrangement structure in the organization table. In a column that is often used for connection, these columns are mainly foreign keys, which can speed up the connection; search by range frequently

Create an index on the column because the index has been sorted and its specified range is continuous;
 
Create an index on a column that needs to be sorted frequently. Because the index has been sorted, you can sort the index to accelerate the sorting query time. You can create an index on a column that is frequently used in the WHERE clause, speed up condition judgment

Degree.
 
Similarly, 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

However, these columns are rarely used, so there are indexes or no indexes, 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

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, a large proportion of the data rows to be searched in the table.
 
Adding indexes does not significantly accelerate the search speed. 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. The

4. When the modification performance is much higher 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 modification

Indexes should not be created when the search performance is far greater than the search performance.
 
Oracle Database can be said to have advantages over disadvantages. The disadvantages need to be further improved, and we strive to perfect the Oracle database.