Re: performance impact of index

"Gordon" <gordon_t_wu_at_hotmail.com> wrote in message news:HEorc.290$Ny6.915_at_mencken.net.nih.gov...
> Hi,
>
> I have a schema which is highly denormalized and purely read-only. For
the
> purpose of fast data access, is there any reason not to create a bitmap
> index on every field for every table?
>
> Gordon

Hi Gordon

I think that there is a good reason to understand why it is sometimes suggested that DBA stands for Does B***** All. You have actually highlighted this by asking exactly the wrong question.

Try asking 'For the purposes of fast data access, is there any reason *to* create a bitmap access on every field for every table?'.

This question has some presuppositions

1. Access isn't fast 'enough'.

Well what is 'fast enough' anyway? If you can't answer this go no further - (do BA)
If you can answer this in the affirmative go no further (do BA).

2. you can't do anything less drastic that has the same effect. well test it, analyse the problem processes and seek to optimize them - often this is actually trivial but left undone.

3. Creating a bitmap index on every field in every table will make things *that matter* go 'fast enough'.
well test it. then take a good hard look at what the *things that matter* actually use, if they don't use an index then don't create it (do BA).

4. Creating a bitmap index on every field in every table will not make things *that matter* go 'too slowly'.
well test it....

In short I think that you may find that bitmap indexes are of use when selectively applied and only when the analysis in 2 suggests that they are effective.

err and I've never yet met a read only database with data that never ever ever changed, and if I did I'd suspect it was unnecessary.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK
http://www.niall.litchfield.dial.pipex.com/