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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
