Huge IOT with BLOB column

From: Karsten Schmidt <groups_at_karsten-schmidt.com>
Date: 13 Jan 2004 23:55:17 -0800
Message-ID: <c6711ac4.0401132355.19b8a8d6@posting.google.com>

Hello,
I have got a large table,

create table sampletab (
  pdate timestamp(4),
  id1 number,
  id2 number,
  data_blob blob )
partition by range (pdate)
.....

the blob is stored in line.
average row length is about 500 bytes (including the blob), there is only one index (the primary key) on pdate,id1,id2. This is the only index there is ever going to be.

total size is going to be a couple of hundred gig, maybe even a terabyte.

I was wondering whether it would be worth while creating this as an partitioned,
key compressed IOT, with the blob in the overflow segment.

the obvious advantage would be storage savings, since i don't need to store the dupes on the leading edge of the index, and don't store the pkey values twice.

The table is going to be used in an OLTP style application, massive array - inserts, maybe tens or so concurrently. no deletes, no updates. only drop partition.

selects are always range scans (or unique scans) on the primary key. (often the inner loop of a nested loop join)

Does anyone have reasons, why this might not be a good idea ?

BTW, this is Oracle 9.2.0.4 on HP/UX 64 Bit, EMC Storage arrays.

Thanks for your help
Karsten Received on Wed Jan 14 2004 - 01:55:17 CST

Huge IOT with BLOB column - good idea ?