RE: bitmap join indexes

Mladen,  

an access-path using only bitmap indexes is what I'm hoping for. In the 2nd query this is achieved and the response time is 0.6 seconds. The 3rd query should have chosen a similar path (just using an extra bitmap index corresponding to the added expression) - however the optimiser chooses to (in addition to using the bitmap index) to also visit the tables and the response time goes up to 16 seconds. I do not understand why it needs to read those tables as all the bitmap indexes are based on the same rowid (even though the columns are on different tables).  

Ottar.

-----Original Message-----

From: Gogala, Mladen [mailto:MGogala_at_allegientsystems.com] Sent: 19 October 2005 15:04
To: Ottar Soerland; oracle-l_at_freelists.org Subject: RE: bitmap join indexes

Ottar, I see that you are converting bitmap to rowid and are accessing CL_CENTRAL through some form of B-Tree index. Have you tried all bitmap indexes and star schema? I'm actually an OLTP guy, but the best book

on the topic is "Essential Oracle8i Data Warehousing". One of the authors of the book also has an excellent website: http://www.evdbt.com.

--

Mladen Gogala
Ext. 121
-----Original Message-----

From: Ottar Soerland [ mailto:OSoerland_at_caci.co.uk] Sent: Wednesday, October 19, 2005 5:05 AM To: oracle-l_at_freelists.org
Subject: 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.

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-l Received on Wed Oct 19 2005 - 11:36:25 CDT