RE: Bitmap index or no index?
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.
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
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.
http://www.freelists.org/webpage/oracle-l Received on Mon Apr 06 2009 - 20:32:23 CDT