Re: Oracle and JDBC Timestamp Types Confusion

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Fri, 12 Aug 2005 10:56:06 +0200
Message-ID: <ddho78$f7q$1@news.BelWue.DE>

Robert Klemme wrote:
> Holger Baer wrote:

[..]

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

Depending on your Oracle Version, the CBO might or might not peek at the bind variables, (only at the first execution) so this is going to be tricky.

And you don't always want the INDEX RANGE SCAN, because depending on the amount of data, even the table FULL SCAN can be appropriate.

But short of using stored outlines I can't think of an appropriate way to change the behaviour. Except perhaps make sure that the first execution of your Prepared Statement uses a value for the bind variable that makes an INDEX RANGE SCAN the correct path - the same plan will be used through out the session (although I believe that changed somewhere in 10g).

> <snip>test code</snip>

>
> ----------------
>

>>>  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?

Optimizer guess: 500 rows. Actual rows retrieved: ~10000
>
>

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

You're welcome.

Cheers
Holger Received on Fri Aug 12 2005 - 03:56:06 CDT