Regis,
Using any hint (other than the rule hint) causes Oracle to use the CBO. So
your index hint is causing the CBO to be used, even though you set your
optimizer mode to rule. Take out the hint and try it...
Gary
Gary Kirsh
Next Extent Consulting
-----Original Message-----
From: Regis Biassala [mailto:Regis.[Email Address Removed] Friday, May 31, 2002 11:54 AM
To: LazyDBA.com Discussion
Subject: Rich Niemiec from TUSC said...but I tried and...(MIKE can you
explain)
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