Re: Oracle and JDBC Timestamp Types Confusion

Holger Baer wrote:
> 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.

Well, we don't want to make things too simple so we try to support Oracle 8, 9 and 10. :-)

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

True. IMHO for the scenario we have here IMHO a range scan would be the most appropriate one: the table is filled with new data all the time (which contains current timestamps) and once in a while (once a day or once a week) old data is purged with the delete statement in question. Deletion is done on a day by day basis, i.e. first we delete all entries from the first day, then next day etc. up to a certain limit.

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

Hm, I'll have to check: maybe the first date we're using is the date of the oldest record so the first delete statement might not delete anything. That might have an impact on the plan...

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


Ah, ok.

Thanks again!

Kind regards

    robert