/*************************************************
First, the topic: ORACLE try not to use an implicit conversion
Two ways to type conversions: Display type conversion (EXPLICIT) automatic type conversion (implicit)
*************************************************/
=============================================================================
1.1. Display conversion more readable;
SELECT to_date (' 1900-01-01 ', ' yyyy-mm-dd ') from dual;--correct
--Contrast
SELECT to_date (sysdate, ' YYYYMMDD ') from dual;--Error: Invalid month
ALTER SESSION SET nls_date_format= ' yyyymondd ';
SELECT to_date (sysdate, ' YYYYMMDD ') from dual;--at this time correct: 2014-6-26
-This modification only modifies the status of this login. Do not modify other sessions.
1.2. Automatic type conversion has a bad effect on performance
--
CREATE TABLE TEST1
(
ID Number (1) Not NULL PRIMARY KEY,
NAME VARCHAR (10)
);
CREATE unique INDEX idx_test1 on TEST1 (NAME);
INSERT into TEST1 VALUES (0, ' 0 ');
INSERT into TEST1 VALUES (1, ' 1 ');
INSERT into TEST1 VALUES (2, ' 2 ');
INSERT into TEST1 VALUES (3, ' A ');
INSERT into TEST1 VALUES (4, ' B ');
INSERT into TEST1 VALUES (5, ' a ');
INSERT into TEST1 (ID) VALUES (7);
--
SELECT * from TEST1 WHERE NAME = 1;--
/* Although the error will be, the execution plan can be viewed
Execution plan: View not walking index, may mislead people
SELECT STATEMENT, GOAL = all_rows 2 1 20
TABLE ACCESS Full SYS TEST1 2 1 20
*/
SELECT * from TEST1 WHERE NAME = ' 1 ';
/* Execution Plan: Walk index
SELECT STATEMENT, GOAL = all_rows 0 1 20
TABLE ACCESS by INDEX ROWID SYS TEST1 0 1 20
INDEX UNIQUE SCAN SYS idx_test1 0 1
*/
1.3. Automatic conversion depends on the context, and once the context changes, the original program may not run.
Changes to the 1.4.ORACLE version (automatic type conversion rules, algorithms,) can also cause problems with automatic type conversions.
1.5. Automatic type conversion is the same as displaying the conversion time, the best way is to avoid the conversion,
Also, the Lvalue (column) is best not to convert the type, which is generally converted to an rvalue (value).
Date as an example: to_char (datecol) = ' datestring ', incorrect notation, which causes the index to fail;
change to the following so that it is expected,
Datecol = to_date (datestring, ' yyyy-mm-dd ')
================================================== ===========================