Re: Oracle and JDBC Timestamp Types Confusion

From: Robert Klemme <bob.news_at_gmx.net>
Date: Fri, 12 Aug 2005 10:36:12 +0200
Message-ID: <3m35bvF14r1seU1@individual.net>

Holger Baer wrote:
> fitzjarrell_at_cox.net wrote:
>> Robert Klemme wrote:
>>
>>> All,
>>>
>>> first I'd like to verify that my understanding of Oracle's concept
>>> of timestamp data types is correct:
>>>
>>> SQL Standard has data types DATE (date only), TIME (time only) and
>>> TIMESTAMP (date and time).
>>>
>>> Oracle 8 has DATE; this is capable of storing date *and* time
>>> information.
>>>
>>> Oracle 10 has DATE and several variants of TIMESTAMP (witout TZ,
>>> with TZ and with LOCAL TZ); all store date and time.
>>>
>>> JDBC seems to adhere to the standard distinction (see
>>> java.sql.Types for example).
>>> http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Types.html
>>>
>>> As far as I can see TIMESTAMP is the appropriate JDBC datataype to
>>> use for Oracle DATE and TIMESTAMP.
>>>
>>> Now here's the strange thing: I have a PreparedStatement "DELETE
>>> FROM tabx WHERE timestampid < ?". DB is Oracle 10g and
>>> tabx.timestampid is of type DATE. Statistics are current. The
>>> execution plan tells me that it's doing an INDEX FULL SCAN on the
>>> PK. The PK on that table has "timestampid" as leading field so I'd
>>> rather expect an INDEX RANGE SCAN here. Can anybody shed some
>>> light on this? Thanks!
>>>
>>> Kind regards
>>>
>>> robert
>>
>>
>> Your primary key uses a concatenated index, making none of the
>> individual columns unique; only the combination of the respective
>> values produces a unique key. As such, there is no 'range' to scan
>> with this index, as the same timestampid could occur anywhere in the
>> data. Were timestampid your only column in the primary key an INDEX
>> RANGE SCAN would be performed. As you have it configured timestampid
>> is not unique, thus the INDEX FULL SCAN is necessary to find any and
>> all records meeting your criteria.
>>
>>
>> David Fitzjarrell
>>
>
> Timestampid is the leading column, so an INDEX RANGE SCAN seems a
> possible
> path. I'd rather assume that missing/outdated statistics or the
> optimizer
> asuming a wrong value for the bind variable are leading to the full
> scan.

As statistics were current (I updated them immediately before) the guessed bind variable value seems the most realistic explanation. I had half conscious considered this but didn't follow this path of thought. Do you know a way to influence this guess that does not modify the SQL statment? (The SQL is used with different db products so index hints in the statment are not an option - at least not without some larger changes.)

<snip>test code</snip>

>> explain plan for select * from test where timestampid < to_date
>> (:dateid, 'DD-MON-YYYY');
>
> Explained.
>
>> select * from table (dbms_xplan.display);
>
> PLAN_TABLE_OUTPUT
> ------------------------------------------------------------------------

---

> Plan hash value: 2561107608
>
> ------------------------------------------------------------------------

---

>> Id | Operation | Name | Rows | Bytes | Cost
>> (%CPU)| Time |
> ------------------------------------------------------------------------

---

>> 0 | SELECT STATEMENT | | 500 | 18500 | 4
>> (0)| 00:00:01 | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 500
>> | 18500 | 4 (0)| 00:00:01 | * 2 | INDEX RANGE SCAN
>> | IDX_TEST | 90 | | 2 (0)| 00:00:01 |
> ------------------------------------------------------------------------

---

>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - access("TIMESTAMPID"<TO_DATE(:DATEID,'DD-MON-YYYY'))
> filter("TIMESTAMPID"<TO_DATE(:DATEID,'DD-MON-YYYY'))
>
> 15 rows selected.
>
>> select count (*) from test where timestampid < to_date (:dateid,
>> 'DD-MON-YYYY');
>
> COUNT(*)
> ----------
> 9796
>
> So the INDEX RANGE SCAN is a possible path, your post sounded like it
> wasn't (and note how the CBO wrongly guessed the outcome of to_date).

You'll have to help me here, where do you see this?

> Unfortunately SQL*Plus doesn't have support a date type, so I can't
> make the demonstration more similar to the OPs situation.

No problem, I think I got your point. Many thanks!

Kind regards

    robert Received on Fri Aug 12 2005 - 03:36:12 CDT