If you use a function on a column, the index of that column will not be used. * * * *
For example: substring(field name,1,2)=’ XXX ‘;
+, -, *, /,! Etc.), the index of the column will not work.
Select * from test where id-1=9; // Error;
select * from test where id=10; // The correct way to write;
In some cases, the index of the column will not be used for the LIKE operation.
LIKE CONCAT(‘%’, ‘2014-08-13 ‘, ‘%’);
4. In some cases, the index of the column will not work if the operation is reversed.
For example, field name <> 2;
Resources: sourl.cn/sx6zLt
If a column has no index, the index of the other columns will not be used. * * * *
Implicit conversion causes index invalidation. This should be taken seriously. This is a common mistake in development. * * * *
SQL > select varCHAR2 (20), varchar2(20), varchar2(20), varchar2(20), varchar2(20)
Select * from test where t_number=13333333333; // Error;
select * from test where t_number=’13333333333′; // The correct way to write; 7. When using not in,not exist, etc. * * * *
When the variable is times and the table field is date. Or vice versa. * * * *
If the b-tree index is null, the bitmap index will be invalid. If the bitmap index is not NULL, the bitmap index will be invalid.
The join index is not null as long as the index column is created (in no particular order). * * * *
In null must be used in conjunction with the first column of the index. If the first position condition of the index is NULL, the other columns of the index can be IS NULL (but only if all columns satisfy is NULL), or = a value.
When the first position of the index is = a value, the other index columns can be any case (including is NULL = a value), the index will be invalidated in both cases, the other case will not be invalidated.