Re: Linking varrays in 2 separate tables

OK, I've gone over this again and had some sleepless nights over varrays. I have read Tom Kyte's one-on-one and regularly visit asktom.oracle.com. At the time, I came to the conclusion that in this case, the varray would be have more benefits than drawbacks. I am, however, ready to backtrack if I have to.

Let me explain my reasoning

The system loads blocks of values (50 per day) from various devices (around 10,000). There are also sets of factors (50 per day) published year upon year and loaded into the system. These factors vary depending on the class of the device. There are around 1000 classes so you get over 18 million factors a year. Any device's class can vary over time. Aggregation is performed more or less once per day where all the devices daily values are multiplied by the appropriate factors and summed in various device groupings.

I recognised right from the start varrays are not SQL friendly so the aggregation requires ugly SQL. However, the aggregation is done only once a day and values are never needed again. No other queries will be performed on the varray values or factors other than to view them as a block of 50 values on screen. Note that the aggregation process does create a statistics table set which IS parent-child. This table set is used for analysis and reporting.

I also considered that speed of aggregation might be an issue so I tested 2 schemas: One as typical parent-child and the other as a varray. The varray was significantly quicker. I admit, however, I did not make the child table an IOT. Would this have closed the performance gap?

The other issue was storage. I was aware of the 4,000 byte out of line issue but with only 50 numbers, this would never be breached. A varray would save quite a lot of space. Even if I used an IOT child table, would it be as storage efficient?

So, is this possibly a case where a varray on disk isn't so horrible? Do you think Tom Kyte might agree? I hope so or I have a lot of work to do... Received on Wed Mar 01 2006 - 06:04:41 CST