RE: bitmap join indexes
- From: "Ottar Soerland" <OSoerland@xxxxxxxxxx>
- To: "Gogala, Mladen" <MGogala@xxxxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 19 Oct 2005 17:33:55 +0100
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@xxxxxxxxxxxxxxxxxxxx]
Sent: 19 October 2005 15:04
To: Ottar Soerland; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxx]
Sent: Wednesday, October 19, 2005 5:05 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxx
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.
Other related posts:
