Index Questions
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Index Questions

  1. #1
    Join Date
    May 2000
    Location
    Huntsville, AL. USA
    Posts
    52
    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!

  2. #2
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    With Nologging, you will not be able to recover the index.

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    - 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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width