Oracle Index __oracle

type of index:
B-Tree Index
Bitmap index
Hash index
Index Choreography Table
Reverse Key index
Function-based indexing
Partitioned index
Local and global indexes

Limit Index ( index invalidation )

The reasons for the failure of Oracle indexes are many: (Specific analysis is required). You can judge according to the plan of execution.

1. Use functions on indexed columns. such as SUBSTR,DECODE,INSTR, and so on, the index column is operational. You need to set up a function index to solve it.

2, the new table has not been able to generate statistical information, analysis of the good

3, based on cost analysis, access to the table is too small, using full table scan consumption is less than the use of indexes.

4, the use of <>, not in, not exist, for the three cases in most cases that the result set is very large, generally greater than 5%-15% do not take the index and walk FTS.

5, Separate >, <.

6, like "%_" hundred semicolon in front.

7. Separate references to index columns not in the first position in the composite index.

8. When a character field is a number, no quotation marks are added in the Where condition.

9, when the variable is using the Times variable, and the table's field is the date variable. or the opposite.

10, index failure, you can consider rebuilding the index, rebuild online.

11, B-tree index is null does not go, is not NULL will go, bitmap index is null,is not NULL will go, federated index is not null as long as the indexed column in the establishment (in no order) will go.

12. It may be that your table is often updated, the fragments are too many, the index takes up too much space, the optimizer does not feel the need to use the index
Just scan the entire table, ah, you re-build the index, you have to organize the pieces of Ah, of course, with the index

Restricting indexing is one of the common mistakes that inexperienced developers make. There are many traps in SQL that make some indexes unusable. Some common issues are discussed below:
4.1 Use not equal to operator (<>,!=)
The following query still performs a full table scan, even if there is an index in the cust_rating column.
Select Cust_id,cust_name
From customers
where cust_rating <> ' AA ';
Change the above statement to the following query statement so that the index is used when adopting a rule-based optimizer instead of a cost-based optimizer (more intelligent).
Select Cust_id,cust_name
From customers
where cust_rating < ' AA ' or cust_rating > ' AA ';
Special Note: You can use indexes to avoid full table scans by changing the not-equal operator to an OR condition.
2 using is null or is not NULL
Using is null or is not NULL also restricts the use of indexes. Because null values are not defined. Using NULL in SQL statements can be a lot of trouble. Therefore, developers are advised to set the columns that need to be indexed to not NULL when the table is being built. If the indexed column has a null value in some rows, the index is not used (unless the index is a bitmap index and the bitmap index is discussed in detail later).
3 Using Functions
If you do not use a function based index, the optimizer ignores these indexes when you use functions in the WHERE clause of the SQL statement for columns that are indexed.
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 above statement to the following statement so that you can find it by index.
Select Empno,ename,deptno
From EMP
where hiredate< (to_date (' 01-may-81 ') +0.9999);
4 Compare mismatched data types
Comparing mismatched data types is also one of the more difficult performance issues to discover. Note The following query example, Account_number is a 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 turn the WHERE clause into to_number (account_number) = 990354, which limits the use of the index and can be used by changing to the following query:
Select Bank_name,address,city,state,zip
From banks
where Account_number = ' 990354 ';
Special note: Comparisons between mismatched data types will allow Oracle to automatically restrict the use of indexes, even if the query execution explain plan does not let you understand why a "full table scan" was done.