As I have been a part of many detailed conversations with Oracle Experts regarding “Use of indexes on Exadata”, I decided to share my thoughts and experience on this topic.
I have been working with Exadata since 2011 and have been a part of many implementations and POC’s. As per my experience, Exadata works better without indexes but getting rid of all indexes is not a practical approach. I have implemented / migrated different types of applications (OLTP / OLAP) to Exadata and there were some cases where I was not allowed to make any application changes. Application changes like dropping an index, partitioning will require testing and will not be easy as it sounds. if you have worked with applications like EBS and SAP, you understand how difficult it will be to make any changes to the environment.
Personally I recommend following balance approach when it comes to use of indexes on Exadata.
- Don’t drop all the indexes
- Keep primary key / unique indexes
- You can drop bit map indexes
- Use invisible index options when possible
- Avoid indexes using SQL HINTS
- Drop and rebuild indexes during ETL load