Shulou(Shulou.com)06/01 Report--

Today, a developer colleague said that when he executed a SQL in the test library, he reported an ORA-01722: invalid number error, but executing the same SQL in the production database and the grayscale database could be executed normally. The SQL is as follows:

Select ItemStatus from BUS_CONTRACT where SerialNo=2016033100000047

Asked the developer for basic information, learned that the field type of SerialNo is varchar2 type, at this time without quotation marks, there must be an implicit conversion, but why can it be executed successfully in the production library and grayscale library? With such doubts, I groped slowly as follows.

The initial guess is that there is data with characters in the SerialNo field of other data rows, but looking at the table structure of the BUS_ contract table, it is found that the primary key of the BUS_ contract table is the SerialNo field, at this time the query should be able to take the primary key index instead of scanning the whole table, even if other data rows have data with characters, it will not be scanned, but why the error?

Later, by searching the articles on the network, we learned that during the implicit conversion of oracle, the conversion of VARCHAR2- > NUMBER will not lead to the invalidation of the index, while the conversion of NUMBER- > VARCHAR2 will invalidate the index. It is obvious that this query is the conversion of NUMBER- > VARCHAR2. At this time, even if there is an index, oracle will not scan the index but only scan the whole table to check its execution plan:

SQL > set autotrace traceonly

SQL >

SQL > select ItemStatus from BUS_CONTRACT where SerialNo=2016033100000047

ERROR:

ORA-01722: invalid number

No rows selected

Execution Plan

Plan hash value: 809618537

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 1 | 21 | 661 (1) | 00:00:08 |

| | * 1 | TABLE ACCESS FULL | BUS_CONTRACT | 1 | 21 | 661 (1) | 00:00:08 |

-

Predicate Information (identified by operation id):

1-filter (TO_NUMBER ("SERIALNO") = 2016033100000047)

Statistics

0 recursive calls

1 db block gets

2341 consistent gets

2392 physical reads

0 redo size

529 bytes sent via SQL*Net to client

519 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

SQL >

SQL >

Then, notify the developer colleague to see if there is dirty data in the SerialNo field through the following SQL:

Select ItemStatus,SerialNo from BUSI_CONTRACT

Sure enough, the developer reported that there was a record that was not a pure number but with some characters, and the query was normal after it was deleted.

It is recommended that SQL related to implicit conversion should be enclosed in quotation marks. The following is the execution plan of the SQL statement select ItemStatus from BUSI_CONTRACT where SerialNo='2016033100000047';:

SQL >

SQL > select ItemStatus from BUS_CONTRACT where SerialNo='2016033100000047'

Execution Plan

Plan hash value: 338903438

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 1 | 21 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | BUS_CONTRACT | 1 | 21 | 2 (0) | 00:00:01 |

| | * 2 | INDEX UNIQUE SCAN | PK_BUS_CONTRACT | 1 | | 1 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

2-access ("SERIALNO" = '2016033100000047')

Statistics

1 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

528 bytes sent via SQL*Net to client

519 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

The performance is significantly better than that without quotation marks, because there is no implicit conversion at this time, and SQL performs a walk-index scan.

Conclusion: 1. When it comes to implicit conversion to fields, it is best to put quotation marks, otherwise the index will not be taken.

two。 If the implicit conversion is VARCHAR2- > NUMBER conversion, it will not cause the index to fail, but if it is the conversion of NUMBER- > VARCHAR2, it will make the index invalid.

3. The reason why NUMBER- > VARCHAR2 will invalidate the index should be converted to where to_number (name) = 123.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.