Re: Oracle and JDBC Timestamp Types Confusion

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 Received on Thu Aug 11 2005 - 16:31:17 CDT