SQL indexes are not used.

SQL indexes are not used. 1. The query predicate does not use the primary boundary of the index, which may lead to the absence of the index. For example, you are querying SELECT * from t where y = XXX; if your T table has a composite index containing Y values, however, the optimizer considers that a row scan is more effective. In this case, the Optimizer may select table access full, but if it is replaced by select y from t where y = XXX, the optimizer will directly find the value of Y in the index, because the corresponding value can be found from the B tree. 2. If there is a null value in the B-tree index, the optimizer will not take the index because null values cannot be stored in the HASHSET when querying such as SELECT COUNT (*) FROM T, there are two ways to make the index valid. One is select count (*) from t where xxx is not null or cannot be empty. 3. If there is an index Y on table T, but your query statement is like this: SELECT * from t where fun (Y) = XXX. At this time, indexes will not be used, because all the rows in the columns you want to query need to be calculated. Therefore, if you want to improve the efficiency of such SQL statements, CREATE a function-based index on this table, for example, create index idxfunt on t (FUN (Y). This method is equivalent to the value that Oracle creates to store the computing results of all functions, you do not need to perform computation when querying again. Because many functions have different return values, it must be indicated that the function has a fixed return value. 4. indexes are not applicable to implicit conversions. For example, your SELECT * from t where y = 5 has an index on Y, but the Y column is VARCHAR2, oracle will perform an implicit conversion of the above 5, SELECT * from t where TO_NUMBER (Y) = 5, this time may also be unable to use the index. 5. In Oracle initialization parameters, there is a parameter that refers to the number of data blocks read at a time. For example, your table has only a few data blocks and can be captured at a time by Oracle, there is no need to use the index, because the index capture also needs to obtain the corresponding element value from the data block according to the rowid. Therefore, when the table is very small, indexing is not a matter of emotion. 6. If no table analysis is performed for a long time, or the table state information is re-collected, the statistical information of the table is inaccurate in the data dictionary. In this case, an incorrect index may be used, which may be inefficient. In the Oracle expert programming book, there is a very good example. Actually, you can have a deeper impression on the index, create table foo (USERNAME VARCHAR2 (100) primary key, USERID VARCHAR2 (100); generates a pile of data, then query SQL create table foobak select * from foo order by userid desc; then add the primary key index to USERNAME, and then query SQL to see two indexes that use the USERNAME pk, different execution plans are generated. This shows that the physical structure of the table actually affects the execution plan.