Denormalize on Exadata compared with index

I modified the example from my previous post to see if on Exadata I could denormalize two tables and get the same performance without an index on the denormalized table that I get with the index on the normalized ones.

Here is the zip: zip of example.

This is on an Exadata V2 with BP20.

What is interesting is that with an index and normalized tables I get these elapsed times:

Elapsed: 00:00:00.09
Elapsed: 00:00:00.11
Elapsed: 00:00:00.09
Elapsed: 00:00:00.10
Elapsed: 00:00:00.09

With the single denormalized table I get these times:

Elapsed: 00:00:00.12
Elapsed: 00:00:00.11
Elapsed: 00:00:00.12
Elapsed: 00:00:00.11
Elapsed: 00:00:00.11

So these are the equivalent with no index.  Of course without the hint the normalized tables are much slower with the full scans and hash join:

Elapsed: 00:00:01.48
Elapsed: 00:00:01.59
Elapsed: 00:00:01.70
Elapsed: 00:00:01.41
Elapsed: 00:00:01.65

I’m really thinking about our OBIEE queries that run on Exadata.  They have a wide variety of queries that have conditions on dimension tables that limit the rows on a huge fact table.  What if the columns used in the conditions were copied into the fact table and then all the conditions were on that table?  I’m not sure if the OBIEE RPD can be changed in this way but it seems like it would be a great way of using the Exadata smart scans.  I’d love to hear from anyone who had used this approach or seen problems with it.

– Bobby


About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.