bitmap index is not used when the actual consistent gets is lower

From: Áõ Qinliu <Ivyliu_99_at_hotmail.com>
Date: Thu, 10 May 2018 06:04:40 +0000
Message-ID: <HK2PR01MB1313EC304D5DC75A971BB23286980_at_HK2PR01MB1313.apcprd01.prod.exchangelabs.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu May 10 2018 - 08:04:40 CEST