Introduction to Oracle SQL Tuning with View Merging

Watch hundreds of Oracle Database tutorials, free, at

Description:
A vital optimization that your queries need

Oracle’s Cost Based Optimizer (CBO) is an incredible software artifact, possibly the most complex we will ever work with. But it is not infallible: the CBO needs our help. In this webinar you will learn how we can provide help related to View Merging (aka Unnesting) to the CBO and – in some cases – dramatically reduce response time with a simple rewrite of the query!

One case is CBO’s ability to merge view definitions and subqueries during optimization. If there are two views referenced in the query, the CBO may choose to build two results sets independently, then merge two results to create a single result set to pass the user OR merge the view definitions and create a single result set to be passed to the user. The latter plan – reducing the number of results sets to be merged – can often deliver astronomical performance benefits. But not if the syntax of the query prevents it. We need to learn how to not prevent view merging.

You will learn – through demonstrations and proving theories with execution plans – what query structures prevent View Merging. You will learn to recognize View Merging – or the lack of it – in execution plans. You’ll learn what the related suspicious structures are – the “Red Flags”.

Prerequisite Knowledge:
Solid experience with Oracle SQL SELECT statement clauses and functions.

Basic experience reading Oracle execution plans. See SkillBuilders’ Free Tutorial How to Read an Execution Plan for assistance.

Some familiarity with Oracle sample schemas, including Scott, Human Resources (HR), Sales History (SH) and Order Entry (OE). If you haven’t used these demonstration schemas before, take a couple of minutes to get familiar with them. Refer to the Oracle Documentation for schema diagrams.

Presenter:
John Watson, Oracle Certified Master DBA, SkillBuilders Director of All Things Oracle Database