Regiss,
The Cost Based Optimizer will be used by default if you force the
Optimizer to use a Bitmap Index.
Certain Functionalities like Bitmap, Function Based Indexes are only
possible only in the cbo and even if the optimizer mode is rule it will
still use the Cost based Optimizer.
HTH
Best Regards,
Ganesh R
Tel : +971 (4) 397 3337 Ext 420
Fax : +971 (4) 397 6262
HP : +971 (50) 745 6019
============================================
Live to learn... forget... and learn again.
============================================
-----Original Message-----
From: Regis Biassala [mailto:Regis.[Email Address Removed]
Sent: Friday, May 31, 2002 7:54 PM
To: LazyDBA.com Discussion
Subject: Rich Niemiec from TUSC said...but I tried and...(MIKE can you
exp lain)
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 documentation is here:
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
To unsubscribe: send a blank email to oracledba-[Email Address Removed] subscribe: send a blank email to oracledba-[Email Address Removed] the list archive: http://www.LAZYDBA.com/odbareadmail.pl
Tell yer mates about http://www.farAwayJobs.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page