Re: snapshot too old - between dates vs and

On May 18, 4:22 am, ton de w <ton_de_win..._at_yahoo.co.uk> 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

Answer to your between question:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/opt_ops.htm#1005356 Easily found by looking BETWEEN up in the master index at tahiti.oracle.com.

Also remember, any concurrency issue like ORA-1555 may depend on fixing other queries than yours.

jg

--
@home.com is bogus.
At least the computer didn't print anything about the nap:
http://www.cnn.com/2007/WORLD/americas/04/19/canada.couch.ap/index.html