Home » RDBMS Server » Performance Tuning » bitmap join indexes
| bitmap join indexes [message #143198] |
Wed, 19 October 2005 07:53  |
osoerland
Messages: 5
Registered: October 2005
|
Junior Member |
|
|
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) nologging;
--Load some data....
insert into cl_central select dum.nextval from dual;
insert /*+APPEND */ into cl_central select dum.nextval from cl_central,cl_central;
/
/
/
insert /*+APPEND */ into cl_central select dum.nextval from cl_central,cl_central where rownum < 1000000;
create table cl_dumc1(id number, class1 number, class2 number, class3 number, class4 number, class5 number) nologging;
insert /*+APPEND*/ 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) nologging;
insert /*+APPEND*/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) nologging;
insert /*+APPEND*/ 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) nologging;
insert /*+APPEND*/ 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;
alter table cl_central add constraint cl_central_pk primary key (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;
end loop;
end;
/
-- for all tables:
begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname =>'TEST',
tabname =>'&1',
estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade =>TRUE
);
end;
--Test some queries:
select
count(*)
from cl_central z,
cl_dumc1 c1
where z.id = c1.id
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
count(*)
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
and c4.class41=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)
*/
count(*)
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
and c4.class41=0
and c4.class42 =1
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 - it should not need to go into CL_DUMC4 at all!
Does anyone have any explanation for this?
|
|
|
|
| Re: bitmap join indexes [message #143221 is a reply to message #143198] |
Wed, 19 October 2005 09:12  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Am I reading your script right? Are all 4 of your tables exactly the same? Including the same data?
And where are your histograms?
Can you explain your first paragraph again about your situation and your goal?
And is each id likely to be in each of the 4 tables, or is existence in a table an eliminating criteria in the join?
|
|
|
|
| Re: bitmap join indexes [message #143239 is a reply to message #143221] |
Wed, 19 October 2005 10:13 |
osoerland
Messages: 5
Registered: October 2005
|
Junior Member |
|
|
The 4 tables in the test script are just an example of what I'm trying to do, so there are just a few column in them and the data is generated. My real tables have something like 50-200 'classification' columns in them with 4-100 different values in each column.
The ID column is primary on all the tables and there's a mandatory one-to-one relationship between them (at least to start with - ideally this method should work even with missing records in one of the tables but if it does not I can infill).
The goal of all this is to be able to quickly count number of ID's across all the 4 tables given a set of criteria on the 'classification' columns.
My first attempt to solve this was to create one big table with all the columns in it (~500 columns) and create bitmap indexes. This works well, but it means that if I want to add a table (which we do) I need to rebuild the whole thing (and this takes a long time!).
The idea behind using bitmap join indexes was to be able to do this count by just using the bitmap indexes and not visiting the wide tables. My second SELECT in the example achieves this and gets the result in .6 seconds. Adding an extra criteria changes it all and the querytime is 16 seconds (incidentally the count is the same).
I've tried with histograms (10 buckets for all columns) but it does not make any difference.
Regards
Ottar.
|
|
|
|
| Re: bitmap join indexes [message #143255 is a reply to message #143198] |
Wed, 19 October 2005 11:36 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
This may not help any, but the last query, with the most criteria, and with the joins, avoids the FTS, which may or may not be what you are after. Also the obvious first queries do.
But as the script shows, it depends on the sorts of queries you run as to whether the FTS. Bitmaps shine best with multiple columns being used as criteria in the same table, which the only queries below that do a FTS do not use multiple criteria columns per table, just one.
I have not worked with bitmap join indexes, but the warehouse guide seems to imply they are designed for things like fact and dimension tables, where there is a central table or hub. That isn't the situation here, although I don't know if that means they can't be used to good effect. Maybe I'll try.
And why use 10 buckets if there are up to 100 different values? Why not more buckets, at least 100, 200 to be safe.
Also if there is a one to one relationship, then I'd think one option could be to query the tables individually and union the results together to eliminate duplicates. But I'm not sure of the value of breaking the tables out only to have to join them back together again all the time, if that is what happens.
Anyway, for what it is worth:
MYDBA@ORCL >
MYDBA@ORCL > create table test1(id number, a number, b number, c number);
Table created.
MYDBA@ORCL > create table test2 as select * from test1;
Table created.
MYDBA@ORCL > create table test3 as select * from test1;
Table created.
MYDBA@ORCL >
MYDBA@ORCL > insert /*+ append */ into test1
2 select rownum, mod(rownum,3), mod(rownum,6), mod(rownum,9)
3 from dual connect by level <= &&size;
Enter value for size: 1000000
old 3: from dual connect by level <= &&size
new 3: from dual connect by level <= 1000000
1000000 rows created.
MYDBA@ORCL >
MYDBA@ORCL > insert /*+ append */ into test2
2 select rownum, mod(rownum,4), mod(rownum,7), mod(rownum,10)
3 from dual connect by level <= &&size;
old 3: from dual connect by level <= &&size
new 3: from dual connect by level <= 1000000
1000000 rows created.
MYDBA@ORCL >
MYDBA@ORCL > insert /*+ append */ into test3
2 select rownum, mod(rownum,5), mod(rownum,8), mod(rownum,11)
3 from dual connect by level <= &&size;
old 3: from dual connect by level <= &&size
new 3: from dual connect by level <= 1000000
1000000 rows created.
MYDBA@ORCL >
MYDBA@ORCL > create bitmap index test1a on test1(a);
Index created.
MYDBA@ORCL > create bitmap index test1b on test1(b);
Index created.
MYDBA@ORCL > create bitmap index test1c on test1(c);
Index created.
MYDBA@ORCL >
MYDBA@ORCL > create bitmap index test2a on test2(a);
Index created.
MYDBA@ORCL > create bitmap index test2b on test2(b);
Index created.
MYDBA@ORCL > create bitmap index test2c on test2(c);
Index created.
MYDBA@ORCL >
MYDBA@ORCL > create bitmap index test3a on test3(a);
Index created.
MYDBA@ORCL > create bitmap index test3b on test3(b);
Index created.
MYDBA@ORCL > create bitmap index test3c on test3(c);
Index created.
MYDBA@ORCL >
MYDBA@ORCL > alter table test1 add constraint test1_pk primary key(id);
Table altered.
MYDBA@ORCL > alter table test2 add constraint test2_pk primary key(id);
Table altered.
MYDBA@ORCL > alter table test3 add constraint test3_pk primary key(id);
Table altered.
MYDBA@ORCL >
MYDBA@ORCL > --create index test1id on test1(id);
MYDBA@ORCL > --create index test2id on test2(id);
MYDBA@ORCL > --create index test3id on test3(id);
MYDBA@ORCL >
MYDBA@ORCL > --create bitmap index test1id on test1(id);
MYDBA@ORCL > --create bitmap index test2id on test2(id);
MYDBA@ORCL > --create bitmap index test3id on test3(id);
MYDBA@ORCL >
MYDBA@ORCL > begin
2 dbms_stats.gather_table_stats(user,'test1',cascade=>true,
3 method_opt=>'for all columns size 250',estimate_percent=>30);
4
5 dbms_stats.gather_table_stats(user,'test2',cascade=>true,
6 method_opt=>'for all columns size 250',estimate_percent=>30);
7
8 dbms_stats.gather_table_stats(user,'test3',cascade=>true,
9 method_opt=>'for all columns size 250',estimate_percent=>30);
10 end;
11 /
PL/SQL procedure successfully completed.
MYDBA@ORCL >
MYDBA@ORCL > set autotrace traceonly explain;
MYDBA@ORCL >
MYDBA@ORCL > select count(*) from test1
2 where a = 0 and b = 0 and c = 0;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=60 Card=1 Bytes=9)
1 0 SORT (AGGREGATE)
2 1 BITMAP CONVERSION (COUNT) (Cost=60 Card=6170 Bytes=55530)
3 2 BITMAP AND
4 3 BITMAP INDEX (SINGLE VALUE) OF 'TEST1C' (INDEX (BITMAP))
5 3 BITMAP INDEX (SINGLE VALUE) OF 'TEST1B' (INDEX (BITMAP))
6 3 BITMAP INDEX (SINGLE VALUE) OF 'TEST1A' (INDEX (BITMAP))
MYDBA@ORCL >
MYDBA@ORCL > select count(*) from test2
2 where a = 0 and b = 0 and c = 0;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=63 Card=1 Bytes=9)
1 0 SORT (AGGREGATE)
2 1 BITMAP CONVERSION (COUNT) (Cost=63 Card=3551 Bytes=31959)
3 2 BITMAP AND
4 3 BITMAP INDEX (SINGLE VALUE) OF 'TEST2C' (INDEX (BITMAP))
5 3 BITMAP INDEX (SINGLE VALUE) OF 'TEST2B' (INDEX (BITMAP))
6 3 BITMAP INDEX (SINGLE VALUE) OF 'TEST2A' (INDEX (BITMAP))
MYDBA@ORCL >
MYDBA@ORCL > select count(*) from test3
2 where a = 0 and b = 0 and c = 0;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=60 Card=1 Bytes=9)
1 0 SORT (AGGREGATE)
2 1 BITMAP CONVERSION (COUNT) (Cost=60 Card=2267 Bytes=20403)
3 2 BITMAP AND
4 3 BITMAP INDEX (SINGLE VALUE) OF 'TEST3C' (INDEX (BITMAP))
5 3 BITMAP INDEX (SINGLE VALUE) OF 'TEST3B' (INDEX (BITMAP))
6 3 BITMAP INDEX (SINGLE VALUE) OF 'TEST3A' (INDEX (BITMAP))
MYDBA@ORCL >
MYDBA@ORCL > select count(*) from test1, test2, test3
2 where test1.id = test2.id and test2.id = test3.id
3 and test1.a = 0 and test1.b = 0 and test1.c = 0;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=578 Card=1 Bytes=23)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=578 Card=6170 Bytes=141910)
3 2 NESTED LOOPS (Cost=573 Card=6170 Bytes=111060)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'TEST1' (TABLE) (Cost=567 Card=6170
Bytes=80210)
5 4 BITMAP CONVERSION (TO ROWIDS)
6 5 BITMAP AND
7 6 BITMAP INDEX (SINGLE VALUE) OF 'TEST1C' (INDEX (BITMAP))
8 6 BITMAP INDEX (SINGLE VALUE) OF 'TEST1B' (INDEX (BITMAP))
9 6 BITMAP INDEX (SINGLE VALUE) OF 'TEST1A' (INDEX (BITMAP))
10 3 INDEX (UNIQUE SCAN) OF 'TEST2_PK' (INDEX (UNIQUE)) (Cost=0 Card=1 By
tes=5)
11 2 INDEX (UNIQUE SCAN) OF 'TEST3_PK' (INDEX (UNIQUE)) (Cost=0 Card=1 Byte
s=5)
MYDBA@ORCL >
MYDBA@ORCL > select count(*) from test1, test2, test3
2 where test1.id = test2.id and test2.id = test3.id
3 and test2.a = 0 and test2.b = 0 and test2.c = 0;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=478 Card=1 Bytes=23)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=478 Card=3551 Bytes=81673)
3 2 NESTED LOOPS (Cost=475 Card=3551 Bytes=63918)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'TEST2' (TABLE) (Cost=472 Card=3551
Bytes=46163)
5 4 BITMAP CONVERSION (TO ROWIDS)
6 5 BITMAP AND
7 6 BITMAP INDEX (SINGLE VALUE) OF 'TEST2C' (INDEX (BITMAP))
8 6 BITMAP INDEX (SINGLE VALUE) OF 'TEST2B' (INDEX (BITMAP))
9 6 BITMAP INDEX (SINGLE VALUE) OF 'TEST2A' (INDEX (BITMAP))
10 3 INDEX (UNIQUE SCAN) OF 'TEST1_PK' (INDEX (UNIQUE)) (Cost=0 Card=1 By
tes=5)
11 2 INDEX (UNIQUE SCAN) OF 'TEST3_PK' (INDEX (UNIQUE)) (Cost=0 Card=1 Byte
s=5)
MYDBA@ORCL >
MYDBA@ORCL > select count(*) from test1, test2, test3
2 where test1.id = test2.id and test2.id = test3.id
3 and test3.a = 0 and test3.b = 0 and test3.c = 0;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=381 Card=1 Bytes=23)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=381 Card=2266 Bytes=52118)
3 2 NESTED LOOPS (Cost=379 Card=2267 Bytes=40806)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'TEST3' (TABLE) (Cost=377 Card=2267
Bytes=29471)
5 4 BITMAP CONVERSION (TO ROWIDS)
6 5 BITMAP AND
7 6 BITMAP INDEX (SINGLE VALUE) OF 'TEST3C' (INDEX (BITMAP))
8 6 BITMAP INDEX (SINGLE VALUE) OF 'TEST3B' (INDEX (BITMAP))
9 6 BITMAP INDEX (SINGLE VALUE) OF 'TEST3A' (INDEX (BITMAP))
10 3 INDEX (UNIQUE SCAN) OF 'TEST2_PK' (INDEX (UNIQUE)) (Cost=0 Card=1 By
tes=5)
11 2 INDEX (UNIQUE SCAN) OF 'TEST1_PK' (INDEX (UNIQUE)) (Cost=0 Card=1 Byte
s=5)
MYDBA@ORCL >
MYDBA@ORCL > select count(*) from test1, test2, test3
2 where test1.id = test2.id and test2.id = test3.id
3 and test1.a = 0 and test2.a = 0 and test3.a = 0;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2809 Card=1 Bytes=24)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=2809 Card=201140 Bytes=4827360)
3 2 HASH JOIN (Cost=1626 Card=201140 Bytes=3218240)
4 3 TABLE ACCESS (FULL) OF 'TEST3' (TABLE) (Cost=597 Card=201140 Bytes=1
609120)
5 3 TABLE ACCESS (FULL) OF 'TEST2' (TABLE) (Cost=594 Card=249300 Bytes=1
994400)
6 2 TABLE ACCESS (FULL) OF 'TEST1' (TABLE) (Cost=591 Card=332630 Bytes=266
1040)
MYDBA@ORCL >
MYDBA@ORCL > select count(*) from test1, test2, test3
2 where test1.id = test2.id and test2.id = test3.id
3 and test1.b = 0 and test2.b = 0 and test3.b = 0;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2375 Card=1 Bytes=24)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=2375 Card=124297 Bytes=2983128)
3 2 TABLE ACCESS (FULL) OF 'TEST1' (TABLE) (Cost=593 Card=167160 Bytes=133
7280)
4 2 HASH JOIN (Cost=1453 Card=124297 Bytes=1988752)
5 4 TABLE ACCESS (FULL) OF 'TEST3' (TABLE) (Cost=599 Card=124297 Bytes=9
94376)
6 4 TABLE ACCESS (FULL) OF 'TEST2' (TABLE) (Cost=596 Card=142580 Bytes=1
140640)
MYDBA@ORCL >
MYDBA@ORCL > select count(*) from test1, test2, test3
2 where test1.id = test2.id and test2.id = test3.id
3 and test1.c = 0 and test2.c = 0 and test3.c = 0;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2210 Card=1 Bytes=24)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=2210 Card=91019 Bytes=2184456)
3 2 TABLE ACCESS (FULL) OF 'TEST1' (TABLE) (Cost=595 Card=111213 Bytes=889
704)
4 2 HASH JOIN (Cost=1384 Card=91019 Bytes=1456304)
5 4 TABLE ACCESS (FULL) OF 'TEST3' (TABLE) (Cost=601 Card=91020 Bytes=72
8160)
6 4 TABLE ACCESS (FULL) OF 'TEST2' (TABLE) (Cost=598 Card=100223 Bytes=8
01784)
MYDBA@ORCL >
MYDBA@ORCL > select count(*) from test1, test2, test3
2 where test1.id = test2.id and test2.id = test3.id
3 and test1.a = 0 and test2.a = 0 and test3.a = 0
4 and test1.b = 0 and test2.b = 0 and test3.b = 0
5 and test1.c = 0 and test2.c = 0 and test3.c = 0;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1418 Card=1 Bytes=39)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=1418 Card=2267 Bytes=88413)
3 2 HASH JOIN (Cost=850 Card=2267 Bytes=58942)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'TEST3' (TABLE) (Cost=377 Card=2267
Bytes=29471)
5 4 BITMAP CONVERSION (TO ROWIDS)
6 5 BITMAP AND
7 6 BITMAP INDEX (SINGLE VALUE) OF 'TEST3C' (INDEX (BITMAP))
8 6 BITMAP INDEX (SINGLE VALUE) OF 'TEST3B' (INDEX (BITMAP))
9 6 BITMAP INDEX (SINGLE VALUE) OF 'TEST3A' (INDEX (BITMAP))
10 3 TABLE ACCESS (BY INDEX ROWID) OF 'TEST2' (TABLE) (Cost=472 Card=3551
Bytes=46163)
11 10 BITMAP CONVERSION (TO ROWIDS)
12 11 BITMAP AND
13 12 BITMAP INDEX (SINGLE VALUE) OF 'TEST2C' (INDEX (BITMAP))
14 12 BITMAP INDEX (SINGLE VALUE) OF 'TEST2B' (INDEX (BITMAP))
15 12 BITMAP INDEX (SINGLE VALUE) OF 'TEST2A' (INDEX (BITMAP))
16 2 TABLE ACCESS (BY INDEX ROWID) OF 'TEST1' (TABLE) (Cost=567 Card=6170 B
ytes=80210)
17 16 BITMAP CONVERSION (TO ROWIDS)
18 17 BITMAP AND
19 18 BITMAP INDEX (SINGLE VALUE) OF 'TEST1C' (INDEX (BITMAP))
20 18 BITMAP INDEX (SINGLE VALUE) OF 'TEST1B' (INDEX (BITMAP))
21 18 BITMAP INDEX (SINGLE VALUE) OF 'TEST1A' (INDEX (BITMAP))
MYDBA@ORCL >
MYDBA@ORCL > set autotrace off;
MYDBA@ORCL >
MYDBA@ORCL > undefine size
MYDBA@ORCL >
MYDBA@ORCL > drop table test1;
Table dropped.
MYDBA@ORCL > drop table test2;
Table dropped.
MYDBA@ORCL > drop table test3;
Table dropped.
MYDBA@ORCL >
MYDBA@ORCL > set echo off;
|
|
|
|
| Re: bitmap join indexes [message #143292 is a reply to message #143198] |
Wed, 19 October 2005 16:17 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
One thing I forgot to include in the above example is some extra padding for data. Without it, the FTS looks better than it would be in real life.
Below is the query plan for one of the three queries that in the prior example did a FTS. This is after adding a column to the table that is a char(100). The FTS now looks so bad that oracle does a FFS on the pk index of one table, then hash joins those rowids to the rowids in one of the bitmap indexes from the same table. It likewise does the same for the other two tables, then hash joins all of those results together.
MYDBA@ORCL >
MYDBA@ORCL > select count(*) from test1, test2, test3
2 where test1.id = test2.id and test2.id = test3.id
3 and test1.c = 0 and test2.c = 0 and test3.c = 0;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=806 Card=1 Bytes=24)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=806 Card=9196 Bytes=220704)
3 2 VIEW OF 'index$_join$_001' (VIEW) (Cost=268 Card=10770 Bytes=86160)
4 3 HASH JOIN
5 4 BITMAP CONVERSION (TO ROWIDS) (Cost=3 Card=10770 Bytes=86160)
6 5 BITMAP INDEX (SINGLE VALUE) OF 'TEST1C' (INDEX (BITMAP))
7 4 INDEX (FAST FULL SCAN) OF 'TEST1_PK' (INDEX (UNIQUE)) (Cost=264 Ca
rd=10770 Bytes=86160)
8 2 HASH JOIN (Cost=537 Card=9196 Bytes=147136)
9 8 VIEW OF 'index$_join$_003' (VIEW) (Cost=268 Card=9197 Bytes=73576)
10 9 HASH JOIN
11 10 BITMAP CONVERSION (TO ROWIDS) (Cost=3 Card=9197 Bytes=73576)
12 11 BITMAP INDEX (SINGLE VALUE) OF 'TEST3C' (INDEX (BITMAP))
13 10 INDEX (FAST FULL SCAN) OF 'TEST3_PK' (INDEX (UNIQUE)) (Cost=264
Card=9197 Bytes=73576)
14 8 VIEW OF 'index$_join$_002' (VIEW) (Cost=268 Card=9833 Bytes=78664)
15 14 HASH JOIN
16 15 BITMAP CONVERSION (TO ROWIDS) (Cost=3 Card=9833 Bytes=78664)
17 16 BITMAP INDEX (SINGLE VALUE) OF 'TEST2C' (INDEX (BITMAP))
18 15 INDEX (FAST FULL SCAN) OF 'TEST2_PK' (INDEX (UNIQUE)) (Cost=264
Card=9833 Bytes=78664)
|
|
|
|
| Re: bitmap join indexes [message #143489 is a reply to message #143255] |
Thu, 20 October 2005 10:42 |
osoerland
Messages: 5
Registered: October 2005
|
Junior Member |
|
|
You're right - bitmap join indexes are designed for a dimension and fact scenarios allowing the bitmap index to be built onto the rowid's of the fact table but the value actually being indexed is from the the dimension table.
So it's a bit like pre-joining the tables.
In my setup this is what I am trying to emulate - with cl_central being the fact table and the cl_class* tables the dimension tables.
I've tried to explain in the attached document.
Re histograms: in my test I only have 4 distinct values.
|
|
|
|
| Re: bitmap join indexes [message #143684 is a reply to message #143198] |
Fri, 21 October 2005 08:19 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
|
Just a follow up on this, I did do some, but not much, playing around with bitmap join indexes. In your case I'd think they would go against your concern of rebuilding things, as they would be linking tables together through indexes making them more dependent. But, in one test I did get the count in my final query of the test above to go lightning fast by doing a single count off of a single bitmap join index. But still I found like other bitmaps they do best when multiple criteria used.
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 18:00:01 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
