Re: CBO bug with global index ?

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Tue, 31 May 2005 08:40:48 +0200
Message-ID: <073401c565ab$aedfe950$3c02a8c0@JARAWIN>

Hi David,

 > CBO bug with global index ?

to combine two indices with OR condition they must by converted to bitmaps first.

Global bitmap index are not supported. I'm not really sure if the conversion of a global index to bitmap index is impossible, but I never observed it. I'd speculate that this is not possible and it is the reason of the full scan with global indexes.

Any on list can confirm or disprove this?

IMHO two global indexes can only be combined on AND condition (see hint AND_EQUAL) Anyway in your example I'd recommend to *use* local index (especially if you deploy exchange partition). Based on the way how the table is populated you may consider to use local bitmap index.

HTH Jaromir D.B. Nemec

http://www.db-nemec.com

• Original Message ----- From: "David Feng" <dbanotes_at_gmail.com> To: <oracle-l_at_freelists.org> Sent: Friday, May 27, 2005 11:41 AM Subject: CBO bug with global index ?

| 6 | BITMAP OR | | | | | | |
| 7 | BITMAP CONVERSION FROM ROWIDS | | | | | | |

|* 8 | INDEX RANGE SCAN | IND_TRADE_BASE_BACCOUNT | | | 43 | 1 | 21 |

| 9 | BITMAP CONVERSION FROM ROWIDS | | | | | | |

|* 10 | INDEX RANGE SCAN | IND_TRADE_BASE_SACCOUNT | | | 44 | 1 | 21 |


| 11 | VIEW | VW_NSO_1 | 8168 | 7992K| 11 | | |

| 12 | COLLECTION ITERATOR PICKLER FETCH| STR2VARLIST | | | | | |
| 13 | TABLE ACCESS FULL | DUAL | 8168 | | 11 | | |

---

SQL> select * from v$version;

BANNER

---

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production NLSRTL Version 9.2.0.4.0 - Production

 Thanks for any help!
!

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



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

Received on Tue May 31 2005 - 02:49:38 CDT