RE: Force implicit data conversion
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: "'stephen van linge'" <swvanlinge@xxxxxxxxx>, <dmarc-noreply@xxxxxxxxxxxxx>, "'Oracle-L Freelists'" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 2 Mar 2015 14:05:53 -0500
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@xxxxxxxxxxxxx> (Redacted sender "swvanlinge@xxxxxxxxx"
for DMARC)
Sent: Monday, March 02, 2015 1:38 PM
To: mwf@xxxxxxxx; dmarc-noreply@xxxxxxxxxxxxx; '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@xxxxxxxx>
To: dmarc-noreply@xxxxxxxxxxxxx; 'Oracle-L Freelists' <oracle-l@xxxxxxxxxxxxx>
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 AM
09-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 AM
19-DEC-12 01.33.47.000000 AM
1 19-DEC-12 19-DEC-12 19-DEC-12 12.00.00.000000 AM
19-DEC-12 02.01.05.000000 AM
SQL> save q_cast_time
Created file q_cast_time.sql
SQL> @q_xplan
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID 9gqfbr6653j64, child number 0
-------------------------------------
select --+ gather_plan_statistics * from time_size where
cast(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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of stephen van linge (Redacted sender "swvanlinge@xxxxxxxxx" 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
Other related posts:
