Re: Huge IOT with BLOB column - good idea ?

The range partitioned IOT sounds good.

If your range scans are on the first one or two columns of the PK to get the rest of the PK but not the BLOB, then using an OVERFLOW for the BLOB sounds like a good idea.

If your range scans are typically to collect a number of BLOBs, I don't think I'd use the overflow - it sounds as if in-line BLOBS in the IOT_TOP would be better.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


Next public appearance2:
 March 2004 Hotsos Symposium - Keynote
 March 2004 Charlotte NC - OUG Tutorial
 April 2004 Iceland


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Karsten Schmidt" <groups_at_karsten-schmidt.com> wrote in message
news:c6711ac4.0401132355.19b8a8d6_at_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