Basic rules for creating indexes in Oracle

Basic rules for creating indexes in Oracle 1. B-Tree indexes 1. the principle of selecting an index field: select a highly selective field for the join field in the most frequently used field join statement in the WHERE clause (if few fields have the same value, there are many unique values, the selection is good) oracle automatically creates indexes on UNIQUE and primary key fields. indexing on poorly selective fields 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 a B-TREE index on a field with few unique values, in which case you can consider creating Bitmap indexes on these fields. in the online transaction processing environment, the concurrency is very high, and the index is often modified. Therefore, you should not create Bitmap indexes and do not create indexes on frequently modified fields. when there are UPDATE, DELETE, and INSETT operations, apart from updating the table data, ORACLE also needs to UPDATE the index, just like updating the data, or do not create indexes on fields that use functions to create restoration and redo entries. In this case, the optimizer Indexes are not used. Unless you create a function index, 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 there are many concurrent INSERT, UPDATE, and DELETE operations on the parent and child tables. When an index is created, please compare the query performance improvement and UPDATE after the index is obtained, DELETE, INSERT operation performance loss, after comparison of gains and losses, finally decide whether to establish this index 2. advantages of choosing composite index: improved selectivity: composite index is more selective than index of a single field to reduce I/O: if all the fields to be queried are included in the composite index fields, ORACLE only needs to access the index. If you do not need to access the table, what will the optimizer use the composite index? (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, they are more selective than each other. 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. composite Index field sorting principle: Make sure that the fields used in the WHERE clause are the leading fields of composite indexes. If a field is most frequently used in the WHERE clause, consider placing this field first (in the create index statement). If all fields use the same frequency in the WHERE clause, the most selective fields are placed at the top, sort the least selective fields at the end. If all fields use the same frequency in the WHERE clause, if the data is physically sorted by a field, Then, we will consider placing this field at the first place of the composite index. 2. When will Bitmap indexes improve the query performance? The WHERE clause contains multiple predicates for fields with a low base. A single predicate selects a large number of rows with bitmap indexes on these low base fields to create 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 containing lengthy WHERE clauses, bitmap index can also provide excellent performance comparison between bitmap index and B-TREE index. Bitmap index can save more storage space. Bitmap index is more suitable for data warehouse environment, but not suitable for online transaction processing environment. 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, the compressed bitmap of a key value is composed Each bitmap segment is about the SIZE of half a BLOCK, and the minimum granularity of the lock is a bitmap segment. In the online transaction processing environment, if multiple transactions execute simultaneous updates (that is, concurrent updates), using Bitmap indexes affects UPDATE, INSERT, DELETE performance. an entry in a B-TREE index contains only one ROWID. Therefore, when an index entry is locked, one 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 index performance is better than B-TREE index. 3. The index and NULL values are considered unique values in the index unless the values of the two or multiple rows of the index are equal. Bottom. in that case, rows are considered equal. Therefore, a unique index does not allow rows to contain null values for fear of being considered equal. however, this rule does not apply when all rows are null. oracle does not index the rows of all tables whose values are NULL, unless it is a bitmap index or when the primary key field value is NULL. 4. Common rules for creating an index: the primary key and foreign key of the following table must be indexed; more than 300 of the data volume should be indexed; for tables that are often connected to other tables, indexes should be set up for the connected fields. For fields that frequently appear in the Where clause, especially for large tables, indexes should be set up; the index should be built on highly selective fields. The index should be built on small fields. For large text fields or even ultra-long fields, do not create an index. The establishment of a composite index requires careful analysis; try to replace it with A single field Index: A. correctly select the primary column field in the composite index, which is generally A highly selective field; b. Do the fields of the composite index often appear in the Where clause in the AND mode? Are there very few or even no single-field queries? If yes, a composite index can be created; otherwise, a single field index is considered; C. If the fields contained in the composite index are often separately contained in the Where clause, they are decomposed into multiple single field indexes; d. If the composite index contains more than three fields, consider the necessity carefully and reduce the composite fields. E. If there is a single field index, composite indexes of these fields can be deleted. For tables that frequently perform data operations, do not create too many indexes. Useless indexes are deleted, avoid negative impact on execution plans. The above are the basis for determining general indexes. In a word, the establishment of indexes must be careful. The necessity of each index should be carefully analyzed and a basis should be established. Because too many indexes and inadequate or incorrect indexes have no benefits for performance: Each index created on a table increases storage overhead, indexes also increase processing overhead for insert, delete, and update operations. In addition, too many composite indexes generally have no value when there is a single-field index. On the contrary, they also reduce the performance when data is added and deleted, especially for frequently updated tables, the negative impact is greater. Seven considerations for creating an Oracle index when creating an Oracle index, we need to pay attention to some issues. The following describes some considerations for creating an Oracle index, it is helpful for you to learn how to create an Oracle index. 1. Generally, you do not need to create an index for a small table. 2. For a large table, if the data to be queried frequently does not exceed 10% to 15%, then there is no need to create an index for it. At this time, the indexing overhead may be much higher than the performance improvement. This ratio is just an empirical data. If the database administrator needs to draw a more accurate conclusion, test and analysis are required. 3. For example, for columns with few duplicates, especially those columns with unique constraints. Creating an index on these columns often results in very good results. For example, if you want to query all columns with non-null values in a mix of Null values and non-Null values, it is best to set an index for these columns. If you frequently need multi-table join queries, you can set indexes on the connected columns to get twice the result with half the effort. 4. The database administrator needs to optimize the database index over a period of time, such as one year. Remove the removed items and adjust them to improve the database performance. 5. Generally, the more indexes a table has, the faster the query speed. However, the table update speed is reduced. This is mainly because the table update speed (such as inserting a record into the table) increases with the increase of indexes. This is mainly because related index information needs to be updated while updating records. Therefore, if it is appropriate to create an index in a table, a balance between the update speed and the query speed needs to be obtained. 6. Some data warehouses or decision-making database systems are mainly used for query. Related records are often imported during database initialization. In this case, you can set more indexes to improve the query performance of the database. At the same time, because the record is not updated much, the update speed will not be affected when there are many indexes. Even if you need to import a large amount of data at the beginning, you can disable the index first. After the data is imported, enable the index. This method can be used to reduce the impact of indexes on data updates. On the contrary, if the tables often need to update records, such as some transaction application systems, data update operations are common. If you create too many indexes in a table, the update speed will be affected. 7. Bitmap indexes. The base number is a basic definition in the bitmap index. It refers to a non-repeated value in the content of a field in the database table. For example, the gender field in the employee information table generally has only two values: Male and female. Therefore, the base number is 2. For the Marital Status field, there are only three States: Married, unmarried, and divorced, the base number is 3, and there are only a limited number of values in the national list. How to create the best index? 1. create index index_name on table_name (field_name) tablespace tablespace_namepctfree 5 initrans 2 maxtrans 255 storage (minextents 1 maxextents 16382 pctincrease 0 ); 2. create a function-based index that is commonly used in function categories such as UPPER, LOWER, and TO_CHAR (date). For example, create index idx_func on emp (UPPER (ename) tablespace tablespace_name; 3. When creating a bitmap index to index a column with a small base and a relatively stable base, you should first consider the bitmap index. For example: create bitmap index idx_bitm on class (classno) tablespace tablespace_na Me; 4. You can use the create unique index statement to create a unique index. For example, create unique index dept_unique_idx on dept (dept_no) tablespace idx_1; 5. You can use the using index statement to create a constraint-related index. It is an index related to the unique and primary key constraints. For example, alter table table_nameadd constraint PK_primary_keyname primary key (field_name) using index tablespace tablespace_name; how to create a local area index? 1) The basic table must be a partition table. 2) the number of partitions is the same as that of the basic table. 3) the number of subpartitions in each index partition is the same as that in the corresponding basic table partition. 4) the index items of rows in the Self-partition of the base table are stored in the corresponding self-partition of the index, for example, create index TG_CDR04_SERV_ID_IDX on TG_CDR04 (SERV_ID) pctfree 5 Tablespace TBS_AK01_IDXStorage (MaxExtents 32768 PctIncrease 0 FreeLists 1 FreeList Groups 1) local/how to create a global index for a range partition? Basic Tables can be global tables and partition tables create index idx_start_date on tg_cdr01 (start_date) global partition by range (start_date) (partition p01_idx vlaues less than ('20140901 ') partition p01_idx vlaues less than ('20140901 ')... partition p01_idx vlaues less than ('20140901')/How to recreate an existing index? Rebuilding an existing index does not affect the current time of the query. You can delete additional data blocks to improve the index query efficiency. alter index idx_name rebuild nologging; for the partition index alter index idx_name rebuild partition partition_name nologging; why is the index deleted? 1) indexes that are no longer needed 2) The index does not provide the expected performance improvement for queries published on the relevant tables 3) the application does not use the index to query data 4) The index is invalid, the index must be deleted before reconstruction. 5) the index has become too broken. You must delete the index statement before reconstruction: drop index idx_name; drop index idx_name partition partition_name; what is the cost of creating an index? During basic table maintenance, the system must maintain indexes at the same time. unreasonable indexes will seriously affect system resources, mainly on CPU and I/O. Inserting, updating, and deleting data results in a large number of db file sequential read lock waits.