RE: Force implicit data conversion
Date: Mon, 2 Mar 2015 14:05:53 -0500
Message-ID: <56db01d0551b$e8d697c0$ba83c740$_at_rsiz.com>
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 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> _at_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_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:05:53 CET