VarChar in Hash Files

jpr196 · Post by **jpr196** » Thu Aug 21, 2008 3:02 pm

Hi All,

We are working with a large data set and have concerns over Hashed File Sizes. I have done my research and understand it is not recommended to change the uvconfig file. So, I'm trying to gather some analysis on which files we will need to explicitely resize to 64bit.

When doing some analysis, I've found that some of our files are 480mb with around 5 million rows. The hashed files have 4 columns: VarChar 999 (stores key columns), varchar 5, varchar 16, timestamp 19. Assuming the Key columns combined were 50 characters, does having the varchar 999 make a difference in the size? In other words, would I see a smaller file if I used Varchar 100 or Char 50.

(By the way, these are delivered hashed files...in case you're wondering why they are set to varchar 999 for the key field.)
Thanks in advance!

mikegohl · Post by **mikegohl** » Thu Aug 21, 2008 3:14 pm

Delivered by whom? PeopleSoft?

ray.wurlod · Post by **ray.wurlod** » Thu Aug 21, 2008 3:17 pm

480MB is not a problem. VarChar(999) may be, since the default maximum key size in a hashed file is 768.

Hashed files need to be 64-bit only if there is any likelihood they will exceed 2GB in size.

The size of the VarChar does not have any impact on storage in a hashed file; only the required amount of space is used.

jpr196 · Post by **jpr196** » Thu Aug 21, 2008 3:21 pm

Yes, these are peoplesoft delivered Hashed Files.

mikegohl · Post by **mikegohl** » Thu Aug 21, 2008 3:43 pm

I would recommend you open a case with Oracle. I'm sure you are not the first customer that has run into this limitation.

ray.wurlod · Post by **ray.wurlod** » Thu Aug 21, 2008 4:04 pm

mikegohl wrote:I would recommend you open a case with Oracle. I'm sure you are not the first customer that has run into this limitation.

I take it you mean Oracle as the owners of PeopleSoft?

mikegohl · Post by **mikegohl** » Thu Aug 21, 2008 4:06 pm

Exactly!

chulett · Post by **chulett** » Thu Aug 21, 2008 4:06 pm

What issue are we discussing with Oracle? As Ray has already noted, dropping the size of the varchar field will not result in a smaller hashed file and 480MB is not overly large.

jpr196 · Post by **jpr196** » Thu Aug 21, 2008 4:07 pm

My take is the varchar 999 is not impacting the size (although it may have other issues, but I'm pretty sure no keys come near the 768 mark), correct? I don't think our limitation is an Oracle concern considering all DataStage Hashed files are 32bit when created.

mikegohl · Post by **mikegohl** » Thu Aug 21, 2008 4:09 pm

When there are issues with PeopleSoft delivered jobs, Oracle has the responsibility to fix them and send patch to all customers. This keeps the customers from needing to make changes every time there is a new release.

chulett · Post by **chulett** » Thu Aug 21, 2008 4:18 pm

That's the thing... I don't see an issue here.

chulett · Post by **chulett** » Thu Aug 21, 2008 4:19 pm

jpr196 wrote:but I'm pretty sure no keys come near the 768 mark), correct?

You would get failures if you attempted to create a key over the limit.

jpr196 · Post by **jpr196** » Thu Aug 21, 2008 4:33 pm

Thanks for the info, I wasn't aware of the Key constraint in hashed files before. I've never seen issues with this in our runs, so I think it's a non-issue. I was just pointing out one example with 5mil/480mb, but we do have many files that will come near or exceed the 2gb mark. I wanted to make sure we've optimized the file structures or atleast not hinder our file capacity before resizing files to 64bit. Thanks for the help guys!