Re: Oracle and JDBC Timestamp Types Confusion

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.

Hm... But timestampid is the *leading* column of the index and the index is sorted ascending for all fields (which, as I understand, is default behavior for Oracle). So although timestampid is not unique, an in order run through the index will yield monotonically increasing timestamps. By this it should be possible to do a range scan to find all matching records. I'm sorry, but I still fail to see the reason why this doesn't happen.

Kind regards

    robert Received on Fri Aug 12 2005 - 03:06:32 CDT