Re: Implementing a large partitioned IOT?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 8 Feb 2002 08:12:59 -0000
Message-ID: <1013155889.23772.0.nnrp-13.9e984b29@news.demon.co.uk>

Since your later note points out you are using 8.1.7, perhaps the most convincing argument against the composite partitioned IOT is the following:

SQL> l
  1 create table pt_composite_1 (
  2 id number primary key,
  3 grp number,
  4 small_vc varchar2(10),
  5 padding varchar2(100)
  6 )
  7 organization index
  8 partition by range(id)
  9 subpartition by hash (grp)
10 subpartitions 2
11 (

 12   partition p0 values less than (100),
 13   partition p1 values less than (200),
 14   partition p2 values less than (400),
 15   partition p3 values less than (800),
 16   partition p4 values less than (1600),
 17   partition p5 values less than (3200)

18 )
19*
SQL> /
(
*
ERROR at line 11:
ORA-25198: only range partitioning is supported for index-organized table

Clearly in their extensive test for proof of concept the design team didn't notice the error message.

Hash partitioning of IOTs is possible in 9.0.1, but even with 9.0.1 you can't composite partition.

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

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

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

Author of:
Practical Oracle 8i: Building Efficient Databases


Mark J. Bobak wrote in message ...


>

>So, others involved in the project said, gee, we

>can use the CUBE() function to generate all the

>possible combinations and store them in an IOT.

>Then we can do single row lookups no matter what

>the query is.  Sigh...  So, now there is a movement

>afoot to rush out and create a range partitioned,

>hash sub-partitioned, IOT that will likely end up

>in the neighborhood of 34 billion rows and at least

>2TB of data.

>

Received on Fri Feb 08 2002 - 02:12:59 CST