Skip to Main Content
  • Questions

  • difference between bitmap join indexes and materialized view

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, AMIR.

Asked: August 20, 2007 - 2:30 am UTC

Last updated: July 18, 2011 - 2:55 pm UTC

Version: 10.0.1

Viewed 1000+ times

You Asked


Hi tom

in oracle we have materialized views and bitmap join indexes. I want to know why oracle have introduce bitmap join indexes we can do the same thing with materialzed views.

Regards
Amir riaz

and Tom said...

you cannot do the same thing via materialized views.

Oh, sure, you could denormalize the data in a materialized view, replicate all of it from both tables, and then bitmap index it

or, you could just bitmap index it.


A bitmap join index would allow you to index (for example) the DNAME column from DEPT - against the EMP table (emp has deptno, dept has deptno->dname - instead of query and join to find out everyone in the RESEARCH department - you can bitmap join index the data and just query the emp table)

to do the same in a materialized view would have you replicate the entire EMP table and the DNAME (at least) column - and then create a bitmap index on it.

A bitmap join index allows you to denormalize in the index, not in the tables.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Why bitmap join index is not used

A reader, July 13, 2011 - 8:27 am UTC

Hi Tom,

Would you please take a look why the bitmap join index is not used?

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

select t.object_name from t, t_code where t.type_code = t_code.type_code and t_code.object_type = 'TABLE';

2392 rows selected.

Elapsed: 00:00:00.09

Execution Plan
----------------------------------------------------------
Plan hash value: 2913955473

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 64509 | 2393K| 234 (2)| 00:00:05 |
|* 1 | HASH JOIN | | 64509 | 2393K| 234 (2)| 00:00:05 |
|* 2 | TABLE ACCESS FULL| T_CODE | 1 | 9 | 16 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T | 322K| 9134K| 216 (1)| 00:00:04 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T"."TYPE_CODE"="T_CODE"."TYPE_CODE")
2 - filter("T_CODE"."OBJECT_TYPE"='TABLE')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
462 consistent gets
0 physical reads
0 redo size
56922 bytes sent via SQL*Net to client
241 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2392 rows processed

Thanks.
Tom Kyte
July 15, 2011 - 8:34 am UTC

probably because using the index would be horribly inefficient. You are selecting object_name, an attribute which is not in the index. so we know we have to go to the table to get that column (table access by rowid).

And we think we'd have to do that 64,509 times.


If you use an index hint, you'd see the bitmap index plan is possible - but - considered quite expensive.

Why bitmap join index is not used

A reader, July 15, 2011 - 9:48 am UTC

But even only 32 rows, Oracle still didn't use the index.

select t.object_name from t, t_code where t.type_code = t_code.type_code and t_code.object_type = 'INDEX';

32 rows selected.

Elapsed: 00:00:00.09

Execution Plan
----------------------------------------------------------
Plan hash value: 2913955473

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 64509 | 2393K| 234 (2)| 00:00:05 |
|* 1 | HASH JOIN | | 64509 | 2393K| 234 (2)| 00:00:05 |
|* 2 | TABLE ACCESS FULL| T_CODE | 1 | 9 | 16 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T | 322K| 9134K| 216 (1)| 00:00:04 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T"."TYPE_CODE"="T_CODE"."TYPE_CODE")
2 - filter("T_CODE"."OBJECT_TYPE"='INDEX')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
462 consistent gets
0 physical reads
0 redo size
1049 bytes sent via SQL*Net to client
241 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
32 rows processed
Tom Kyte
July 18, 2011 - 9:41 am UTC

that is 322,000 rows, not 32 rows.

do you see the estimate, that is what drives the selection of a plan.


You might need a sql profile for this type of query - since the cardinality estimates are going to be pretty complicated.

what kind of database are you running that has only 4 indexes (4*8 times the data = 32)????


I have 32,000 indexes in my example table after running the scripts provided by you??


Why bitmap join index is not used

A reader, July 18, 2011 - 10:48 am UTC

It's from an user account which doesn't owner too many database objects but is granted access to objects from other schemas. There are tables in all_objects but not indexes associated with these tables. That's why there are only 32 indexes which belong to this schema.

If I don't use bitmap join index, Oracle will pick index.

create table tt as select object_id, object_name, object_type from all_objects;
insert into tt select * from tt;
insert into tt select * from tt;
insert into tt select * from tt;

create index tt_idx on tt(object_type);

exec dbms_stats.gather_table_stats(user,'tt',cascade=>true);

set autotrace traceonly

select object_name from tt where object_type = 'INDEX';

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 138971389

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23069 | 811K| 156 (0)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| TT | 23069 | 811K| 156 (0)| 00:00:03 |
|* 2 | INDEX RANGE SCAN | TT_IDX | 23172 | | 16 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_TYPE"='INDEX')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
18 consistent gets
0 physical reads
0 redo size
1257 bytes sent via SQL*Net to client
241 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
48 rows processed

Thanks.


Tom Kyte
July 18, 2011 - 11:29 am UTC

like I said, plans are based on estimated row counts.

do you see the estimate there?

and you are comparing a single table access to a two table join? Please compare apples to apples.


and, as stated above:

that is 322,000 rows, not 32 rows.

do you see the estimate, that is what drives the selection of a plan.


You might need a sql profile for this type of query - since the cardinality estimates are going to be pretty complicated.



We pick plans based on cardinality estimates. We a lot more than you got. You might need a sql profile for this one.

Why bitmap join index is not used

A reader, July 18, 2011 - 12:31 pm UTC

I will do sql profile.
Do you think if it's because Oracle didn't provide right statistics?

Would you please provide a similar case which Bitmap Join Index is used?

Thanks.
Tom Kyte
July 18, 2011 - 2:55 pm UTC

It is because the statistics are *hard to get*.

Look at the query, assuming you would not be running part of the query to optimizer the query (because the optimizer doesn't), how many rows would you think would come out of it.

You know what the type is - 'INDEX'.
You know that gets one row from the type code table.
You know that you join to T by type_code.

That is all you know, how do you predict how many rows you'll get from that?


ops$tkyte%ORA11GR2> create table t1 as select * from all_objects;

Table created.

ops$tkyte%ORA11GR2> create table t2 as select * from all_users;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter table t2 add constraint t2_pk primary key(username);

Table altered.

ops$tkyte%ORA11GR2> create bitmap index t1_t2_bidx
  2  on t1( t2.user_id )
  3  from t1, t2
  4  where t1.owner = t2.username;

Index created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T1' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T2' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace traceonly
ops$tkyte%ORA11GR2> select count(*)
  2  from t1, t2
  3  where t1.owner = t2.username
  4    and t2.user_id = 42;


Execution Plan
----------------------------------------------------------
Plan hash value: 758379198

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |     6 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |            |     1 |     6 |            |          |
|   2 |   BITMAP CONVERSION COUNT   |            |  2187 | 13122 |     1   (0)| 00:00:01 |
|*  3 |    BITMAP INDEX SINGLE VALUE| T1_T2_BIDX |       |       |            |          |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T1"."SYS_NC00016$"=42)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        423  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select t1.* from t1, t2 where t1.owner = t2.username and t2.user_id = 42;

308 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3169558978

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |  2187 |   207K|   231   (0)| 00:00:03 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T1         |  2187 |   207K|   231   (0)| 00:00:03 |
|   2 |   BITMAP CONVERSION TO ROWIDS|            |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE | T1_T2_BIDX |       |       |            |          |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T1"."SYS_NC00016$"=42)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         31  consistent gets
          0  physical reads
          0  redo size
      33874  bytes sent via SQL*Net to client
        640  bytes received via SQL*Net from client
         22  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        308  rows processed

ops$tkyte%ORA11GR2> set autotrace off

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.