Very Computer

I have a relatively simple index question;
A developer has asked that an index be placed on a table column that
stores either a 1 or 0 value.
There are two other indexes on this particular table.
I'm not sure if this will actually help anything, but would appreciate
any discenting viewpoints and opinions.

Thanks in advance.

Quote:> I have a relatively simple index question;
> A developer has asked that an index be placed on a table column that
> stores either a 1 or 0 value.
> There are two other indexes on this particular table.

Given that there are only two values for this column, a regular b-tree
index would be of little value. You can, however, create a bitmap index
on this column, which is ideal for columns of low cardinality, provided
you are running at least Oracle 8x. The question of whether or not this
bitmap index will actually be of any value depends upon your queries.
That is, does the query access the column in the WHERE clause? I've
seen excellent improvements in queries using bitmap indexes so long as
the column is referenced in the where clause of the query. Also, keep
in mind that creating additional indexes incurs more storage and slows
down update, delete and insert statements.

Quote:> I'm not sure if this will actually help anything, but would appreciate
> any discenting viewpoints and opinions.

> Thanks in advance.

Sent via Deja.com http://www.deja.com/
Before you buy.

If the data is evenly distributed , this is waste of storage. Most likely as
one of the two values will account for more than 10 percent, the CBO will
not use it at all. You need to look into using bitmap indexes.

Hth,

--
Sybrand Bakker, Oracle DBA

In my environment, we have an OK_TO_MAIL flag
whose meaning is 'Y' = ok to mail else null = not ok.

It's indexed (the null ones are not in the index of course)
so that joins using this field are quite quick.

(on 7.3.2 -- no bitmapped indexes until 7.3.4)

Skip the index.

A bitmap index is only appropriate if the table is relatively static, due to
the cost of maintaining it.   I think the solution is to educate your
developer.

A summary of the other posts:

a) data evenly spread means that normal index isnt much use
b) uneven distribution could get benefit from normal index

c) bitmap indexes great but:
 i) overhead to maintain is more expensive
 ii) locking concerns are much larger

d) foreign key locking may (or may not) mandate the use of a normal
index...

HTH
--
===========================================
Connor McDonald
"These views mine, no-one elses etc etc"

"Some days you're the pigeon, and some days you're the statue."

What about on a column where the data is the following:

Networks1
Networks2
Networks3
Networks4

etc. etc.

hi,
if the number of distinct values in lower than 5 or 6, then usually a b-tree
index is
not helpfull, a bitmap one may be more efficient, but as you said, it all
depends
on the SQL you're writing.

rgds

gsalem.vcf < 1K Download