Introduction to oracle indexes (creation, Introduction, tips, and how to view ).

I. Index Overview

1. indexes are equivalent to directories.
2. Indexes use a set of sorted index keys to replace the default full table scan retrieval method, thus improving the retrieval efficiency.
3. The index creation should be moderate. If it is more, it will affect the efficiency of addition, deletion, and modification. If it is less, it will affect the query efficiency. It is best to create an index on a column with scattered values, avoid creating too many indexes for the same table
4. The use of indexes is transparent to users. The system determines when to use indexes.
5. Oracle supports multiple types of indexes. You can classify indexes based on the number of columns, unique index values, and the organization of index data to meet the requirements of various tables and query conditions. (See attachment)
A. Single Column index and Composite Index
B. B-tree index (default type when creating index)
All leaf nodes in the B-tree index have the same depth, so no matter what the query conditions are, the query speed is basically the same. In addition, the B-tree index can adapt to various query conditions, including precise query, fuzzy query, and comparative query.
-- The Unique index value of Unique is Unique, but null is allowed. The primary key has a Unique index by default, but the column cannot be null.
-- Non-Unique: A Non-Unique index. Its index value can be repeated and can be NULL. By default, the index created by Oracle is a non-unique index.
-- Reverse Key: Reverse keyword index. By specifying the "REVERSE" keyword when creating an index, you can create a REVERSE keyword index. The data in each indexed data column is reversely stored.
But keep the original data column order
C. Bitmap index (the value range of a column is small, such as gender and political outlook, rather than the default B-Tree Index)
C. Function Indexing
When you need to frequently access some functions or expressions, you can store them in the index. When you access them next time, because the value has been calculated, you can greatly increase the values that include the function or
The speed of the expression query operation;
Function indexes can use both B-tree indexes and bitmap indexes.

Ii. index management principles

Indexes should follow the following basic principles.
1. Small tables do not need to be indexed.
2. For large tables, if the number of records frequently queried is less than 15% of the total number of records in the table, you can create an index. This ratio is not absolute. It is inversely proportional to the full table scan speed.
3. You can create an index for columns whose values are not repeated.
4. For columns with a large base, B-tree indexes are suitable, while Bitmap indexes are suitable for columns with a small base.
5. Indexes should be created for columns with many null values, but all columns with non-null values are frequently queried.
6. indexes cannot be created for LONG and long raw columns.
7. Indexes should be created on columns that frequently perform connection queries.
8. When you use the create index statement to CREATE a query, place the most frequently queried columns in front of other columns.
9. Maintenance of indexes requires overhead. In particular, when you insert or delete a table, you must limit the number of indexes in the table. For tables mainly used for reading, it is advantageous to have multiple indexes. However, if a table is often changed, the index should be less.
10. insert data into the table and create an index. If an index is created before data is loaded, each index must be changed in Oracle when each row is inserted.

Iii. LONG type (2 GB storage) is mainly used for LONG string data that does not need to be searched for strings. To search for characters, varchar2 type is required, use the pstat1.setCharacterStream () method to store this long data. For more information, see the appendix P26.

Iv. index creation Syntax:

The syntax of the create index statement is as follows:
CREATE [UNIQUE] | [BITMAP] INDEX index_name
ON table_name ([column1 [ASC | DESC], column2
[ASC | DESC],…] | [Express])
[TABLESPACE tablespace_name]
[PCTFREE n1]
[STORAGE (INITIAL n2)]
[NOLOGGING]
[NOLINE]
[NOSORT];


V,
1. You can view the index information in the all_indexs table.
2. view the index information and the referenced column all_ind_columns
3. View function index information all_ind_expressions

4. oracle is intelligent. Sometimes it is not used even if an index is created. For example, if the data volume is small, indexes may not be used.
5. It is more efficient to scan the entire table without using indexes.
6. the query may use the cache. Therefore, if you find that the execution speed is faster, it does not necessarily indicate that your SQL statements are better. It may be because the cache is used.
7. Use the explain Plan function in plsql to compare the consumption of execution plans and then write better SQL statements.