Hi Mike and all of you.
I was reading the lastest release of Oracle Magazine may/june 2002, page 31
at title WHEN NOT TO USE A BITMAP INDEX.
Rich Niemiec said: "Bitmap indexes are not considered by the rule based
optimizer..."
Here's what I did from he's example:

SQL> create table emp1 as select * from scott.emp;
Table créée.

SQL> create table dept1 as select * from scott.dept;
Table créée.

SQL> alter table dept1
2 add constraint dept_constr1 unique(deptno);
Table modifiée.

SQL> create bitmap index empdept_idx
2 on emp1(dept1.deptno)
3 from emp1, dept1
4 where emp1.deptno = dept1.deptno;
Index créé.

SQL> analyze table emp1 compute statistics;
Table analysée.

SQL> analyze table dept1 compute statistics;
Table analysée.

SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
optimizer_mode string CHOOSE

SQL> set autotrace on explain
SQL> select /*+ index(emp1 empdept_idx) */ count(*) from emp1, dept1
2 where emp1.deptno = dept1.deptno;

COUNT(*)
----------
14

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=2)
1 0 SORT (AGGREGATE)
2 1 BITMAP CONVERSION (COUNT)
3 2 BITMAP INDEX (FULL SCAN) OF 'EMPDEPT_IDX'

Note: until here everything is alright, CBO is using my bitmap index
EMPDEPT_IDX

Now let delete the statistics and switch to RBO

SQL> analyze table emp1 delete statistics;
Table analysée.

SQL> analyze table dept1 delete statistics;
Table analysée.

SQL> alter session set optimizer_mode = rule;
Session modifiée.

SQL> select /*+ index(emp1 empdept_idx) */ count(*) from emp1, dept1
2 where emp1.deptno = dept1.deptno;

COUNT(*)
----------
14
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=34 Card=1 Bytes=13)
1 0 SORT (AGGREGATE)
2 1 BITMAP CONVERSION (COUNT)
3 2 BITMAP INDEX (FULL SCAN) OF 'EMPDEPT_IDX'

Note: RBO is still using my index...so I don't get it...I know the cost is
high but RBO is still using that Index.

Mike and the DBAs any idea why..!?

Thanks,
Regis
*********************************************************************
This electronic transmission is strictly confidential and intended solely
for the addressee. It may contain information which is covered by legal,
professional or other privilege. If you are not the intended addressee,
you must not disclose, copy or take any action in reliance of this
transmission. If you have received this transmission in error,
please notify the sender as soon as possible.
**********************************************************************

Oracle LazyDBA home page