Wednesday, October 6, 2010

Exadata and indexes

This has been a very interesting topic around my shop.. Some people say that you can get rid of all you indexes, some people say no..

Well first lets look at why you have indexes and rule out those as removal candidates.

1) Indexes that support Primary keys. Gotta keep those right ?

2) Indexes that support RI to avoid locking. OLTP ? Gotta keep those.

For a lot of OLTP applications, just the 2 above criteria is enough to keep most of your indexes in play. But what about everything else.

Here is what I've been seeing. The exadata can scan like crazy, but there is a limit (20.8 on a full rack, do the math for your configuration). If you have a FTS on a table containing 50G, you can see that you utilizing ALL I/O for almost 3 seconds. if you have any concurrency, you can imagin what happens.

So in my mind the answer is to keep indexes where they can significantly limit the data access.

No comments:

Post a Comment