Re: Force implicit data conversion
Date: Mon, 02 Mar 2015 19:30:52 +0000
Message-ID: <2073230664.1915398.1425324651941.JavaMail.yahoo_at_mail.yahoo.com>
Ah, I understand now. Thanks Kim and Mark. I tend to live on the SQL level, so "variable" means something different to me. Suppose I should be more PL/SQL minded by now...
Thanks,
Stephen
From: Kim Berg Hansen <kibeha_at_gmail.com>
To: dmarc-noreply_at_freelists.org
Cc: "mwf_at_rsiz.com" <mwf_at_rsiz.com>; Oracle-L Freelists <oracle-l_at_freelists.org>
Sent: Monday, March 2, 2015 11:19 AM
Subject: Re: Force implicit data conversion
Hi, Stephen
The bit of the documentation you are refering to is this one, right?
- During SELECT FROM operations, Oracle converts the data from the column to the type of the target variable. As I read that when it states "target variable", that's the variables (PL/SQL, client bind variables, ...) that you are selecting into. Not applicable to your case. I can't find the documentation, but as far as I know the rule for implicit conversion in comparisons is, that rounding/truncation is avoided if possible. Meaning that the variable or column with the least precision is converted to the variable or column with the highest precision and thus avoid any rounding. If one side of the comparison is a TIMESTAMP and the other a DATE, the DATE would be implicitly considered TIMESTAMP. So your equal comparison of a TIMESTAMP column with a DATE variable should only find TIMESTAMP values that has exactly zero milliseconds. The converse situation - a DATE column and a TIMESTAMP variable - has tricked people before, when they see that their index on DATE column isn't used. For example WHERE mydatecol = TIMESTAMP '2015-03-02 20:00:00' would be implicitly like WHERE cast(mydatecol as timestamp) = TIMESTAMP '2015-03-02 20:00:00' and force full table scan (unless you have a function based index.) So I think there isn't really any reason for Oracle to show which of the sides of the comparison was implicitly converted, as the rule always is to do it whichever way will avoid rounding.
So if you have a DATE variable and need to find all rows where the TIMESTAMP column has that value to the second, no matter what millisecond value it may have, you can do something like: WHERE timestampcol >= datevariableAND timestampcol < datevariable + interval '1' second Or you could potentially do something like: WHERE cast(timestampcol as date) = datevariable But that would preclude using index on timestampcol (unless you build functionbased index for this purpose.)
Regards
Kim Berg Hansen
http://dspsd.blogspot.comkibeha_at_gmail.com_at_kibeha
On Mon, Mar 2, 2015 at 7:51 PM, stephen van linge <dmarc-noreply_at_freelists.org> wrote:
I'm on Oracle 11gR2 (11.2.0.4) and the documentation (http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements002.htm#SQLRF00214) says it'll convert the column to the variable's data type. However, if I make the variable (date) equal to the timestamp column with ms's either rounded or chopped, it doesn't pull up the record, which makes me think it might not be the case.
I'll try the other way around (find a timestamp with 0 ms and see if the date field will be converted to timestamp). Weird.
Thanks,
Stephen
From: stephen van linge <dmarc-noreply_at_freelists.org>
To: "mwf_at_rsiz.com" <mwf_at_rsiz.com>; "dmarc-noreply_at_freelists.org" <dmarc-noreply_at_freelists.org>; 'Oracle-L Freelists' <oracle-l_at_freelists.org>
Sent: Monday, March 2, 2015 10:38 AM
Subject: Re: Force implicit data conversion
No, I need it specifically to be an implicit conversion, aka something Oracle is doing on its' own. Also, the trace file does not show this. I just don't see a way to tell if the data types are changing in the where clause, and if they are, what's changing to what data type? A way to see this should be sufficient.
Thanks for your help,
Stephen
From: Mark W. Farnham <mwf_at_rsiz.com>
To: dmarc-noreply_at_freelists.org; 'Oracle-L Freelists' <oracle-l_at_freelists.org>
Sent: Monday, March 2, 2015 10:29 AM
Subject: RE: Force implicit data conversion
do you mean, for example using the cast function?:SQL> r 1 select 2 --+ gather_plan_statistics 3 * from time_size 4* where cast(timeuntrunc as date) > to_date('19700101','YYYYMMDD') ID DATETRUNC DATEUNTRU TIMETRUNC---------- --------- --------- ------------------------------------------------------------------------TIMEUNTRUNC--------------------------------------------------------------------------- 1 09-MAY-11 09-MAY-11 09-MAY-11 12.00.00.000000 AM09-MAY-11 09.58.59.000000 AM 3 ~ 09-MAY-11 ~09-MAY-11 10.58.51.000000 AM 1 19-DEC-12 19-DEC-12 19-DEC-12 12.00.00.000000 AM19-DEC-12 01.33.47.000000 AM 1 19-DEC-12 19-DEC-12 19-DEC-12 12.00.00.000000 AM19-DEC-12 02.01.05.000000 AM SQL> save q_cast_timeCreated file q_cast_time.sqlSQL> _at_q_xplan PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------- SQL_ID 9gqfbr6653j64, child number 0-------------------------------------select --+ gather_plan_statistics * from time_size wherecast(timeuntrunc as date) > to_date('19700101','YYYYMMDD') Plan hash value: 1526675273 ------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 3 (100)| 4 |00:00:00.01 | 8 ||* 1 | TABLE ACCESS FULL| TIME_SIZE | 1 | 1 | 3 (0)| 4 |00:00:00.01 | 8 |------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(CAST(INTERNAL_FUNCTION("TIMEUNTRUNC") AS date)>TO_DATE(' 1970-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of stephen van linge (Redacted sender "swvanlinge_at_yahoo.com" for DMARC)
Sent: Monday, March 02, 2015 1:09 PM
To: Oracle-L Freelists
Subject: Force implicit data conversion Hi all, This is an academic question. Given a table A with column B (timestamp data type) and date parameter C, how can i force an implicit conversion within a select statement of B to date type? I'm looking at this: SELECT *
FROM A
WHERE B = C and I'm not sure if B is being converted to date, or if C is being converted to timestamp... Thanks, Stephen
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Mar 02 2015 - 20:30:52 CET