Re: Force implicit data conversion

From: stephen van linge <swvanlinge_at_yahoo.com>
Date: Mon, 02 Mar 2015 18:38:15 +0000
Message-ID: <2009123638.1876147.1425321495421.JavaMail.yahoo_at_mail.yahoo.com>

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    

#yiv8671190218 #yiv8671190218 -- _filtered #yiv8671190218 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv8671190218 {font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;} _filtered #yiv8671190218 {font-family:HelveticaNeue;}#yiv8671190218 #yiv8671190218 p.yiv8671190218MsoNormal, #yiv8671190218 li.yiv8671190218MsoNormal, #yiv8671190218 div.yiv8671190218MsoNormal {margin:0in;margin-bottom:.0001pt;font-size:12.0pt;}#yiv8671190218 a:link, #yiv8671190218 span.yiv8671190218MsoHyperlink {color:blue;text-decoration:underline;}#yiv8671190218 a:visited, #yiv8671190218 span.yiv8671190218MsoHyperlinkFollowed {color:purple;text-decoration:underline;}#yiv8671190218 span.yiv8671190218EmailStyle17 {color:#1F497D;}#yiv8671190218 .yiv8671190218MsoChpDefault {font-size:10.0pt;} _filtered #yiv8671190218 {margin:1.0in 1.0in 1.0in 1.0in;}#yiv8671190218 div.yiv8671190218WordSection1 {}#yiv8671190218 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-l

Received on Mon Mar 02 2015 - 19:38:15 CET