Fair enough, the OP did specify 8i which I haven't got for testing on, and it never occurred to me to test on RBO.
I was just making the point that "x IN(y,z)" is the same as, and is rewritten into, "(x = y OR x = z)", in much the same way as "x BETWEEN y AND z" becomes "x >= y AND x <= z", both of which can be seen in Explain Plan output. The OP asked "does Oracle translate the IN clause into OR statements?" and the answer is yes, always - unless you are using RBO which you shouldn't be unless you are still on version 7, which you aren't.
The IN-List Iterator discussion is separate. All it means is the optimizer iterates over the items in an indexed OR list, perhaps repeating some common condition, and (I'm assuming, though the docs don't seem too clear on this) combines rowids at the end. Whether the OR list was arrived at via an IN list or was actually written with OR makes no difference.
Also it is perfectly possible for a query to scan a table once while checking for multiple conditions as it goes, so even in the absence of an IN-List iterator operation an OR list does not necessarily require multiple scans of the same table, which I thought was what you meant by "you are forced to run the queries several times".
Click Here to Expand Forum to Full Width