Today's live staff reported: the same data, the same SQL, can be executed in a product database, but always an error in the test database.
The inspection steps are as follows: 1. Run SQL in the two databases, verify that the situation is as follows, and the result is true. 2. View the SQL statement, understand the meaning of SQL, and found that the SQL write is not ideal. After the change, it will run normal in the two databases, but this is another problem, which is not Table 3. Check that in two libraries, the SQL execution plan is the same, and the result is different. 4. Check if the version of the two libraries is the same, and the result is the same. 5. Check if the optimization mode in the two libraries is the same, the result is different, at this time, use the ALTER session to modify the optimization mode of the database of running error, and then view the execution plan again, find the same, run SQL again, and find it can run normally. 6. For this case, step 5 above is already over, if the optimization mode is found in step 5, then this step continues to see if the statistics of the two tables in the two libraries are different. 7. If it is still the same in step 6, then other optimized related parameters are continued, such as Optimizer_index_cost_adj, etc. 8. If it is also the same, then go to MetaLink, Google, usually, this is an Oracle's bug, confirming whether you belong to this bug. . .
The above is to find a question when I personally process the approach, maybe some help from Newbies.
Below is some SQL operation records and memories in this case.
The InteriorID field is the type of varchar2 (100), stores some numbers or characters, the following SQL is wrong when using the To_Number function.
SQL> ALTER session set optimizer_mode = choose;
Session altered.
SQL> select interiorid, constdisplayname 2 from (select interiorid, constdisplayname 3 from globalconst 4 where globalconst = 'status') 5 where to_number (interiorid) <4 6 order by to_number (interiorid); where to_number (interiorid) <4 * ERROR at Line 5: ORA-01722: Invalid Number
The execution plan at this time is a full table scan, and since the report 1722 is wrong, it is obvious because Oracle's first step is to query all TO_NUMBER (InteriorID) <4 records, and due to the interiorID field contains non-numbers Character, so error.
SQL> ALTER Session Set Optimizer_Mode = first_ROWS;
Session altered.
SQL> select interiorid, constdisplayname 2 from (select interiorid, constdisplayname 3 from globalconst 4 where globalconst = 'status') 5 where to_number (interiorid) <4 6 order by to_number (interiorid);
InteriorID constdisplayName ---------------------------------------------------------------------------------------------------------------------------- - 0 Normal 1 Sales 2 Freezing 3 Locks Execution Plan ----------------------------------- --------------------- 0 Select Statement Optimizer = first_ROWS (COST = 5 card = 1 bytes = 2)
1 0 Sort (ORDER BY) (COST = 5 Card = 1 Bytes = 22) 2 1 Table Access (By Index Rowid) of 'GlobalConst' (COST = 3 C Ard = 1 Bytes = 22)
3 2 Index (Range Scan) of 'PK_GLOBALCONST' (COST = 2 CARD = 1)
Modify the optimization mode, SQL starts using PK to index scanning, the index is the joint primary key consisting of GlobalConst InteriorID, because all records of globalconst = 'status' is indeed a number, so this SQL is implemented normally.
SQL> ALTER session set optimizer_mode = choose;
Session altered.
SQL> Select InteriorId, ConstisplayName 2 from Globalconst 3 Where globalconst = 'status' 4 and to_number (interior) <4 5 ORDER BY 1;
InteriorID constdisplayName ---------------------------------------------------------------------------------------------------------------------------- --0 Normal 1 Sales 2 Freezing 3 Lock
Execution Plan ------------------------------------------------ ---------- 0 Select Statement Optimizer = Choose (COST = 3 Card = 1 Bytes = 22) 1 0 Sort (ORDER BY) (COST = 3 Card = 1 Bytes = 22) 2 1 Table Access Full) Of 'globalconst' (COST = 1 card = 1 byte s = 22)
We change SQL to a way of writing, although the execution plan is still a full table scan, but it can be speculated that Oracle uses globalconst = 'status' as a condition of Filter, and the re-judgment of the condition is to be the need to n_Number (InteriorID) <4, Because the record InteriorID field of GlobalConst = 'status' is a number, SQL is performed normally. Suppose we reintert a globalconst = 'status' and InteriorId is not a digital record, and then execute SQL again, it will be reported again 1722 error. SQL> ALTER Session Set Optimizer_Mode = first_ROWS;
Session altered.
SQL> SELECT TO_NUMBER (InteriorID), ConstisplayName 2 from Globalconst 3 Where globalconst = 'status' 4 and to_number (interior) <4 5 ORDER BY 1;
TO_NUMBER (InteriorID) ConstisplayName ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------- 0 Normal 1 Sales 2 Freezing 3 Lock
Execution Plan ------------------------------------------------ ---------- 0 Select Statement Optimizer = first_ROWS (COST = 5 card = 1 bytes = 2 2)
1 0 Sort (ORDER BY) (COST = 5 Card = 1 Bytes = 22) 2 1 Table Access (By Index Rowid) of 'GlobalConst' (COST = 3 C Ard = 1 Bytes = 22)
3 2 Index (Range Scan) of 'PK_GLOBALCONST' (COST = 2 CARD = 1)
Modify the optimization mode, SQL starts using PK to index scanning. At this time, SQL is not before modifying, naturally it can be performed normally.
