I'm back on the road this month, meeting with customers to discuss their initial impressions and experiences with Oracle Database In-Memory. During one such discussion, I got asked a very peculiar question. The question was, "Do I really have to drop all of my reporting indexes if I use Database In-Memory?"
I have to admit I was a little taken aback by this question. After all, I thought most folks would be delighted to have an opportunity to give up the majority of their indexes, not just because of the space savings and DML performance benefits but also the maintenance nightmare that indexes can sometimes become.
Assuming this was a trick question, I deployed the standard stalling technique of answering a question with a question, “Can you tell me a little more about your situation?”
To which the system architect explained that they were in production with Oracle Database In-Memory on a 2 node RAC cluster running on commodity servers and a crap IO subsystem (his words, not mine). They had a snowflake schema, and had enough memory to accommodate all of their dimension tables but only the last 3 months of data in their two fact tables. Following my guidelines, they had kept their primary key indexes but dropped the rest of their indexes. He assured me that the performance of most of their queries had improved 100X and their ETL jobs were finishing 2X faster without the indexes but there were some queries that accessed more than just the last 3 months worth of data in the fact table and their performance had gotten worse, a lot worse.
It was in that moment that I realized that our guidance on dropping all reporting indexes with Database In-Memory had been based on an assumption that was not always true. The assumption I had been working under was; all of your performance critical data resides in memory or you have a good IO sub-system (engineered system etc.)