snapshot too old - between dates vs and

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 Received on Fri May 18 2007 - 06:22:49 CDT