Would splitting a query by an indexed ID field avoid ORA-01555: snapshot too old error?

Question

I have some queries of the form:

select * from MAIN.MY_TABLE 
    where ID in (select ID from OTHER.OTHER_TABLE where type = 'BANANA');

Where ID is indexed on MAIN.MYTABLE, but not indexed on OTHER.OTHER_TABLE

Recently these have been erroring with ORA-01555: snapshot too old.

My understanding is this this due to the query taking too long for the undo space.

This is likely to be due it being peak business season and the databases being under heavy load.

The question is, if I were to split the query into the several queries of the form:

select * from MAIN.MY_TABLE 
    where ID in (select ID from OTHER.OTHER_TABLE where type = 'BANANA') 
        and ID >= 0 and ID <1000;

select * from MAIN.MY_TABLE 
    where ID in (select ID from OTHER.OTHER_TABLE where type = 'BANANA') 
        and ID >= 1000 and ID <2000;

select * from MAIN.MY_TABLE 
    where ID in (select ID from OTHER.OTHER_TABLE where type = 'BANANA') 
        and ID >= 2000 and ID <3000;

On one hand this seems like each query would take less time than initial query. On the otherhand, it seems like such an obvious optimisation, that I would think that Oracle would do this anyway.

Answer 1

Why don't you try optimising the query so it doesn't take so long? For starters, Oracle can have some difficulty building a good query plan if you use IN (SELECT...) instead of a join. Does this query return the same result with a better query plan (i.e. a JOIN operator instead of an IN operator):

select * from MAIN.MY_TABLE 
inner join
OTHER.OTHER_TABLE 
on MAIN.MY_TABLE.ID = OTHER.OTHER_TABLE.ID
and OTHER.OTHER_TABLE.type = 'BANANA';

The only issue is if you have duplicates in OTHER.OTHER_TABLE on the ID column. This will result in double counting in the final result set. But you should really avoid that kind of IN (SELECT....) structure if possible.

Answer 2

How many rows this query suppose to return comparing to number of rows in MY_TABLE?

Index on MY_TABLE.ID will help if number of rows in number of rows in (select distinct ID from OTHER.OTHER_TABLE where type = 'BANANA') ~2-5% of number of rows in MY_TABLE.

So I'd check that numbers first. And I would try query like this:

select * 
from MAIN.MY_TABLE a,
     (select distinct ID from OTHER.OTHER_TABLE where type = 'BANANA') b
where a.id = b.id;

If true, and index on MY_TABLE is used (according to execution plan), I'd check how fast (select distinct ID from OTHER.OTHER_TABLE where type = 'BANANA') is executed:

select count(1)
from
(select distinct ID from OTHER.OTHER_TABLE where type = 'BANANA')

P.S. To get real execution plan in sqlplus:

set linesize 200
set autotrace traceonly
    select * 
    from MAIN.MY_TABLE a,
         (select distinct ID from OTHER.OTHER_TABLE where type = 'BANANA') b
    where a.id = b.id;