Re: Cardinality of indexed columns... how to discover? What is the ma

The answer to the question you asked is to check the dba_indexes view for columns
distinct_keys and num_rows.

HOWEVER - It is not sensible to change a b-tree index into a bitmap index simply because it has a relatively small number of distinct values. (Even if you have seen an article in Oracle Magazine that might suggest otherwise).

Bitmap indexes tend to degrade catastrophically as the underlying table suffers inserts and deletes or gets updates to the indexed column. And the probability of contention and deadlock on concurrent inserts/updates/deletes is very high.

Before going much further down your current path, you might like to read a couple of articles I've written about bitmap indexes at
  www.dbazine.com

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 20 December 2002 20:16
the ma

>I'm constantly finding, in the database that I'm
developing/supporting,
>b-tree indexes on columns with low-cardinality. I generally re-create
said
>index as a bitmap; however I'd like to go through the entire db and
identify
>these cases proactively. Is there a way to determine the cardinality
of an
>index's columns via the dictionary? Also, what recommendations do you
have
>for determining the cardinality percentage that indicates when a
bitmap
>should be used? 10%? 20%?
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).