Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: bitmapped secondary indexes on an IOT partitioned file.

Re: bitmapped secondary indexes on an IOT partitioned file.

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 14 Aug 2006 18:13:42 +0100
Message-ID: <009601c6bfc4$fe3f89f0$0200a8c0@Primary>

Running under 10.2.0.1

drop table pt_iot;

create table pt_iot (
 customer_id number(12),
 ref_date date,
 expenditure number(6,2),
 constraint iot_pk primary key (customer_id, ref_date) )
organization index
mapping table
partition by range (ref_date)
(

 partition pt_2000 values less than (to_date('01-jan-2001','dd-mon-yyyy')),
 partition pt_2001 values less than (to_date('01-jan-2002','dd-mon-yyyy')),
 partition pt_2002 values less than (to_date('01-jan-2003','dd-mon-yyyy')),
 partition pt_2003 values less than (to_date('01-jan-2004','dd-mon-yyyy')),
 partition pt_2004 values less than (to_date('01-jan-2005','dd-mon-yyyy'))
)
;

SQL> select table_name,partition_name from user_tab_partitions;

TABLE_NAME PARTITION_NAME

-------------------- --------------------
SYS_IOT_MAP_63206    PT_2000
SYS_IOT_MAP_63206    PT_2001
SYS_IOT_MAP_63206    PT_2002
SYS_IOT_MAP_63206    PT_2003
SYS_IOT_MAP_63206    PT_2004
PT_IOT               PT_2000
PT_IOT               PT_2001
PT_IOT               PT_2002
PT_IOT               PT_2003
PT_IOT               PT_2004


(And, as you said, 9.2.0.6 with the same script gives: create table pt_iot (
*
ERROR at line 1:
ORA-25182: feature not currently available for index-organized tables

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

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

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

> I've looked at the documentation and I believe that I need Oracle 10g to use
> the mapping table with a partitioned IOT.
>
> I tried it with 9.2 and got "ORA-25182: feature not currently available for
> index-organized tables"
>
> I looked at the 10.2 documentation and it says "Oracle Database creates the
> mapping table or mapping table partition in the same tablespace as its
> parent index-organized table or partition." Now, I'm assuming that this
> means a partitioned IOT; however, a close reading could cause one to believe
> that it supports an unpartitioned IOT OR a heap partitioned table.
>
> I don't have 10.2 and won't for a while. Does anybody have experience with
> this in 10.2?
>
> I'm planning a data warehouse and I'd like to make my fact tables IOT. I'd
> further like to partition them by fiscal year, since most queries will be
> restricted to a single fiscal year. But I can't restrict the queries to
> just using the primary key (or a prefix of it) so I'd like to have bitmapped
> secondary indexes (low cardinality). For that I need to create a mapping
> table with the fact table. I can't do that in 9.2. I'd like to know that I
> will be able to in 10.2.
>
> Thanks
>


No virus found in this incoming message. Checked by AVG Free Edition.
Version: 7.1.405 / Virus Database: 268.10.9/417 - Release Date: 11/08/2006

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Aug 14 2006 - 12:13:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US