hi list,
oracle 10.2.0.5 on windows
the following sql when executing choose the full table scan but not the bitmap
join index.
when hinted the bitmap join index(t_idx t_idx ) in the sql, the actual
consistent gets is lower .
Why the CBO choose the fulltable scan while the actul resource consume is lower
when using the index
Is there anything that I can do to make the CBO choose the index without hint.
select t_code.object_type from t, t_code where t.type_code = t_code.type_code
and t_code.object_type = 'TABLE';
create table t_code (type_code number, object_type varchar2(10));
insert into t_code values (1,'TABLE');
insert into t_code values (2,'INDEX');
insert into t_code values (3,'VIEW');
insert into t_code values (4,'SYNONYM');
insert into t_code values (5,'OTHER');
alter table t_code add constraint t_code_pk primary key (type_code) using index;
exec dbms_stats.gather_table_stats(user,'t_code',cascade=>true);
create table t_code (type_code number, object_type varchar2(10));
insert into t_code values (1,'TABLE');
insert into t_code values (2,'INDEX');
insert into t_code values (3,'VIEW');
insert into t_code values (4,'SYNONYM');
insert into t_code values (5,'OTHER');
alter table t_code add constraint t_code_pk primary key (type_code) using index;
exec dbms_stats.gather_table_stats(user,'t_code',cascade=>true);
create table t as select object_id, object_name, decode(object_type,
'TABLE',1,'INDEX',2,'VIEW',3,'SYNONYM',4,5) type_code from all_objects;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
CREATE bitmap index t_idx on t(t_code.object_type)
FROM t, t_code
WHERE t.type_code = t_code.type_code
/
exec dbms_stats.gather_table_stats(user,'t',cascade=>true);
set autotrace traceonly
the following sql does not use the bitmap index when executing
select t_code.object_type from t, t_code where t.type_code = t_code.type_code
and t_code.object_type = 'TABLE';
Execution Plan
----------------------------------------------------------
------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 83067 | 973K| 484 (2)|
| 1 | HASH JOIN | | 83067 | 973K| 484 (2)|
| 2 | TABLE ACCESS FULL| T_CODE | 1 | 9 | 3 (0)|
| 3 | TABLE ACCESS FULL| T | 415K| 1216K| 478 (2)|
------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3638 consistent gets
0 physical reads
0 redo size
386313 bytes sent via SQL*Net to client
16937 bytes received via SQL*Net from client
1497 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22432 rows processed
SQL> select /*+index(t t_idx)*/t_code.object_type from t, t_code where t.type_co
de = t_code.type_code and t_code.object_type = 'TABLE';
----------------------------------------------------------
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16613 | 194K| 1404 (1)|
| 1 | HASH JOIN | | 16613 | 194K| 1404 (1)|
| 2 | TABLE ACCESS FULL | T_CODE | 1 | 9 | 3 (0)|
| 3 | TABLE ACCESS BY INDEX ROWID | T | 83067 | 243K| 1400 (1)|
| 4 | BITMAP CONVERSION TO ROWIDS| | | | |
| 5 | BITMAP INDEX SINGLE VALUE | T_IDX | | | |
-----------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2050 consistent gets
4 physical reads
0 redo size
386313 bytes sent via SQL*Net to client
16937 bytes received via SQL*Net from client
1497 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22432 rows processed
