From oracle-l-bounce@freelists.org Wed Oct 19 04:07:20 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j9J97Jgv012524 for ; Wed, 19 Oct 2005 04:07:19 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j9J96kvX012368 for ; Wed, 19 Oct 2005 04:06:47 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 82A3B208A6D; Wed, 19 Oct 2005 04:06:34 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 04173-06; Wed, 19 Oct 2005 04:06:34 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id F3322208A08; Wed, 19 Oct 2005 04:06:33 -0500 (EST) X-IronPort-AV: i="3.97,230,1125874800"; d="scan'208"; a="37777019:sNHT35052168" X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Subject: bitmap join indexes Date: Wed, 19 Oct 2005 10:04:31 +0100 Message-ID: <01907DC216FF7447BA6B9998CB82282F02F0061A@KENDC1.caci.co.uk> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: bitmap join indexes Thread-Index: AcXUjCGpQsUBHXd0ToatF2hnpSvR3Q== From: "Ottar Soerland" To: Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Content-Type: text/plain; charset="iso-8859-1" X-archive-position: 27175 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: OSoerland@caci.co.uk Precedence: normal Reply-To: OSoerland@caci.co.uk X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Level: X-Spam-Status: No, hits=-4.9 required=5.0 tests=BAYES_00 autolearn=ham version=2.63 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; end loop; end; / 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) */ 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 - 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@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-l