How does the ODBC driver know the difference between Date and Timestamp?
Ok, so we know ODBC is old. I'm using it in a C++ program that was initialy developed about 10 years ago. In particular it makes heavy use of the SQLBindParameter function declared in sqlext.h. Binding is good for all the parsing reasons we know and love.
SQLBindParameter only supports three type for date and time data. ODBC calls them date, time and timestamp. (Actually, there are two versions of each, but I'm not sure that matters. Correct me if I'm wrong!) The ODBC date is just a date - no time part. The ODBC timestamp is a date and time to seconds, and optionally to thousandths of seconds. Oracle, as we know, has the DATE type which can store any date, time or date-time to seconds. Oracle also has its version of TIMESTAMP, which supports millionths of seconds, and the two TIMESTAMP with time zone types.
The problem is as follows: Suppose my SQL statment in my C++ program looks like:
SELECT event_description from event WHERE event_dt BETWEEN ? and ?
(event_dt is of type DATE in Oracle, and is indexed)
I SQLBindParameter two date-times using the ODBC timestamp struct in C++ (with zero in the fractional seconds part). When Oracle runs the statement, it should interpret the two bound values as DATEs, and thus do an index range scan on the appropriate index. But if Oracle interprets the bound values as TIMESTAMPs, it views that as an instruction to implicitly convert event_dt to TIMESTAMP before evaluating the where condition. So it tablescans!
I have run into at least one situation where the Oracle Tuning Advisor has explicitly said that the query plan is not using the index on a DATE column because of this type of implicit conversion. Does this mean everything that I bind as the ODBC timestamp type, even with zero in the fractional seconds part, is treated as an Oracle TIMESTAMP instead of DATE? That would be a disaster. If there is some other clue the Oracle ODBC driver uses to tell the difference, what is it?
Thanks,
Greg