Index Questions

[grrickar]

Gearing up for certification and came across these things I would like to know about indexes:

What overhead/benefit is added when indexes are logged? I know this writes updates to the redo logs, which will add some overhead, but what is gained by using logging vs nologging?

Correct me if I am wrong here: A b-tree index is best for high cardinality, and in the index a key may be repeated but with different rowids paired with the key. A bitmap index has the key only once, but has many rowid values for that key. Best used for low cardinality for this reason.


Thanks!

[Halo]

With Nologging, you will not be able to recover the index.

[jmodic]

- Logging/nologging for indexes:
Nologging only means that during initial creation (ore rebuild) of the index, the index entries are not logged in redo logs. This might dramatically speed the index creation. But all subsequent changes to indexed columns (inserts, updates, deletes) will be logged normaly. As Halo mentioned, you have to recreate index manualy in case of the recovery, because index entries were not recorded during creation. But this is true only until your first backup after you create the index. As soon as you make your first backup (hot or cold) you are safe, index will be recovered normaly after that. That is why it is very advisable to meka a database backup as soon as possible after every nologged operation.

- Your assumption about b*tree and bitmap indexes are correct (except that bitmap indexes do not actually store the rowids). I would only add one thing: (very) low cardinality does not necessarily mean the column is not suited for a b*tree index. For example, you could have a 100.000, 100 of which have some value in a column A, other 999.900 have NULL in that column. Your application mostly perform queries and DMLs on those not-null records. Here an index on that column might dramaticaly improve the performance, and depending on circumstances it would be better to choose B*tree over bitmap.