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

i have queries of form:

select * main.my_table      id in (select id other.other_table type = 'banana'); 

where id indexed on main.mytable, not indexed on other.other_table

recently these have been erroring ora-01555: snapshot old.

my understanding this due query taking long undo space.

this due being peak business season , databases being under heavy load.

the question is, if split query several queries of form:

select * main.my_table      id in (select id other.other_table type = 'banana')          , id >= 0 , id <1000;  select * main.my_table      id in (select id other.other_table type = 'banana')          , id >= 1000 , id <2000;  select * main.my_table      id in (select id other.other_table type = 'banana')          , id >= 2000 , id <3000; 

on 1 hand seems each query take less time initial query. on otherhand, seems such obvious optimisation, think oracle anyway.

why don't try optimising query doesn't take long? starters, oracle can have difficulty building query plan if use in (select...) instead of join. query return same result better query plan (i.e. join operator instead of in operator):

select * main.my_table  inner join other.other_table  on main.my_table.id = other.other_table.id , other.other_table.type = 'banana'; 

the issue if have duplicates in other.other_table on id column. result in double counting in final result set. should avoid kind of in (select....) structure if possible.