DSXchange

Hello there,
Is it possible to build a 'Hash file' with 20 million rows. Does it reach the limit of hash file size of 2GB. Can anyone clear my doubt.
Thanx in advance

-Yamini

Yes, it's possible.

Whether it reaches the 2GB limit obviously depends on the length of the rows.

If it's likely to exceed 2GB, create a hashed file that uses 64-bit internal addressing; the 2GB limit then goes away (at the cost of a small amount of additional storage overhead). Theoretical maximum size is then approximately 19 million TB. That's not a challenge! Some operating systems do not support files this large.

20 million rows, how many average characters per row? If each row is 100 characters, then you need o store 20 X 100 million characters, which I would guess exceeds 2.2 gigabytes.

So, you need to do a little "data profiling" and figure out if your average row will keep you in 32-bit sizing or send to you 64-bit heaven. You may consider a "partitioned" hash file arrangement, or go for the gusto of a "distributed" hash file. Both terms are discussed in various threads on the forum as well as configuring for 64-bit usage which is often simpler in use.

To estimate the hash file sixe, how can i find the length of the record in Oracle?

Thx in Adv

Hi,
Just wanted to point on the previous post, that how much it takes in the DB is not always the same size in your hash file.
to get the average size in the DB you can ask your DBAs.
to get the size in a hash file you can write representative rows to a hash file and use the ANALIZE.FILE to get this info.

IHTH,

Also, if you have a tool like TOAD, it can compute the Average Record Length for you - either based on the metadata, or (more accurately) by scanning the contents of the table.

Hello trokosz,
Thank you very much for your posting.
-Yamini