The standard reason is that the optimizer does not understand your data as well
as you do - often because of a problem with statistics.
In this example the optimizer only has the information that there are 5
distinct values for type_code in table t which implies it will access 1/5th of
the rows and it assumes those rows will be scattered evenly throughout the
table. Note that the tablescan shows 415K rows in the table but the join
reduces this to 83K - one fifth.
You probably have about thousand or so rows of type_code 1, and a large
fraction of them are probably fairly well clustered (because all the tables in
a schema are often created at the same time): if you created a histogram on
type_code and a b-tree index on type code the histogram would tell Oracle that
you had a relatively small number of type_code =1 , and the index would tell
you they were fairly well clustered and Oracle would use the index. (bitmap
indexes don't hold clustering information in the clustering_factor, by the way).
The secondary problem comes from the join - while you know that type_code = 1
is 'TABLE' the optimizer doesn't, so it (sort of) assumes that 'TABLE'
corresponds to "the average type_code" - so even with a histogram on type_code
the optimizer would still work out the wrong number.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of 刘 Qinliu <Ivyliu_99@xxxxxxxxxxx>
Sent: 10 May 2018 07:04
To: oracle-l@xxxxxxxxxxxxx
Subject: bitmap index is not used when the actual consistent gets is lower
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
