Re: snapshot too old - between dates vs and

ton de w wrote:
> Hello,
> I have been on the receiving end of "ORA-01555 snapshot too old
> rollback segment number 1 _SYYSSMU1$ too small. a couple of times
> while running queries against a 9i db.
>
> It seem this is because my query takes so long that, cos Oracle gives
> me a consistent read, there is no room for the rollback stuff.
>
> OK fair enough - I should speed up my query.
>
> two questions therefore:
> Does using between rather than and help?
> LastEvent between to_date('16/05/2007 00:00:00','DD/MM/YYYY
> HH24:MI:SS')
> and to_date('17/05/2007 23:59:59','DD/MM/YYYY HH24:MI:SS')
> vs
> LastEvent <to_date('17/05/2007 00:00:00','DD/MM/YYYY HH24:MI:SS')
> and
> LastEvent >= to_date('16/05/2007 00:00:00','DD/MM/YYYY HH24:MI:SS')
>
> does putting say integer comparisons first in my where clause help?
>
> does putting tests that are likely to fail towards the left in a
> series of and clause help?
>
> Sorry that's 3 questions!
>
> TIA
>
> Ton

One at a time.

The solution to the problem is to size your undo tablespace appropriately and set an appropriate undo retention time.

BETWEEN and AND have nothing to do with the issue nor does your other suggestion. Read the concept docs and review the advise on Tom Kyte's asktom.oracle.com website with respect to ORA-01555.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org