Re: Force implicit data conversion
Date: Thu, 5 Mar 2015 10:37:16 +0100
Message-ID: <CA+S=qd3LczsHwRkC9E_UYjWcgbaRUursgQSBMFDmS=3xjU9CpQ_at_mail.gmail.com>
Hi, Norman
You say: "*it looks like Oracle will convert the column's data to the literal's data type*"...
No, that is not what you can conclude from those docs. As has been
explained before in this thread, you must take note of the operative
word *target
variable* in this doc line:
During SELECT FROM operations, Oracle converts the data from the column to the type of the *target variable*.
What is meant here is the columns in the SELECT list that will be converted to the datatype of the *target* - that is either the client program variables the columns of the SELECT is bound to. If the client is PL/SQL, it means the datatypes of the variables in the INTO clause.
When we are talking *comparisons*, it is not really a matter of whether you are comparing two columns or a column and a literal. What matters is which side of the comparison has the highest precision and which has the lowest precision. The side that has the lowest precision is "implicitly converted" to the higher precision - otherwise you would get wrong results due to rounding.
Say you have a column NUM of type NUMBER(10,2) that has the value 1.23. You compare NUM = 1.234. That will be compared as 1.230 = 1.234 - the column with precision 2 is "implicitly converted" to precision 3. You compare NUM = 1.2. That will be compared as 1.23 = 1.20 - the literal with precision 1 is "implicitly converted" to precision 2.
Now for numbers like that - or VARCHAR2(10) compared to VARCHAR2(2) - it does not have impact, as the precision here is "just a restraint", not a matter of datatype representation in the datafile.
But the same *principle* is at stake with DATE and TIMESTAMP. When
comparing those two, TIMESTAMP is considered higher precision than DATE.
If you compare a DATE to a TIMESTAMP, the DATE will be implicitly converted
to a TIMESTAMP with zero fractional seconds, no matter which is a column,
literal or expression.
That's the only way to avoid false results due to rounding.
Consider:
create table tab1 (
dt date
, ts timestamp
)
/
create index tab1_dt on tab1 ( dt )
/
create index tab1_ts on tab1 ( ts )
/
insert into tab1
select date '2000-01-01' + numtodsinterval((level-1),'day') as dt
, timestamp '2000-01-01 00:00:00' + numtodsinterval((level-1),'day')
+ numtodsinterval((level-1)/1000,'second') as ts
from dual
connect by level <= 1000
/
commit
/
begin
dbms_stats.gather_table_stats(user,'TAB1',cascade=>true);
end;
/
If I compare the DATE column with a DATE literal, no problem, of course:
select *
from tab1
where dt = DATE '2000-01-01'
/
DT TS ------------------- -----------------------2000-01-01 00:00:00 2000-01-01 00:00:00.000
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 19 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TAB1_DT | 1 | |1 (0)| 00:00:01 |
Predicate Information (identified by operation id):
2 - access("DT"=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') )
If I compare the TIMESTAMP column with a DATE literal, the DATE literal is implicitly converted to a TIMESTAMP literal:
select *
from tab1
where ts = DATE '2000-01-01'
/
DT TS ------------------- -----------------------2000-01-01 00:00:00 2000-01-01 00:00:00.000
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 19 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TAB1_TS | 1 | |1 (0)| 00:00:01 |
Predicate Information (identified by operation id):
2 - access("TS"=TIMESTAMP' 2000-01-01 00:00:00')
If I compare the DATE column to a TIMESTAMP literal, the DATE column is implicitly converted to TIMESTAMP, forcing a full table scan:
select *
from tab1
where dt = TIMESTAMP '2000-01-01 00:00:00'
/
DT TS ------------------- -----------------------2000-01-01 00:00:00 2000-01-01 00:00:00.000
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | TABLE ACCESS FULL| TAB1 | 1 | 19 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - filter(INTERNAL_FUNCTION("DT")=TIMESTAMP' 2000-01-01
00:00:00.000000000')
It also follows that since the DATE column is implicitly converted to TIMESTAMP, if the TIMESTAMP contains fractional seconds, nothing will be found, but it will still full table scan:
select *
from tab1
where dt = TIMESTAMP '2000-01-02 00:00:00.001'
/
no rows selected
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | TABLE ACCESS FULL| TAB1 | 10 | 190 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - filter(INTERNAL_FUNCTION("DT")=TIMESTAMP' 2000-01-02
00:00:00.001000000')
And in such cases you may (if that is the result you desire) need to explicitly convert "in the other direction", which looses the fractional seconds:
select *
from tab1
where dt = cast(TIMESTAMP '2000-01-02 00:00:00.001' as date)
/
DT TS ------------------- -----------------------2000-01-02 00:00:00 2000-01-02 00:00:00.001
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 19 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TAB1_DT | 1 | |1 (0)| 00:00:01 |
Predicate Information (identified by operation id):
2 - access("DT"=CAST(TIMESTAMP' 2000-01-02 00:00:00.001000000' AS date))
All of the above would have been exactly the same if I had not used literals but instead compared to another column (whether the same table or another in a join.)
The lowest precision is implicitly converted to the higher precision to
avoid "false results" due to rounding.
When using literals, if it is the literal that has the lowest precision
(i.e. DATE), no problem, no impact, it will just be converted to a literal
of the higher precision (TIMESTAMP.)
The case to worry about is when the literal has the highest precision (i.e.
TIMESTAMP), because then it will be the column (DATE) that will be
converted, and that may very well make an impact ;-)
But you may also have same negative impact comparing two columns or a column and an expression if one side of the comparison is DATE and the other side is TIMESTAMP and you expect an index range scan on the DATE and see a full table scan instead.
Regards
Kim Berg Hansen
http://dspsd.blogspot.com
kibeha_at_gmail.com
_at_kibeha
On Wed, Mar 4, 2015 at 9:22 PM, Norman Dunbar <oracle_at_dunbar-it.co.uk> wrote:
> Ok, I looked, and found the docs ... > > 11GR2 at > http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements002.htm > 12C at http://docs.oracle.com/database/121/SQLRF/sql_elements002.htm > > And I quote ... > > Implicit and Explicit Data Conversion > > Oracle recommends that you specify explicit conversions, rather than rely > on implicit or automatic conversions, for these reasons: > > · SQL statements are easier to understand when you use explicit data type > conversion functions. > · > · Implicit data type conversion can have a negative impact on performance, > especially if the data type of a column value is converted to that of a > constant rather than the other way around. > · > · Implicit conversion depends on the context in which it occurs and may > not work the same way in every case. For example, implicit conversion from > a datetime value to a VARCHAR2 value may return an unexpected year > depending on the value of the NLS_DATE_FORMAT parameter. > · > · Algorithms for implicit conversion are subject to change across software > releases and among Oracle products. Behavior of explicit conversions is > more predictable. > · > · If implicit data type conversion occurs in an index expression, then > Oracle Database might not use the index because it is defined for the > pre-conversion data type. This can have a negative impact on performance. > > ... (Ignore conversion table here) ... > > · During INSERT and UPDATE operations, Oracle converts the value to the > data type of the affected column. > · > · During SELECT FROM operations, Oracle converts the data from the column > to the type of the target variable. > > ... > > So, it looks like Oracle will convert the column's data to the literal's > data type, if necessary, and this is going to affect all the rows selected. > > > > Cheers, > Norm. > > -- > Sent from my Android device with K-9 Mail. Please excuse my brevity. >
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 05 2015 - 10:37:16 CET