Nov
21
Written by:
Richard To
Friday, November 21, 2008 5:54 AM
Written by Rene Woody
This blog is a continuation of a series about the SQL optimization process in the Batch Optimizer and the Tuning Lab modules of Quest SQL Optimizer for Oracle. It covers optimizing SQL statements that use VIEWs.
In early versions of the Oracle database, a VIEW was handled like a temporary table that stores the temporary data from the VIEW’s SELECT statement. Then the temporary table was joined with the main portion of the SQL statement, so the tuning of a SQL statement that contained a VIEW could be separated into the VIEW’s SQL statement and main SQL statement. In more recent versions of Oracle, the SQL optimizer in Oracle can merge these two SQL statements together before it does further SQL optimization, so, it is now more complicated for you to tune a VIEW SQL statement by hand than before. Quest SQL Optimizer has a “View to Inline View transformation” technique to solve this problem.
The optimization process in Quest SQL Optimizer includes this technique of rewriting the VIEW’s SQL statement when it transforms the syntax of the original SQL statement. So it will automatically do all the work for you.
For example, with this simple SQL statement
SELECT * FROM VIEW_DEPT
WHERE DPT_AVG_SALARY > 40000
that uses the following VIEW.
CREATE OR REPLACE VIEW VIEW_DEPT
(DPT_ID,
DPT_NAME,
DPT_MANAGER,
DPT_AVG_SALARY)
AS
SELECT "DPT_ID",
"DPT_NAME",
"DPT_MANAGER",
"DPT_AVG_SALARY"
FROM DEPARTMENT
WHERE DPT_ID IN (SELECT EMP_DEPT
FROM EMPLOYEE
WHERE EMP_ID > 50)
When the VIEW’s SELECT statement is inserted into the original SQL statement, the SQL statement looks like this:
SELECT *
FROM (SELECT "DPT_ID",
"DPT_NAME",
"DPT_MANAGER",
"DPT_AVG_SALARY"
FROM DEPARTMENT
WHERE DPT_ID IN (SELECT EMP_DEPT
FROM EMPLOYEE
WHERE EMP_ID > 50))
WHERE DPT_AVG_SALARY > 40000
The original SQL statement and the SQL statement with the VIEW inserted will both be rewritten by the Quest SQL Optimizer to generate all the possible SQL statements which produce the same results as the original SQL statement. Some of the SQL alternatives will not include the VIEW’s SQL and others will have the VIEW’s SQL rewritten.
An example of one of the SQL alternatives is:
SELECT *
FROM (SELECT DPT_ID,
DPT_NAME,
DPT_MANAGER,
DPT_AVG_SALARY
FROM DEPARTMENT
WHERE EXISTS (SELECT 'X'
FROM EMPLOYEE
WHERE EMP_ID > 50
AND EMP_DEPT = DEPARTMENT.DPT_ID))
WHERE DPT_AVG_SALARY > 40000
You can control whether the VIEW’s SQL is rewritten along with the original SQL statement with the Transform view to inline view setting in the Options. Quest SQL Optimizer will also transform a VIEW that is being used by another VIEW. You control how many levels (VIEWs within VIEWs) are included when the original SQL is rewritten by specifying the Transformation levels setting.