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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 10 May 2018 12:51:58 +0000
Message-ID: <LO1P123MB0977C190BA330A816EFBC18DA5980_at_LO1P123MB0977.GBRP123.PROD.OUTLOOK.COM>

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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Áõ Qinliu <Ivyliu_99_at_hotmail.com> Sent: 10 May 2018 07:04
To: oracle-l_at_freelists.org
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

†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Thu May 10 2018 - 14:51:58 CEST