Can someone tell me how indexes work ?
Does index stores some kind of number which select statement then accesses to make query faster ?
Is it stored in memory ? If so what happens when some one does DML on that table. Now you try to do select, how does index knows the new data ?
I read many documents on this but this is not explained any where. It is just said that good indexes improves performance, which I think everyone knows. How ??
When you insert records into a table, all the indexes related to this table have to be updated with the rowid information for the newly inserted record. If you have too many indexes on a table then this synchronization operation takes time.
Whether to create a bit map index or b-tree index depends on the requirement.If the column has low cardinality,then creating a bitmap index is better.If the column has a high cardinality,then creating a b-tree index is better.Hope this helps.