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 11.2.0.2 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've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply