"Reprint" on the precedence of Oracle implicit conversions and conversions

Reproduced below from: http://blog.itpub.net/29324876/viewspace-1096741/There are both explicit type conversions (EXPLICIT) and implicit type conversions (implicit) for different types of processing in Oracle, we are controllable for explicit type conversions, but for implicit type conversions, of course, it is not recommended,

Because it is difficult to control, there are many shortcomings, but it is difficult to avoid the implicit type conversion, if we do not understand the rules of implicit type conversion, it will often change the execution plan of our SQL, which may lead to inefficiency or other problems.

  1.1 Implicit conversion occurrence Scenario

1. for INSERT and UPDATE operations,Oracle The inserted or updated values are implicitly converted to the data type of the field.

For example:

Sql> CREATE TABLE text (ID varchar2 (+), name Varchar2 (ten), age number);

Table created.

sql> INSERT into text values (' 1 ', ' Jack ', ' 18 ');

1 row created.

sql> Update text set age= ' 19 ';

1 rows updated.

Sql> select * from text;

ID NAME Age

---------- ---------- ----------

1 Jack 19

Note: INSERT into text values (' 1 ', ' Jack ', ' 18 ') equals insert into text values (, ' 1 ', ' Jack ', To_number (' 18 '))

Update text set age= ' 19 ' is equivalent to update text set Age=to_number (' 19 ')

2. When comparing the values of a character and a numeric value,Oracle implicitly converts the value of the character type to a numeric type. For example:

Sql> explain plan for select * from text where age= ' 19 ';

explained.

Sql> select * FROM table (dbms_xplan.display);

Plan_table_output

-------------------------------------------------------------------------------

Plan Hash value:738342525

--------------------------------------------------------------------------

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

--------------------------------------------------------------------------

| 0 |      SELECT STATEMENT |     |    2 |     66 | 2 (0) | 00:00:01 |

|* 1 | TABLE ACCESS full|     TEXT |    2 |     66 | 2 (0) | 00:00:01 |

--------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

1-filter ("Age" =19)

Note: SELECT * from text where age= ' 19 ' is equivalent to select * from text where Age=to_number (' 19 ')

Sql> explain plan for select * from text where id=1;

explained.

Sql> select * FROM table (dbms_xplan.display);

Plan_table_output

-------------------------------------------------------------------------------

Plan Hash value:738342525

--------------------------------------------------------------------------

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

--------------------------------------------------------------------------

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

|* 1 | TABLE ACCESS full|     TEXT |    1 |     38 | 2 (0) | 00:00:01 |

--------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

1-filter (To_number ("ID") =1)

Note: SELECT * from text where id=1; equivalent to select * from text where to_number (ID) =1

If the ID column has an index, it will expire at this time

3. when comparing data in both character and date types,Oracle Converts the character type to the date type. For example:

Sql> CREATE TABLE Table_date (varchar_date varchar2), date_date date);

Table created.

sql> INSERT into table_date values (To_char (sysdate, ' yyyy-mm-dd '), sysdate);

1 row created.

Sql> select * from Table_date;

Varchar_date date_date

-------------------- ---------

2014-02-26 26-feb-14

Sql> alter session set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss ';

Session altered.

Sql> explain plan for SELECT * from Table_date where varchar_date<sysdate;

explained.

Sql> select * FROM table (dbms_xplan.display);

Plan_table_output

-------------------------------------------------------------------------------

Plan Hash value:1510990824

--------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------

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

|* 1 | TABLE ACCESS full|     Table_date |    1 |     21 | 2 (0) | 00:00:01 |

--------------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

1-filter (Internal_function ("Varchar_date") <[email protected]!)

Note: SELECT * from Table_date where varchar_date<sysdate equals

SELECT * from Table_date where to_date (varchar_date, ' yyyy-mm-dd hh24:mi:ss ') <sysdate

Sql> explain plan for SELECT * from table_date where date_date> ' 2014-2-26 0:0:0 ';

explained.

Sql> select * FROM table (dbms_xplan.display);

Plan_table_output

-------------------------------------------------------------------------------

Plan Hash value:1510990824

--------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------

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

|* 1 | TABLE ACCESS full|     Table_date |    1 |     21 | 2 (0) | 00:00:01 |

--------------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

1-filter ("Date_date" >to_date (' 2014-02-26 00:00:00 ', ' syyyy-mm-dd

Hh24:mi:ss '))

Note: SELECT * from table_date where date_date> ' 2014-2-26 0:0:0 ' equals

SELECT * from Table_date where date_date>to_date (' 2014-2-26 0:0:0 ', ' yyyy-mm-dd hh24:mi:ss '

4. an implicit conversion is invalidated when it is on a field column. For example:

1) index is valid when an implicit conversion occurs at the end

Sql> CREATE table T1 as select object_id as ID, To_char (object_id) as vid from dba_objects;

Table created.

sql> desc T1

Name Null? Type

----------------------------------------- -------- ----------------------------

ID number

VID VARCHAR2 (40)

Sql> CREATE index T1_ind_vid on T1 (vid);

Index created.

Sql> explain plan for SELECT * from t1 where vid= ' 15612 ';

explained.

Sql> select * FROM table (dbms_xplan.display);

Plan_table_output

-------------------------------------------------------------------------------

Plan Hash value:1215445203

------------------------------------------------------------------------------------------

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

------------------------------------------------------------------------------------------

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

|  1 | TABLE ACCESS by INDEX rowid|     T1 |    1 |     35 | 2 (0) | 00:00:01 |

|* 2 | INDEX RANGE SCAN |     T1_ind_vid |       1 |     | 1 (0) | 00:00:01 |

------------------------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

2-access ("VID" = ' 15612 ')

Note: An implicit conversion does not occur to perform an index sweep correctly

2) The index is invalidated when the field column has an implicit conversion

Sql> explain plan for SELECT * from T1 where vid=15612;

explained.

Sql> select * FROM table (dbms_xplan.display);

Plan_table_output

-------------------------------------------------------------------------------

Plan Hash value:3617692013

--------------------------------------------------------------------------

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

--------------------------------------------------------------------------

| 0 |      SELECT STATEMENT |     |    1 |    11 | 48 (5) | 00:00:01 |

|* 1 | TABLE ACCESS full|     T1 |    1 |    11 | 48 (5) | 00:00:01 |

--------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

1-filter (To_number ("VID") =15612)

Note: The SELECT * from T1 where vid=15612 is equivalent to the SELECT * from T1 where to_number (vid) =15612,vid column occurs when the implicit conversion execution plan displays the end-use index of the full table scan.

  1.2 Disadvantages of implicit conversions

1. Using display type conversions makes our SQL easier to understand, which is more readable, but implicit type conversions do not have this advantage.

2. Implicit type conversions tend to have a bad effect on performance, especially if the type of the lvalue is implicitly converted to the right-value type. This approach is likely to make it possible for us to use the index instead of the index, or it may lead to a knot
Error.

3. Implicit type conversions may depend on the context in which the conversion occurs, such as the To_date (SYSDATE,FMT) in 1, and it is likely that our program will not run once the context changes.

4. An implicit type conversion algorithm or rule, which can change in the future, is dangerous, meaning that older code is likely to run into problems (performance, errors, and so on) in the new Oracle version, and display type conversions always have the highest
, the display type conversion does not have the potential to cause problems with this version replacement.

5. Implicit type conversion is to consume time, of course, the same explicit type conversion time is similar, the best way is to avoid similar conversions, in the display type conversion we will see, it is best not to convert the Lvalue to type, to
Indexes are not used, indexes are built, index storage and management overhead are increased.

  Summary

Oracle uses the precedence of data types to determine implicit type conversions, with the principle of converting low priority to high priority (data type priority is:number> character type > Date type). Implicit conversions occur in words
The index is invalidated on the segment column.


Dba_ Jian Jin
2014.2.27

"Reprint" on the precedence of Oracle implicit conversions and conversions