Re: Oracle and JDBC Timestamp Types Confusion

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Fri, 12 Aug 2005 10:17:31 +0200
Message-ID: <ddhlus$cvo$1@news.BelWue.DE>

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.

Consider the following (on 10g):

SQL> create table test (timestampid date not null, owner varchar2(32) not null, object_name varchar2(30));

Table created.

SQL> create index idx_test on test (timestampid, owner);

Index created.

SQL> alter table test add constraint pk_test primary key (timestampid, owner);

Table altered.

SQL> insert into test select sysdate -(10000 - rownum) as timestampid, owner, object_name from all_objects where rownum < 10000;

9999 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats (user, 'TEST', cascade=> true);

PL/SQL procedure successfully completed.

SQL> variable dateid varchar2(11)
SQL> exec :dateid := '21-JAN-2005';

PL/SQL procedure successfully completed.

SQL> explain plan for select * from test where timestampid < to_date (:dateid, 'DD-MON-YYYY');

Explained.

SQL> 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.

SQL> select count (*) from test where timestampid < to_date (:dateid, 'DD-MON-YYYY');

   COUNT(*)

---

       9796

SQL> SQL> 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). Unfortunately SQL*Plus doesn't have support a date type, so I can't make the demonstration more similar to the OPs situation.

Regards,
Holger Received on Fri Aug 12 2005 - 03:17:31 CDT