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
