About Hashed File Memory Usage

suresh.narasimha · Post by **suresh.narasimha** » Tue Jul 22, 2008 10:29 pm

Hi Everyone,

Good Afternoon.

I'm confused with the way hashed file reads the data.

1. If i use a hashed file as a lookup and i donot check option " Preload file to memory " ....does it pull all the data to memory or reads data from the physical file location for each row ?

2. If it reads data from the physical location for every row...which will give me a better performance between hashed file and OCI..if i have 1,00,000 rows.

Please suggest..

Thanks ..

saikir · Post by **saikir** » Tue Jul 22, 2008 10:51 pm

Hi,

If i understand correctly you want to do a look up in a server edition. In a server edition you can do a lookup using only a hashed file.

Pre-load into memory: Once enabled the hashed file will be read into memory once the job is run. It can certainly improve the performance.

However, if you are hashed file is exceeding your memory then you will have a problem.

Sai

suresh.narasimha · Post by **suresh.narasimha** » Tue Jul 22, 2008 10:58 pm

Hi Saikir,

Thanks.

My question is if i donot enable preload file to memory how does a hashed file behave ?

Regards

saikir · Post by **saikir** » Tue Jul 22, 2008 11:06 pm

Hi,

If you dont have the option enabled, it will not load the entire hashed file into memory. It will read from the hashed file row by row.

However, if you have a large amount of data to do lookups on and if you can create an intermediate table in your database then load the data into a table and do a equi join. As join in a database is a set based operation and will be much faster.

Sai

ray.wurlod · Post by **ray.wurlod** » Tue Jul 22, 2008 11:07 pm

If you do not enable pre-load to memory each record is logically read from disk. In practice very few of these I/Os will be physical I/Os, because the relevant pages are likely to be in memory - either main memory or in the disk controller's memory.

Even so a hashed file is likely to out-perform even a local Oracle instance because of the way each finds its records. A hashed file uses a hashing algorithm to calculate the actual direct address of the key it needs to retrieve. Oracle (and every other database that don't use hashed file terminology) has the overhead of having to search a primary key index to determine that location.

suresh.narasimha · Post by **suresh.narasimha** » Tue Jul 22, 2008 11:20 pm

Thanks Ray.

Now i'm clear the way how hashed file behaves.

Regards