RE: Bitmap index or no index?

From: Brady, Mark <Mark.Brady_at_Constellation.Com>
Date: Mon, 6 Apr 2009 18:03:18 -0400
Message-ID: <46732D8B0755664E87B4276E3C2FF6056ECBF33E90_at_EXM-OMF-06.Ceg.Corp.Net>



How many processes simultaneously write to mfgunit and/or the other tables?

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Michael Ray Sent: Monday, April 06, 2009 4:24 PM
To: oracle-l_at_freelists.org
Subject: Bitmap index or no index?

For the following query should I bother with a bitmap index on backflushed or just go without an index?

select replace(p.item_no, '-'), count(*) from pumpdata pd, part p, mfgunit m
where pd.mfgunitid = m.mfgunitid and m.backflushed IS NULL

   and pd.partid = p.partid and pd.pumpdefectsid=0 group by p.item_no
order by 2;

This is 10.2 with a couple million rows in the mfgunit table. The number of rows where backflushed is null will be very few FOR the records that match the join condition with the pumpdata table. Once the records that match this query are processed, backflushed is set for them. A process will regularly add new records that will match the query. Looking at the query I may add a bitmap on pumpdefectsid if it doesn't exist already since that only has 7 values I think.

-- 

Sincerely,
Michael Ray
--
http://www.freelists.org/webpage/oracle-l



>>> This e-mail and any attachments are confidential, may contain legal, professional or other privileged information, and are intended solely for the addressee. If you are not the intended recipient, do not use the information in this e-mail in any way, delete this e-mail and notify the sender. CEG-IP1
-- http://www.freelists.org/webpage/oracle-l
Received on Mon Apr 06 2009 - 17:03:18 CDT

Original text of this message