Re: Basic INDEX and SELECT questions

"lucky" <rahulsai_at_yahoo.com> wrote in message news:1126153018.221887.325110_at_o13g2000cwo.googlegroups.com...
> AFAIK,
> 1) Index order creation is not material, the type of index that you
> select for the cardinality of the field is material. use Bitmap index
> if field has low cardinality, use B Tree index for fast indexing and
> fields with high cardinality.

AARRGGGHHH! No. The order of the columns in the index does make a difference. You should NOT use bitmapped indexes in an OLTP system you will regret it. The whole subject is not trivial. You want the DBA knowledge but not work for it. I'd love to be a MD without having to go to school.

In general, and Oracle version is important here, indexes are most useful where they select a small subset of the data. If the index selects a large portion of the table then it will usually not be used. an index on a,b will work if a=x or a=x and b=y but not just b=y. What order you put the columns in the index is important depending upon what the queries are and the cardinality of the data. (uniqueness)

> 2) The more the indexes on a table, the more reindexing will have to be
> done frequently if large data is inserted, deleted etc. so you should
> be moderate with indexes.
>

False, utterly false. If you have more indexes on a table then it will take longer to insert, delete, and update records. Received on Wed Sep 07 2005 - 23:37:29 CDT