Some Parting Thoughts

As you've read, it's not that hard to obtain star transformation explain plans, and thus, fast ad-hoc report runtimes . But, I still find a lot of people wanting to push for parallel query and partitioning as their "silver bullets" for optimal performance. As I've expressed in this chapter, a non-partitioned and non-parallel query star transformation explain plan will run nearly as fast as a partitioned and parallel queried one. I'm stressing this because far too often, people turn on all the bells and whistles at once and then fail to hit the mark. If you start out with less features turned on and obtain star transformation, then turning on additional features will make things faster, and you will retain nearly identical star transformation explain plans.

To prove this, I've included four additional figures that show that the same star transformation explain plan is obtained for same query, even though parallel and partitioning options are varied as follows :

• Non-parallel and non-partitioned (Figure 5-7)

  Figure 5-7. Star Transformation for Non-Parallel and Non-Partitioned

  

• Non-parallel and range-partitioned (Figure 5-8)

  Figure 5-8. Star Transformation for Non-Parallel and Range-Partitioned

  

• Parallel and non-partitioned (Figure 5-9)

  Figure 5-9. Star Transformation for Parallel and Non-Partitioned

  

• Parallel and range-partitioned (Figure 5-10)

  Figure 5-10. Star Transformation for Parallel and Range-Partitioned

  

Please note that the star transformation portion of the explain plan is highlighted on the left-hand side of each figure, and is identical. Only the right-hand side changes (which is also highlighted) regarding parallel operations for some steps or partition elimination as an additional step. So again, if you can just get the basic star transformation explain plan to work, you can make it even better with these additional Oracle features.