Home » RDBMS Server » Performance Tuning » Storage recomendation please
Storage recomendation please [message #124025] Wed, 15 June 2005 19:08 Go to next message
Messages: 1
Registered: June 2005
Location: Sweden
Junior Member

Our database includes a unit table and a code table, the code table has three fields: UnitID, code name identifier and the code data field(varchar20). A unit can have up to 12 codes. Current qty of the codes table is 2000 million records and growth is 30 million records/month so it's quite a lot of data. The code data field is not indexed and only access is by UnitID. Most common usage is to create flat file reports requiring 12 outer joins to provide unit and codes data on a single line.

We are now in progress of upgrading from 8.1.7 to 10g and also redesigning the datamodel.

The question is if it would be better to store all codes for a unit in a single record instead, either as a delimited string or using an array type, it would decrease size and growth and maybe also increase performance.

Anyone with experience of similar solutions please advice.
Re: Storage recomendation please [message #124182 is a reply to message #124025] Thu, 16 June 2005 14:33 Go to previous message
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
An interesting situation. My first thought is to get your data physically located next to what it is being queried on. That means either a Btree Cluster, a Hash Cluster, or an Index Organized Table.

But you have got a lot of data (did you really mean 2000 million?) so you've almost certainly got to want to use partitioning, which if memory serves rules out both types of clusters but not the IOT's (need to double check that).

So, if you made the pk in the iot code table the combo of unitid and codeid, in that order, then all of the codes for a given unit would be stored in the same block, right there together.

I'd think then that access would be pretty direct: hit the root, hit a branch block or two, and then hit a single leaf block and be done. Might also be a good candidate for index key compression of the unitid.

Another thing to test would be whether it is better to normalize out your code data from your existing code table. If you did, you'd have the unit table, the unitcode table (containing only the unitid and codeid, IOT as before), and a code lookup table, which would have codeid and codedata.

This code lookup table could then probably be a hash cluster, because you probably have a relatively fixed and small total number of different codes (12?), and not need partitioning.

Advantage would be you wouldn't be repeating that 20 bytes of code data 30 million or 2000 million or whatever times, making your associative table (index, if IOT) much much smaller.

Disadvantage would be having to do an extra LIO. But, if code table was static, or nearly static, and really only 12 rows, then that would be like a one block table always in RAM.
Previous Topic: Locally Managed Table syntax
Next Topic: Decide the size of every component of SGA in given case?
Goto Forum:

Current Time: Thu Oct 01 02:15:55 CDT 2020