Oracle index details-1

Oracle index description-1. Index features: 1. By creating a unique index, You can ensure the uniqueness of each row of data in the database table. 2. Data retrieval speed can be greatly accelerated, which is also the main reason for index creation. 3. It can accelerate the relationship between tables, especially in data reference integrity. 4. When you use grouping and sorting clauses to retrieve data, you can reduce the time for grouping and sorting in queries. 5. By using the index, you can use the optimization hiding tool to improve the system performance during the Query Process. Ii. Insufficient indexes: 1. It takes a lot of time to create and maintain indexes. This time will increase as the data volume increases. 2. Indexes occupy physical space. In addition to data tables, each index occupies a certain amount of physical space. To create a clustered index, a larger physical space is required. 3. When adding, deleting, and modifying table data, indexes also need to be dynamically maintained, which reduces the Data Maintenance speed. 3. Features of index columns should be set up: 1. The search speed can be improved for columns that frequently need to be searched; 2. For columns that are used as primary keys, force the uniqueness of the column and the data arrangement structure in the organization table. 3. These columns are usually used in connection columns. These columns are mainly foreign keys, which can speed up connection query; 4. 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. 5. Create an index on a column that often needs to be sorted, because the index has been sorted, this can greatly reduce the time needed for sorting; 6. Create an index on the column in The WHERE clause frequently to speed up condition judgment; 4. Features of index columns should not be created: 1. Indexes should not be created for columns that are rarely used or referenced in queries, because these columns are rarely used, therefore, indexing or non-indexing does not increase the query speed. On the contrary, the addition of indexes reduces the system maintenance speed and space requirements. 2. Indexes should not be created for columns with very few data values, because these columns have very few values, such as gender in the personnel table. In the query results, data rows in the result set occupy a large proportion, that is, a large proportion needs to be searched in the result set. Increasing the index does not significantly speed up the query. 3. Indexes should not be added for columns defined as Blob data because the data volume of these columns is either large or small. 4. When the modification performance is far greater than the retrieval performance, you should not create an index because the modification performance is in conflict with the retrieval performance. When the index is added, the retrieval performance will be improved, however, the modification performance will be reduced, and the modification performance will be improved when the index is reduced, but the retrieval performance will be reduced. Therefore, when the modification performance is far greater than the retrieval performance, you should not create an index.