SQL tuning with views

Oracle views provide a benefit in these important areas:

·Code reuse: Views ensure that everyone uses the exact same SQL to access their tables.

·Column access security: Using the "grant" security model, views can be used for column-level security, such that some columns in a table may be "hidden" by not specifying them in a view.

For all of the benefits that Oracle views provide, they do come at a cost. One downside to using views is that it adds complexity to the SQL and makes it harder for the optimizer to service a query with the minimum amount of resources. This can pertain to either I/O or CPU resources, depending on the optimizer goal.

While it is clear that views are useful for end-user ad hoc queries and cases where the goal is to simplify the syntax of complex SQL queries, the following serious problems can occur when queries contain views:

·Predicate pushing: The downside to reusable code is that WHERE clause predicates must be "pushed" down the hierarchy of views to the base query. This adds processing overhead to the optimizer and increases the chances of a bad execution plan

·Non mergeable views: Because a view is an encapsulation of a complex query, it is used as if it were a discrete relational table. Hence, Oracle must pre-materialize a view whenever it is used in a query. This creates a hidden sub-plan that must be exposed for SQL tuning.

·Unnecessary overhead: Views are abused when they introduce unnecessary complexity. For example, there may be a call to a view that is composed of 10 tables where the result set only requires data from two tables.

·Excessive hard parsing: Predicate pushing may result in a hard parse of the underlying SQL that is executed. Hence, it is important to make sure bind variables are used instead of literals in SQL code calling views.

Read on to see important tips and tricks for tuning SQL that contains views.

..more..