Re: bitmapped secondary indexes on an IOT partitioned file.

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Mon, 14 Aug 2006 13:01:07 -0400
Message-Id: <1155574867l.3954l.2l@medo.noip.com>

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 :)

-- 
Mladen Gogala
http://www.mgogala.com

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Aug 14 2006 - 12:01:07 CDT