Re: Force implicit data conversion
Date: Mon, 02 Mar 2015 19:11:36 +0000
Message-ID: <748395454.1980070.1425323496474.JavaMail.yahoo_at_mail.yahoo.com>
Ok, thanks Mark, I appreciate the detail in your responses. At least for this case, it seems that the date field (the parameter) is being converted to a timestamp and then compared with the timestamp column (as we would expect). Interesting that the documentation is so contrary: "
The following rules govern the direction in which Oracle Database makes implicit datatype conversions:During SELECT FROM operations, Oracle converts the data from the column to the type of the target variable." Because it seems to be, at best, unreliable to expect a specific direction of implicit conversion, I'm happy saying it can't be done to the interested parties. Thanks,
Stephen
From: Mark W. Farnham <mwf_at_rsiz.com>
To: 'stephen van linge' <swvanlinge_at_yahoo.com>; dmarc-noreply_at_freelists.org; 'Oracle-L Freelists' <oracle-l_at_freelists.org>
Sent: Monday, March 2, 2015 11:05 AM
Subject: RE: Force implicit data conversion
#yiv7562869581 #yiv7562869581 -- _filtered #yiv7562869581 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv7562869581 {font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;} _filtered #yiv7562869581 {font-family:HelveticaNeue;}#yiv7562869581 #yiv7562869581 p.yiv7562869581MsoNormal, #yiv7562869581 li.yiv7562869581MsoNormal, #yiv7562869581 div.yiv7562869581MsoNormal {margin:0in;margin-bottom:.0001pt;font-size:12.0pt;}#yiv7562869581 a:link, #yiv7562869581 span.yiv7562869581MsoHyperlink {color:blue;text-decoration:underline;}#yiv7562869581 a:visited, #yiv7562869581 span.yiv7562869581MsoHyperlinkFollowed {color:purple;text-decoration:underline;}#yiv7562869581 p.yiv7562869581MsoAcetate, #yiv7562869581 li.yiv7562869581MsoAcetate, #yiv7562869581 div.yiv7562869581MsoAcetate {margin:0in;margin-bottom:.0001pt;font-size:8.0pt;}#yiv7562869581 p.yiv7562869581msonormal, #yiv7562869581 li.yiv7562869581msonormal, #yiv7562869581 div.yiv7562869581msonormal {margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv7562869581 p.yiv7562869581msochpdefault, #yiv7562869581 li.yiv7562869581msochpdefault, #yiv7562869581 div.yiv7562869581msochpdefault {margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv7562869581 span.yiv7562869581msohyperlink {}#yiv7562869581 span.yiv7562869581msohyperlinkfollowed {}#yiv7562869581 span.yiv7562869581emailstyle17 {}#yiv7562869581 p.yiv7562869581msonormal1, #yiv7562869581 li.yiv7562869581msonormal1, #yiv7562869581 div.yiv7562869581msonormal1 {margin:0in;margin-bottom:.0001pt;font-size:12.0pt;}#yiv7562869581 span.yiv7562869581msohyperlink1 {color:blue;text-decoration:underline;}#yiv7562869581 span.yiv7562869581msohyperlinkfollowed1 {color:purple;text-decoration:underline;}#yiv7562869581 span.yiv7562869581emailstyle171 {color:#1F497D;}#yiv7562869581 p.yiv7562869581msochpdefault1, #yiv7562869581 li.yiv7562869581msochpdefault1, #yiv7562869581 div.yiv7562869581msochpdefault1 {margin-right:0in;margin-left:0in;font-size:10.0pt;}#yiv7562869581 span.yiv7562869581BalloonTextChar {}#yiv7562869581 span.yiv7562869581EmailStyle29 {color:#1F497D;}#yiv7562869581 .yiv7562869581MsoChpDefault {font-size:10.0pt;} _filtered #yiv7562869581 {margin:1.0in 1.0in 1.0in 1.0in;}#yiv7562869581 div.yiv7562869581WordSection1 {}#yiv7562869581 Well I doubt you can. Consider this: IF the column’s type is X and the parameter’s type is Y, then it would mean Oracle would have to run a type conversion on each returned tuple (whether a filter or an index access). For zero rows it is moot, for one row it is about a tie, and for more than one row converting the parameter always wins. So I doubt Oracle would ever convert the column type without an explicit cast. I’m thinking it might even be considered a performance bug if they did. It could get more interesting if you were talking about type comparisons between disparate types of columns on a join. Then costing might involve whether or not there were hash and index opportunities and different numbers of candidates between the sources to be compared. But I’d be surprised if Oracle ever voluntarily (meaning implicitly) converted the column in a column versus parameter mismatch. mwf
From: "" <dmarc-noreply_at_freelists.org> (Redacted sender "swvanlinge_at_yahoo.com" for DMARC)
Sent: Monday, March 02, 2015 1:38 PM
To: mwf_at_rsiz.com; dmarc-noreply_at_freelists.org; 'Oracle-L Freelists'
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:11:36 CET