RE: Bitmap index or no index?

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <topshot.rhit@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 6 Apr 2009 21:32:23 -0400

Is this a "needs work down" as a side effect of some process?

If so, make a table that is the list of pks that need work. Then use that
tiny table to drive everything.

This fundamental flow happens frequently. Recognizing it and keeping the
needles is a far better process than tossing the needles into the haystack
and trying to figure out whether to burn the haystack down or find a huge
magnet is best way to find the lost needles.

Solve easier problems.


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Michael Ray
Sent: Monday, April 06, 2009 4:24 PM
To: oracle-l@xxxxxxxxxxxxx
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.


Michael Ray


Other related posts: