The Lookup stage is most appropriate when the reference data for all lookup stages in a job is small enough to fit into available physical memory. Each lookup reference requires a contiguous block of physical memory. If the datasets are larger than available resources, the JOIN or MERGE stage should be used.
If the reference to a Lookup is directly from a Oracle table, and the number of input rows is significantly smaller (eg. 1:100 or more) than the number of reference rows, a Sparse Lookup may be appropriate.