| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> bitmap join indexes
Hi all,
here's the background to my problem: I have 4 large tables (40 mill records) - each with an id (same id for all of them) and lots (100-300) of low-cardinality columns ('classification'-columns). The objective is to be able to do a fast count of the id's across the tables based on various criteria for the 'classification' columns. I also need to be able to add tables of the same format ideally without having to rebuild the whole thing.
To this end I've been experimenting with bitmap-indexes and bitmap join indexes. Quering one table with a bitmap index on each of the 'class' columns achieves the performance goal:
select count(*)
from cl_dumc1
where class1 in (1,2,3)
and class2 = 4
and class3=2
However when I need to select across 2 or more tables I do not get the required performance:
select count(*)
from cl_dumc1 c1,
cl_dumc2 c2
where c1.class1 in (1,2,3) and c1.class2 = 4 and c1.class3=2 and c2.class21=5 and c1.id = c2.id
As you know a bitmap index is a map (one long string of bits per distinct value of the column - one bit per row telling us if the row has the value or not) onto the rowids in the table. So what I really want is bitmap indexes that maps not to the the rowids but my ID column (since this is common across all the tables).
I figured I could do this by having a skinny table (cl_central) with only one column (ID), and then create bitmap join indexes that binds my 'real' tables together to one central id (being the rowid of my cl_central table).
Here's a sample script:
create sequence dum;
create table cl_central
(id number);
insert into cl_central
select dum.nextval,dum.nextval
from all_objects;
/
create table cl_dumc1(id number, class1 number, class2 number, class3 number, class4 number, class5 number);
insert into cl_dumc1
select id,mod(id,4), mod(id+1,4), mod(id+2,4), mod(id+3,4), mod(id+4,4) from cl_central;
create table cl_dumc2
(id number, class21 number, class22 number, class23 number, class24 number, class25 number);
insert into cl_dumc2
select id,mod(id,4),mod(id+1,4),mod(id+2,4),mod(id+3,4),mod(id+4,4) from cl_central;
create table cl_dumc3 (id number, class31 number, class32 number, class33 number, class34 number, class35 number);
insert into cl_dumc3
select id,mod(id,4),mod(id+1,4),mod(id+2,4),mod(id+3,4),mod(id+4,4)from cl_central;
create table cl_dumc4(id number, class41 number, class42 number, class43 number, class44 number, class45 number);
insert into cl_dumc4
select id,mod(id,4),mod(id+1,4),mod(id+2,4),mod(id+3,4),mod(id+4,4)from cl_central;
create unique index cl_central_pk on cl_central (id); alter table cl_central add constraint cl_central_pk primary key (id);
create unique index cl_dumc1_pk on cl_dumc1 (id); create unique index cl_dumc2_pk on cl_dumc2 (id); create unique index cl_dumc3_pk on cl_dumc3 (id); create unique index cl_dumc4_pk on cl_dumc4 (id); alter table cl_dumc1 add constraint cl_dumc1_pk primary key (id); alter table cl_dumc2 add constraint cl_dumc2_pk primary key (id);alter table cl_dumc3 add constraint cl_dumc3_pk primary key (id); alter table cl_dumc4 add constraint cl_dumc4_pk primary key (id);
declare
i number;
begin
for c in (select table_name,column_name from user_tab_columns where table_name in ('CL_DUMC1','CL_DUMC2','CL_DUMC3','CL_DUMC4') and column_name !='ID') loop
select count(*) into i from user_indexes where index_name=c.column_name;
if i = 0 then
EXECUTE IMMEDIATE 'CREATE BITMAP INDEX '||c.column_name||'
ON cl_central('||c.table_name||'.'||c.column_name||')
FROM cl_central, '||c.table_name||'
WHERE cl_central.id = '||c.table_name||'.id';
end if;
Test some queries:
select /*+INDEX(z class1)
*/
count(*)
from cl_central z,
cl_dumc1 c1
where z.person_urn = c1.person_urn
and c1.class1=0
Explain plan:
SELECT STATEMENT Cost= 15
SORT AGGREGATE
BITMAP CONVERSION COUNT
BITMAP INDEX SINGLE VALUE CLASS1
So far so good - very quick with just selecting on the bitmap.
select /*+INDEX(z class1)
INDEX(z class21)
INDEX(z class31)
INDEX(z class41)
INDEX(z class42)
*/
from cl_central z,
cl_dumc1 c1,
cl_dumc2 c2,
cl_dumc3 c3,
cl_dumc4 c4
where z.id= c1.id
and z.id = c2.id
and z.id = c3.id
and z.id = c4.id
and c1.class1=0
and c2.class21=0
SELECT STATEMENT Cost= 4
SORT AGGREGATE
BITMAP CONVERSION COUNT
BITMAP AND
BITMAP INDEX SINGLE VALUE CLASS1
BITMAP INDEX SINGLE VALUE CLASS21
BITMAP INDEX SINGLE VALUE CLASS31
BITMAP INDEX SINGLE VALUE CLASS41
Looks great - very fast accessing just small bitmap indexes.
select /*+INDEX(z class1)
INDEX(z class21)
INDEX(z class31)
INDEX(z class41)
INDEX(z class42)
*/
from cl_central z,
cl_dumc1 c1,
cl_dumc2 c2,
cl_dumc3 c3,
cl_dumc4 c4
where z.id = c1.id
and z.id = c2.id
and z.id = c3.id
and z.id = c4.id
and c1.class1=0
and c2.class21=0
and c3.class31=0
SELECT STATEMENT Cost= 16
SORT AGGREGATE
HASH JOIN
TABLE ACCESS FULL CL_DUMC4
TABLE ACCESS BY INDEX ROWID CL_CENTRAL
BITMAP CONVERSION TO ROWIDS
BITMAP AND
BITMAP INDEX SINGLE VALUE CLASS1
BITMAP INDEX SINGLE VALUE CLASS21
BITMAP INDEX SINGLE VALUE CLASS31
BITMAP INDEX SINGLE VALUE CLASS41
BITMAP INDEX SINGLE VALUE CLASS42
Disaster (well it is with my volumes...) - it visits the tables in addition to the bitmaps - AND I CANT SEE THE REASON FOR IT - all I did was to add a second condition to the last classification table.
Does anyone have any explanation for this?
This electronic message contains information from CACI International Inc or
subsidiary companies, which may be confidential, proprietary,
privileged or otherwise protected from disclosure. The information is
intended to be used solely by the recipient(s) named above. If you are not
an intended recipient, be aware that any review, disclosure, copying,
distribution or use of this transmission or its contents is prohibited. If
you have received this transmission in error, please notify us immediately
at postmaster_at_caci.co.uk
Viruses: Although we have taken steps to ensure that this e-mail and
attachments are free from any virus, we advise that in keeping with good
computing practice the recipient should ensure they are actually virus free.
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Oct 19 2005 - 04:07:20 CDT
 |
 |