Re: Partition exchange, bitmap join indexes and ORA-14098

From: Phil <philip.moore_at_hp.com>
Date: 13 Jul 2005 23:00:55 -0700
Message-ID: <1121320855.357298.211190@o13g2000cwo.googlegroups.com>

Hi Jonathan,

I found this post interesting, so I tried to replicate in 10G R2 which I have installed on my Linux partition (RHEL4.0 WS)...

Unfortunately, the bug still appears to exist here's some spool output from my SQL session:

SQL> select * from v$version
  2 /

BANNER

---

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

SQL> create table facts (prod_id varchar2(15)   2 , fact_amt number)
  3 partition by range (prod_id)
  4 (partition p1 values less than ('M')   5 ,partition p2 values less than (MAXVALUE)   6 )
  7 /

Table created.

SQL> create table products (prod_id varchar2(15)   2 , prod_name varchar2(30)
  3 , constraint products_pk primary key (prod_id)   4 )
  5 /

Table created.

SQL> create bitmap index facts_prod_bjix   2 on facts(products.prod_name)
  3 from facts
  4 , products
  5 where facts.prod_id = products.prod_id   6 local
  7 /

Index created.

SQL> create table facts_new
  2 as
  3 select * from facts where 1 = 2
  4 /

Table created.

SQL> create bitmap index facts_new_prod_bjix   2 on facts_new(products.prod_name)
  3 from facts_new
  4 , products
  5 where facts_new.prod_id = products.prod_id   6 /

Index created.

SQL> alter table facts
  2 exchange partition p1 with table facts_new   3 including indexes with validation
  4 /

Table altered.

SQL> alter table products
  2 add price number
  3 /

Table altered.

SQL> create bitmap index facts_prod_bjix2   2 on facts(products.price)
  3 from facts
  4 , products
  5 where facts.prod_id = products.prod_id   6 local
  7 /

Index created.

SQL> create bitmap index facts_new_prod_bjix2   2 on facts_new(products.price)
  3 from facts_new
  4 , products
  5 where facts_new.prod_id = products.prod_id   6 /

Index created.

SQL> alter table facts
  2 exchange partition p1 with table facts_new   3 including indexes with validation
  4 /
exchange partition p1 with table facts_new

                                 *

ERROR at line 2:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION

SQL> spool off

#############################################

So guess we have to wait for a patch to all three versions (upgrading won't save us) :)

Sincerely,

Philip Moore Received on Thu Jul 14 2005 - 01:00:55 CDT