An introduction to the oracle index (creation introduction tips how to view).
- 2021-11-24 03:13:50
- OfStack
1. Introduction to the Index
1. Index is equivalent to directory
2. Index is to replace the default full-table scanning retrieval mode by a group of sorted index keys, so as to improve the retrieval efficiency.
3. Indexes should be created moderately. More indexes will affect the efficiency of addition, deletion and modification, while less indexes will affect the efficiency of query. Indexes should be created on columns with scattered values to avoid creating too many indexes on the same table
4. The use of indexes is transparent to users, and the system decides when to use indexes.
5. Oracle supports many types of indexes, which can be classified according to the number of columns, whether the index value is only 1 and the organization form of index data, so as to meet the requirements of various tables and query conditions. (Please see attachment.)
a. Single-column and composite indexes
b. B tree index (default type in create index)
All leaf nodes in the B tree index have the same depth, so the query speed is basically the same regardless of the query criteria. In addition, B tree index can adapt to various query conditions, including precise query, fuzzy query and comparative query
--Unique only 1 index value only 1, but null is allowed, primary key only 1 index exists by default, but column cannot be null
--Non-Unique: Non-unique index, whose index value can be duplicated, allowing NULL. By default, the index created by Oracle is a non-only 1 index
--Reverse Key: Reverse keyword index. By specifying the "REVERSE" keyword at index creation time, you can create a reverse keyword index, and the data in each data column that is indexed is stored in reverse
But still keep the order of the original data columns
c. Bitmap index (for less range of column values, such as gender politics, rather than the default B tree index)
c. Functional index
When you need to access a few functions or expressions frequently, you can store them in the index. When you access them next time, because the value has already been calculated, you can greatly improve those who include this function in the WHERE clause or
The speed of the query operation of the expression;
Functional indexes can use either B tree indexes or bitmap indexes.
2. Principles for managing indexes
Using indexes should follow the following basic principles.
1. Small tables do not need to be indexed.
2. For large tables, an index can be created if the number of records frequently queried is less than 15% of the total number of records in the table. This ratio is not absolute, it is inversely proportional to the scanning speed of the whole meter.
3. Indexing can be done for columns where most column values do not duplicate.
4. For columns with large cardinality, B tree index is suitable, while for columns with small cardinality, bitmap index is suitable.
5. Indexes should be built for columns that have many null values in their columns, but frequently query all non-null records.
6. The LONG and LONG RAW columns cannot be indexed.
7. Indexes should be created on columns where join queries are frequently performed.
8. When you create a query using the CREATE INDEX statement, put the most frequently queried column before the other columns.
9. Maintaining indexes requires overhead, especially when inserting and deleting tables, so limit the number of indexes in a table. For tables that are mainly used for reading, it is beneficial to have more indexes, but if a table is frequently changed, it should have fewer indexes.
10. Create an index after inserting data into the table. If an index is created before loading the data, Oracle must change each index when inserting each row.
3. LONG type (2G can be stored) is mainly used for long string data without string search. If character search is needed, varchar2 type should be used. Please use pstat1.setCharacterStream () method for such long storage. See Annex P26 for information
4. The syntax for creating an index is as follows
The syntax of the CREATE INDEX statement is as follows:
CREATE [UNIQUE] | [BITMAP] INDEX index_name
ON table_name([column1 [ASC|DESC],column2
[ASCDESC],...] [express])
[TABLESPACE tablespace_name]
[PCTFREE n1]
[STORAGE (INITIAL n2)]
[NOLOGGING]
[NOLINE]
[NOSORT];
5.
1. The index information can be viewed in the all_indexs table
2. View index information and referenced columns all_ind_columns
3. View the functional index information all_ind_expressions
4. oracle is intelligent, and sometimes it will not be used even if an index is created. For example, in the case of a small amount of data, an index may not be used
5. When scanning the whole table, it will be more efficient without indexing
6. Query may use cache, so if you find that the execution speed becomes faster, it will not mean that your sql is better, and it may be that you use cache.
7. Using the "Explain Plan" function in plsql, you can compare the consumption of executing the plan, and then write a better sql
1. Index is equivalent to directory
2. Index is to replace the default full-table scanning retrieval mode by a group of sorted index keys, so as to improve the retrieval efficiency.
3. Indexes should be created moderately. More indexes will affect the efficiency of addition, deletion and modification, while less indexes will affect the efficiency of query. Indexes should be created on columns with scattered values to avoid creating too many indexes on the same table
4. The use of indexes is transparent to users, and the system decides when to use indexes.
5. Oracle supports many types of indexes, which can be classified according to the number of columns, whether the index value is only 1 and the organization form of index data, so as to meet the requirements of various tables and query conditions. (Please see attachment.)
a. Single-column and composite indexes
b. B tree index (default type in create index)
All leaf nodes in the B tree index have the same depth, so the query speed is basically the same regardless of the query criteria. In addition, B tree index can adapt to various query conditions, including precise query, fuzzy query and comparative query
--Unique only 1 index value only 1, but null is allowed, primary key only 1 index exists by default, but column cannot be null
--Non-Unique: Non-unique index, whose index value can be duplicated, allowing NULL. By default, the index created by Oracle is a non-only 1 index
--Reverse Key: Reverse keyword index. By specifying the "REVERSE" keyword at index creation time, you can create a reverse keyword index, and the data in each data column that is indexed is stored in reverse
But still keep the order of the original data columns
c. Bitmap index (for less range of column values, such as gender politics, rather than the default B tree index)
c. Functional index
When you need to access a few functions or expressions frequently, you can store them in the index. When you access them next time, because the value has already been calculated, you can greatly improve those who include this function in the WHERE clause or
The speed of the query operation of the expression;
Functional indexes can use either B tree indexes or bitmap indexes.
2. Principles for managing indexes
Using indexes should follow the following basic principles.
1. Small tables do not need to be indexed.
2. For large tables, an index can be created if the number of records frequently queried is less than 15% of the total number of records in the table. This ratio is not absolute, it is inversely proportional to the scanning speed of the whole meter.
3. Indexing can be done for columns where most column values do not duplicate.
4. For columns with large cardinality, B tree index is suitable, while for columns with small cardinality, bitmap index is suitable.
5. Indexes should be built for columns that have many null values in their columns, but frequently query all non-null records.
6. The LONG and LONG RAW columns cannot be indexed.
7. Indexes should be created on columns where join queries are frequently performed.
8. When you create a query using the CREATE INDEX statement, put the most frequently queried column before the other columns.
9. Maintaining indexes requires overhead, especially when inserting and deleting tables, so limit the number of indexes in a table. For tables that are mainly used for reading, it is beneficial to have more indexes, but if a table is frequently changed, it should have fewer indexes.
10. Create an index after inserting data into the table. If an index is created before loading the data, Oracle must change each index when inserting each row.
3. LONG type (2G can be stored) is mainly used for long string data without string search. If character search is needed, varchar2 type should be used. Please use pstat1.setCharacterStream () method for such long storage. See Annex P26 for information
4. The syntax for creating an index is as follows
The syntax of the CREATE INDEX statement is as follows:
CREATE [UNIQUE] | [BITMAP] INDEX index_name
ON table_name([column1 [ASC|DESC],column2
[ASCDESC],...] [express])
[TABLESPACE tablespace_name]
[PCTFREE n1]
[STORAGE (INITIAL n2)]
[NOLOGGING]
[NOLINE]
[NOSORT];
5.
1. The index information can be viewed in the all_indexs table
2. View index information and referenced columns all_ind_columns
3. View the functional index information all_ind_expressions
4. oracle is intelligent, and sometimes it will not be used even if an index is created. For example, in the case of a small amount of data, an index may not be used
5. When scanning the whole table, it will be more efficient without indexing
6. Query may use cache, so if you find that the execution speed becomes faster, it will not mean that your sql is better, and it may be that you use cache.
7. Using the "Explain Plan" function in plsql, you can compare the consumption of executing the plan, and then write a better sql