Oracle index Overview

1. index creation Syntax:
Create uniuqe | bitmap index <schema>. <index_name>
ON <schema>. <table_name>
(<Column_name >|< expression> ASC | DESC,
<Column_name> | <expression> ASC | DESC ,...)
TABLESPACE <tablespace_name>
STORAGE <storage_settings>
LOGGING | NOLOGGING
COMPUTE STATISTICS
NOCOMPRESS | COMPRESS <nn>
NOSORT | REVERSE
PARTITION | global partition <partition_setting>


Description
1) UNIQUE | BITMAP: specify UNIQUE as the UNIQUE value index, and BITMAP as the BITMAP index, which is omitted as the B-Tree index.
2) <column_name> | <expression> ASC | DESC: you can perform a Union Index on multiple columns. When expression is used, it is a "function-based index"
3) TABLESPACE: Specify the TABLESPACE to store the index (the index and the original table are not in the same TABLESPACE, And the validity rate is higher)
4) STORAGE: You can further set the storage parameters of the tablespace.
5) LOGGING | NOLOGGING: whether to generate redo logs for indexes (use NOLOGGING to reduce the occupied space and improve the efficiency of large tables as much as possible)
6) compute statistics: Collects STATISTICS when creating a new index.
7) NOCOMPRESS | COMPRESS <nn>: whether to use "Key compression" (you can use key compression to delete duplicate values in a key column)
8) NOSORT | REVERSE: NOSORT indicates creating an index in the same order as the table, and REVERSE indicates storing the index value in REVERSE order.
9) PARTITION | NOPARTITION: You can PARTITION the created index in a partitioned table or a non-partitioned table.


2 Index features:
First, you can create a unique index to ensure the uniqueness of each row of data in the database table.
Second, it can greatly speed up data retrieval, which is also the main reason for creating an index.
Third, it can accelerate the connection between tables, especially in achieving Data Reference integrity.
Fourth, when you use grouping and sorting clauses to retrieve data, you can also significantly reduce the time for grouping and sorting in queries.
Fifth, by using indexes, you can use the optimizer during the query process to improve system performance.


3. Insufficient indexes:
First, it takes time to create and maintain indexes. This time increases with the increase of data volume.
Second, indexes occupy physical space. In addition to data tables, each index occupies a certain amount of physical space. To create a clustered index, the required space is larger.
Third, when adding, deleting, and modifying data in the table, the index must also be dynamically maintained, which reduces the Data Maintenance speed.


4. Restrict Indexes
Limiting indexes is one of the common mistakes that some inexperienced developers make. There are many traps in SQL that make some indexes unusable. Some common problems are discussed below:


4.1 Use non-equals operators (<> ,! =)
The following query statement performs a full table scan even if there is an index in the cust_rating column.
Select cust_Id, cust_name from MERs where cust_rating <> 'a ';
Change the preceding statement to the following query statement. In this way, indexes are used when a rule-based optimizer is used instead of a cost-based optimizer (more intelligent.
Select cust_Id, cust_name from MERs where cust_rating <'A' or cust_rating> 'a ';
Note: You can use indexes by changing the non-equals operator to the OR condition to avoid full table scanning.


4.2 Use is null or IS NOT NULL
Using is null or is not null also limits the use of indexes. The NULL value is not defined. Using null in SQL statements can cause a lot of trouble.
Therefore, we recommend that you set the column to not null when creating a table. If the column to be indexed has a NULL value in some rows, this index will not be used.
(Unless the index is a bitmap index, the bitmap index will be discussed in detail later ).

4.3 Use Functions
If function-based indexes are not used, the optimizer will ignore these indexes when using functions for indexed columns in the WHERE clause of SQL statements.
The following query does not use an index (as long as it is not a function-based index)
Select empno, ename, deptno from emp where trunc (hiredate) = '01-MAY-81 ';
Change the preceding statement to the following statement, so that you can search by index.
Select empno, ename, deptno from emp where hiredate <(to_date ('01-MAY-81 ') + 0.9999 );


4.4 unmatched Data Types
It is also one of the more difficult to find performance problems. Note that in the following query example, account_number is of the VARCHAR2 type and has an index on the account_number field.
The following statement performs a full table scan:
Select bank_name, address, city, state, zip from banks where account_number = 990354;
Oracle can automatically change the where clause to to_number (account_number) = 990354, which limits the use of the index. You can change it to the following query to use the index:
Select bank_name, address, city, state, zip from banks where account_number = '20140901 ';
Note: The comparison between unmatched data types will allow Oracle to automatically limit the use of indexes. Even if you execute the Explain Plan for this query, you cannot understand why a full table scan is performed"


5. query Indexes
Query the DBA_INDEXES view to obtain the list of all indexes in the table. Note that you can only retrieve the schema indexes using the USER_INDEXES method.
Access the USER_IND_COLUMNS view to obtain a specific column indexed in a given table.


6. combined index
When an index contains multiple indexed columns, it is called a concatented index.


7. Index type
B-tree index, bitmap index, HASH index, index orchestration table, reverse key index, function-based index, partition index, local and global index

7.1 B-tree index (default type)
B-tree features:
Suitable for large numbers of OLTP)
Queries that contain the OR operator cannot be used;
Suitable for columns with a high base (multiple unique values)
Typical tree structure;
Each node is a data block;
Most of them are physical, two or three layers, but logical;
The leaf block data is sorted, increasing from left to right;
The range of the index is placed in the branch block and the root block;
Note: B * Tree indexes provide the best performance when retrieving high-base data columns (high-base data columns refer to the columns with many different values.
When the proportion of the number of retrieved rows to the total number of rows is smaller than that of the hour, the B-Tree Index provides a more effective method than the full table search. However, when the check range is more than 10% of the table, the performance of data retrieval cannot be improved.

7.2 bitmap Index
Features of Bitmapt:
Applicable and decision support systems;
The UPDATE cost is very high;
It is very suitable for OR operator queries;
Bitmap indexes can be created only when the base number is small;
Note: Bitmap indexes are recommended for low cardinality columns (that is, the unique value of a column divided by the number of rows is a small value, close to zero ),
For example, in another "gender" column, the column values include "Male", "Female", and "Null". If there are a total of 3 million records, the value of 3/3000000 is approximately 0, in this case, bitmap indexes are most suitable.


7.3 Function Index
You can create a function-based index in a table. If there is no function-based index, any query that executes the function on the column cannot use the index of this column.
The following two system parameters must be set:
QUERY_REWRITE_ENABLED = TRUE
QUERY_REWRITE_INTEGRITY = TRUSTED