The above are some general judgment bases for indexing. In a word, the establishment of indexes must be careful, and the necessity of each index should be carefully analyzed.
The above are some general judgment bases for indexing. In a word, the establishment of indexes must be careful, and the necessity of each index should be carefully analyzed.
I. B-Tree indexes
1. Principles for selecting index fields:
The most frequently used field in the where clause
Join fields in a join statement
Select highly selective fields (if a few fields have the same value and there are many unique values, the selection is good)
Oracle automatically creates an index on UNIQUE and primary key fields
Creating an index on a field with poor selectivity is beneficial only when the value distribution of this field is very skewed (in this case, two field values appear much less than other field values)
Do not create B-TREE indexes on fields with few unique values. In this case, you can consider creating Bitmap indexes on these fields. in the online transaction processing environment, the concurrency is very high, and indexes are often modified. Therefore, bitmap indexes should not be created.
Do not index frequently-modified fields. when there are UPDATE, DELETE, and INSETT operations, in addition to updating the table data, ORACLE also needs to UPDATE the index, just like updating data, or generating restoration and redo entries.
Do not create indexes on fields that use functions. In this case, the optimizer does not use indexes unless you create function indexes.
You can consider creating indexes on foreign key fields. These indexes allow the sharing of sub-Table locks when performing the UPDATE and DELETE operations on the master table, this is very suitable for the case where the parent and child tables have many concurrent INSERT, UPDATE, and DELETE operations.
After an index is created, compare the query performance improvement and the loss of UPDATE, DELETE, and INSERT operations after the index is created. After comparison, determine whether to create this index.
2. Select create Composite Index
Advantages of composite indexes:
Improved selectivity: composite indexes are more selective than single-field indexes.
Reduce I/O: if all the fields to be queried are included in the compound index field, ORACLE only needs to access the index without accessing the table.
Under what circumstances will the optimizer use composite indexes?
(A) When the WHERE clause of an SQL statement uses the leading field of composite indexes, the ORACLE optimizer will consider using composite indexes for access.
(B) when several fields are often used together in the WHERE clause of an SQL statement using the AND operator as filter predicates, when these fields are combined, the selectivity is better than that of each field.
You can use these fields to create a composite index.
(C) When several query statements query the same field values, you can create a composite index on these fields.
Principles for sorting compound index fields:
Make sure that the fields used in the WHERE clause are the leading fields of the composite index.
If a field is most frequently used in the WHERE clause, you should consider putting this field first when creating a composite INDEX (in the create index Statement)
If all fields use the same frequency in the WHERE clause, the most selective fields are placed at the beginning, and the least selective fields are placed at the end.
If all fields use the same frequency in the WHERE clause, if the data is physically sorted by a field, consider placing this field first in the composite index.
Ii. Bitmap Index
Under what circumstances can bitmap indexes improve query performance?
The WHERE clause contains fields with multiple predicates in the middle and low bases.
A single predicate selects a large number of rows on these low-base fields.
Bitmap indexes have been created on some or all of these low-base fields.
The queried table contains many rows.
Multiple Bitmap indexes can be created on a single table. Therefore, bitmap indexes can improve the performance of complex queries that contain lengthy WHERE clauses. In the join query statements of aggregate queries and star models, bitmap indexes can also provide excellent performance.
Comparison between bitmap index and B-TREE Index
Bitmap indexes save storage space
Bitmap indexes are suitable for data warehouse environments, but not for online transaction processing environments. in the data warehouse environment, data maintenance is usually completed through batch INSERT and batch UPDATE, so the index maintenance is delayed until the DML operation is completed. for example, when you insert 1000 rows of data in batches, these inserted rows are placed in the sort buffer, and then the 1000 index entries are updated in batches, each bitmap segment only needs to be updated once in each DML operation, even if multiple rows in that bitmap segment are updated
A compressed bitmap of a key value is composed of one or more bitmap segments. Each bitmap segment is about as large as half a BLOCK. The minimum granularity of a lock is a bitmap segment, in the online transaction processing environment, if multiple transactions execute simultaneous updates (that is, concurrent updates), bitmap indexes will affect the UPDATE, INSERT, and DELETE performance.
An B-TREE index contains only one ROWID, so when an index entry is locked, that is, a row is locked. however, for Bitmap indexes, an index entry may potentially contain a ROWID (that is, a ROWID within a certain range, with multiple rowids). When a bitmap index entry is locked, the ROWID contained in this entry is locked, which affects the concurrency. the larger the number of rowids in a single-digit image segment, the poorer the concurrency. even so, for bulk insert, UPDATE, and DELETE, bitmap indexes still offer better performance than B-TREE indexes.
,
