Oracle and JDBC Timestamp Types Confusion

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 Received on Thu Aug 11 2005 - 04:56:26 CDT