My experience with a simple SQL Optimization-index unavailability

One day I met a colleague who asked me to help optimize an SQL statement, which took a long time to run. So I queried the execution plan and found that this SQL was going to be performed once.

One day I met a colleague who asked me to help optimize an SQL statement, which took a long time to run. So I queried the execution plan and found that this SQL was going to be performed once.

One day I met a colleague who asked me to help optimize an SQL statement, which took a long time to run. As a result, I queried the execution plan and found that the SQL was to perform a full table scan. At that time, I checked the definition of the table and found that the condition column in The where clause is indexed. Why does the execution plan display the full table scan. This problem has plagued me for a long time, so I later looked at the definition of the table and found the root of the problem. The condition field is varchar, the SQL statement contains a string of numbers! In this case, the index becomes unavailable and the processing method is very simple. You can add a pair of single quotes.

Below are the statements and execution plans (here we have simplified the processing, you only need to pay attention to the full table scan ):

Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 1 | 5 | 3 (0) | 00:00:01 |
| * 1 | table access full | TEST | 1 | 5 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter (TO_NUMBER ("T". "SPEC_PRPTY_ID") = 3303)

This is an execution plan with single quotes added:

Plan hash value: 1128569081
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-----------------------------------------------------------------------------
| 0 | select statement | 1 | 5 | 1 (0) | 00:00:01 |
| * 1 | index range scan | IDX_TEST | 1 | 5 | 1 (0) | 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-access ("T". "SPEC_PRPTY_ID" = '000000 ')

We can see that the index is used. If the table has a large amount of data, the efficiency improvement is very impressive.

In fact, from the execution plan:1-filter (TO_NUMBER ("T ". "SPEC_PRPTY_ID") = 3303) the reason is that an implicit type conversion is performed. In this case, no index is used. If the table is large enough, the query is slow.

This example is very simple, and many experts will sneer at it, but I still get a knowledge point from the belief that Oracle is learned from its subtleties: The index will not be used in several cases:

1 not equal to operation cannot be used for Index

2 indexes cannot be used for index columns that have passed common or function operations.

3 Including forward blur

4. The index column is empty.

5 when comparing values, the left and right types are different, which is equivalent to implicit type conversion.

6. The value queried for the index is an unknown field rather than a known number.

Be sure to write SQL with caution.