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
-- //www.freelists.org/webpage/oracle-l