ORAOCI Lookup or Hash File Look Up

dsuser · Post by **dsuser** » Thu Dec 11, 2003 10:45 am

Hi,

I'd like to know if a look up to a ORAOCI stage will be faster or a look up to the Hash File(for about 20,000 records) will be faster with Pre-Load File to Memory option disabled.And Why???

Thanks,
dsuser

kduke · Post by **kduke** » Thu Dec 11, 2003 11:10 am

dsuser

This has been covered a lot. Do a search.

kcbland · Post by **kcbland** » Thu Dec 11, 2003 11:57 am

It depends. Is the reference table residing within the same instance of the source data? How is the table constructed? Would you be using SQL BETWEEN statements to do ranged selects? What is the content of data that you need to reference? A hash file could have 10 rows in it, but 1000 columns of 32K bytes, and that would be VERY inefficient. Likewise, a table lookup returning the same data is very inefficient. Will the lookup be shared across multiple jobs (either instantiated jobs or a common reference lookup).

You can't answer a question like yours without careful framing. It's like asking someone to give an short answer to a question like: Which is better, DB2 or Oracle?

kcbland · Post by **kcbland** » Thu Dec 11, 2003 12:01 pm

Here's some posts that may help you decide:

viewtopic.php?t=85569

viewtopic.php?t=85364

viewtopic.php?t=85822