FAQ
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

Search Discussions

  • Mladen Gogala at Aug 14, 2006 at 5:01 pm

    On 08/14/2006 11:57:23 AM, Thomas Day wrote:
    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?
    Tom, I don't have any experience with that particular feature, but I do
    have an abundance of good will to test things. Could this be something like
    what you need:

    SQL> CREATE TABLE EMP_TEST

    ( EMPNO NUMBER(4,0),
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4,0),
    HIREDATE DATE,

    CONSTRAINT EMP_TEST_PK PRIMARY KEY(EMPNO)

    )
    ORGANIZATION INDEX

    MAPPING TABLE

    TABLESPACE USERS;

    2 3 4 5 6 7 8 9 10 11
    Table created.

    SQL> create bitmap index emp_test_jobs_i on emp_test(job)
    2 tablespace indx;

    Index created.

    SQL> select * from v$version;

    BANNER

    Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
    PL/SQL Release 10.2.0.2.0 - Production
    CORE 10.2.0.2.0 Production
    TNS for Linux: Version 10.2.0.2.0 - Production
    NLSRTL Version 10.2.0.2.0 - Production

    SQL>

    I will have to partition it by the "hiredate" column later, so that all
    of the 14 rows can be accessed in the most optimal fashion :)
  • Jonathan Lewis at Aug 14, 2006 at 5:13 pm
    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

    Original Message -----
    From: "Thomas Day"
    To:
    Sent: Monday, August 14, 2006 4:57 PM
    Subject: bitmapped secondary indexes on an IOT partitioned file.
    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
  • Mladen Gogala at Aug 14, 2006 at 5:23 pm

    On 08/14/2006 01:13:42 PM, Jonathan Lewis wrote:

    Running under 10.2.0.1 ....
    partition pt_2000 values less than (to_date('01-jan-2001','dd-mon-yyyy')),
    It looks like I will not have to partition it myself, after all. I admire your
    hands-on approach, Jonathan.
  • Thomas Day at Aug 14, 2006 at 5:25 pm
    Thanks Mladen and Jonathan. That's what I wanted to know. Isn't English a
    fun language!
  • Thomas Day at Aug 14, 2006 at 5:22 pm
    Fact tables are supposed to be nothing but foreign keys to dimension tables
    plus "facts" - numeric measurements.

    From the 10g Concepts manual, on the benefits of IOT -

    "Index-organized tables provide faster access to table rows by the primary
    key or any key that is a valid prefix of the primary key. Presence of nonkey
    columns of a row in the B-tree leaf block itself avoids an additional block
    access. Also, because rows are stored in primary key order, range access by
    the primary key (or a valid prefix) involves minimum block accesses.

    In order to allow even faster access to frequently accessed columns, you can
    use a row overflow segment (as described later) to push out infrequently
    accessed nonkey columns from the B-tree leaf block to an optional
    (heap-organized) overflow segment. This allows limiting the size and content
    of the portion of a row that is actually stored in the B-tree leaf block,
    which may lead to a higher number of rows in each leaf block and a smaller
    B-tree.

    Unlike a configuration of heap-organized table with a primary key index
    where primary key columns are stored both in the table and in the index,
    there is no such duplication here because primary key column values are
    stored only in the B-tree index.

    Because rows are stored in primary key order, a significant amount of
    additional storage space savings can be obtained through the use of key
    compression.

    Use of primary-key based logical rowids, as opposed to physical rowids, in
    secondary indexes on index-organized tables allows high availability. This
    is because, due to the logical nature of the rowids, secondary indexes do
    not become unusable even after a table reorganization operation that causes
    movement of the base table rows. At the same time, through the use of
    physical guess in the logical rowid, it is possible to get secondary index
    based index-organized table access performance that is comparable to
    performance for secondary index based access to an ordinary table."

    Since the fact tables will be the start point for the majority of queries I
    want the access to the result set to be as fast as possible. A current
    complaint is the length of time that it takes to get a result. I plan to
    move most of the measurements to the overflow area and make the IOT portion
    just the foreign keys to the dimension tables (the fact table's primary
    key). That's what led me to the idea of a fact table as an IOT. The fact
    table consists of it's primary key plus some measurements. Since I use an
    index to enforce that primary key I have a situation where most of the data
    that I want from the fact table can be satisfied from the index alone. I
    decided that I might as well save the space of the table (since the table
    and the index will basically duplicate each other's data) and make the table
    index organized. I'm partitioning it by date since most queries are limited
    to a single fiscal year.

    But I need a mechanism to speed queries that are not via the primary key or
    a valid prefix thereof. That's where the secondary indexes come in.

    I'm a little dissapointed that IOT tables cannot be composite partitioned.
    My dimension tables will be range partitioned by date (because of the date
    limited nature of the queries) and hash subpartitioned to spread the I/O
    over as many spindles as possilbe (each subpartition goes to its own
    tablespace and each tablespace will be on a separate spindle).

    However, that's just my thinking at the planning stage. If you have
    experience with the flaws of using IOT fact tables I certainly want to learn
    from that experience and not repeat a known mistake.

    What are the problems with IOT fact tables?
  • Thomas Day at Aug 14, 2006 at 6:09 pm
    Thank you. I agree, there's no such thing as a free lunch. IOTs do have
    their trade offs. I'm hoping to arrange the primary keys such that most
    queries will use them or a prefix of them. However, the only requirement
    that I have is "instant" response time.

    We will have more than 1 TB of data and fact tables with over 30 M rows. I
    expect that most queries will return a tiny percentage of the rows (but it
    may still be a large number).

    Right now I don't have the resources to run those very careful tests.
    However, I certainly will be monitoring the warehouse closely after it is in
    production. If it turns out that IOTs are a bad idea I should have ample
    opportunity to restructure the fact tables and I should also have a better
    idea how to subpartition to produce a fine-grained structure that matches
    the users' needs. Right now my heap subpartitioning on the dimension tables
    is just based on the idea of getting as many spindles involved as possilbe.

    If I get a RAID 0+1 storage and SAME it then the heap subpartitioning
    becomes a moot point and I can try to find a better way to get that
    fine-grained partitioning. Right now no one is asking for my advice on
    storage; nor are they likely to take it if I offer it unsolicited. The
    physical side of the data warehouse is under a different contract with a
    different contractor. Given that they'll want to do it as cheaply as
    possible, I expect to end up with RAID 5.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedAug 14, '06 at 3:57p
activeAug 14, '06 at 6:09p
posts7
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase