玉面飞龙的BLOG

Oracle 文档中没有说的太明白，有些Features的使用是需要CBO的，有些Features的使用是自动Enable CBO的。记录一下。

The following features require use of the CBO:

• Reverse key indexes
• Function-based indexes
• Star transformations and star joins
• Query rewrite with materialized views
• Hash joins
• Bitmap indexes and bitmap join indexes
• Index skip scans

The following features enable use of the CBO even Optimizer_mode is RULE:

• Partitioned tables and indexes
• Index-organized tables
• SAMPLE clauses in a SELECT statement
• Parallel query and parallel DML

对一些Feature得简单测试，

Bitmap indexes and bitmap join indexes –require CBO

SQL> create table t tablespace cr_data as select * from dba_objects;
Table created.
SQL> create bitmap index tidx on t(owner);
Index created.
SQL> set autotrace on
SQL> select count(*) from t where owner=’SYSTEM’;
Execution Plan
———————————————————-
0      SELECT STATEMENT Optimizer=RULE
1    0   SORT (AGGREGATE)
2    1     TABLE ACCESS (FULL) OF ‘T’
SQL> analyze table t compute statistics;
Table analyzed.
SQL> alter session set optimizer_mode=choose;
Session altered.
SQL> select count(*) from t where owner=’SYSTEM’;
Execution Plan
———————————————————-
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=6)
1    0   SORT (AGGREGATE)
2    1     BITMAP CONVERSION (COUNT)
3    2       BITMAP INDEX (FAST FULL SCAN) OF ‘TIDX’

 Hash joins    –require CBO

SQL> set autotrace on
SQL> select ename,dname from emp,dept where emp.deptno=dept.deptno;
Execution Plan
———————————————————-
0      SELECT STATEMENT Optimizer=RULE
1    0   MERGE JOIN
2    1     SORT (JOIN)
3    2       TABLE ACCESS (FULL) OF ‘DEPT’
4    1     SORT (JOIN)
5    4       TABLE ACCESS (FULL) OF ‘EMP’

SQL> alter session set optimizer_mode=all_rows;
Session altered.
SQL>  select ename,dname from emp,dept where emp.deptno=dept.deptno;
Execution Plan
———————————————————-
0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=82 Bytes=3444)
1    0   HASH JOIN (Cost=5 Card=82 Bytes=3444)
2    1     TABLE ACCESS (FULL) OF ‘EMP’ (Cost=2 Card=82 Bytes=1640)
3    1     TABLE ACCESS (FULL) OF ‘DEPT’ (Cost=2 Card=82 Bytes=1804)

 SAMPLE clauses in a SELECT statement –enable CBO

SQL> set autotrace on
SQL> select count(*) from t sample (5);
Execution Plan
———————————————————-
0      SELECT STATEMENT Optimizer=RULE (Cost=11 Card=1)
1    0   SORT (AGGREGATE)
2    1     TABLE ACCESS (SAMPLE) OF ‘T’ (Cost=11 Card=396)

 Function-based indexes          –require CBO，and reference here.

SQL> create index tidx2 on t(lower(object_name));
Index created.
SQL> analyze table t compute statistics;
Table analyzed.
SQL> set autotrace on
SQL> select owner from t where lower(object_name)=’t';
Execution Plan
———————————————————-
0      SELECT STATEMENT Optimizer=RULE
1    0   TABLE ACCESS (FULL) OF ‘T’
SQL> alter session set optimizer_mode=choose;
Session altered.
SQL> select owner from t where lower(object_name)=’t';
Execution Plan
———————————————————-
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=23)
1    0   TABLE ACCESS (BY INDEX ROWID) OF ‘T’ (Cost=2 Card=1 Bytes=23)
2    1     INDEX (RANGE SCAN) OF ‘TIDX2′ (NON-UNIQUE) (Cost=1 Card=1)

Reverse key indexes             –require CBO

SQL> create index tidx3 on t(object_id) reverse;
Index created.
SQL> set autotrace on
SQL> analyze table t compute statistics;
Table analyzed.
SQL> select * from t where object_id=234234;
no rows selected
Execution Plan
———————————————————-
0      SELECT STATEMENT Optimizer=RULE
1    0   TABLE ACCESS (FULL) OF ‘T’

SQL> alter session set optimizer_mode=choose;
Session altered.
SQL> select * from t where object_id=234234;
Execution Plan
———————————————————-
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=80)
1    0   TABLE ACCESS (BY INDEX ROWID) OF ‘T’ (Cost=2 Card=1 Bytes=80)
2    1     INDEX (RANGE SCAN) OF ‘TIDX3′ (NON-UNIQUE) (Cost=1 Card=1)