Optimizing SQL statements with a VIEW

This tips covers optimizing SQL statements that use a VIEW in place of a TABLE.

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 contains a VIEW can 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 for Oracle 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 > 4000

that uses the following VIEW.

CREATE OR REPLACE VIEW VIEW_DEPT
    (DPT_ID,
     DPT_NAME,
     DPT_MANAGER,
     DPT_AVG_SALARY)ASSELECT "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 viewsetting 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.