Inline Views – What is Wrong with this Quote?

18 09 2011

September 18, 2011

I do not recall putting together any articles about inline views, so let’s start out with a couple of examples before taking a look at a quote.  We need to create a table for this example, so I will reuse a slightly modified table creation script from another article, and also collect statistics with 100% sampling.

DROP TABLE T1 PURGE;

CREATE TABLE T1(
  C1 NUMBER,
  C2 NUMBER,
  C3 NUMBER,
  C4 DATE,
  C5 DATE,
  C6 DATE,
  C7 VARCHAR2(20),
  C8 VARCHAR2(20),
  C9 VARCHAR2(20),
  PRIMARY KEY(C1));

INSERT INTO
  T1
SELECT
  ROWNUM,
  DECODE(MOD(ROWNUM,5),0,NULL,ROWNUM),
  DECODE(MOD(ROWNUM,5),0,NULL,ROWNUM),
  TRUNC(SYSDATE)+TRUNC(ROWNUM/100),
  DECODE(MOD(ROWNUM,5),0,NULL,TRUNC(SYSDATE)+TRUNC(ROWNUM/100)),
  DECODE(MOD(ROWNUM,5),0,NULL,TRUNC(SYSDATE)+TRUNC(ROWNUM/100)),
  RPAD(CHR(MOD(ROWNUM-1,26)+65)||
    CHR(MOD(ROWNUM,26)+65)||
    CHR(MOD(ROWNUM+1,26)+65)||
    CHR(MOD(ROWNUM+2,26)+65),20,'A'),
  DECODE(MOD(ROWNUM,5),0,NULL,
   RPAD(CHR(MOD(ROWNUM-1,26)+65)||
    CHR(MOD(ROWNUM,26)+65)||
    CHR(MOD(ROWNUM+1,26)+65)||
    CHR(MOD(ROWNUM+2,26)+65),20,'A')),
  DECODE(MOD(ROWNUM,5),0,NULL,
   RPAD(CHR(MOD(ROWNUM-1,26)+65)||
    CHR(MOD(ROWNUM,26)+65)||
    CHR(MOD(ROWNUM+1,26)+65)||
    CHR(MOD(ROWNUM+2,26)+65),20,'A'))
FROM
  DUAL
CONNECT BY
  LEVEL<=200000;

COMMIT;

CREATE INDEX IND_T1_C2 ON T1(C2);
CREATE INDEX IND_T1_C7 ON T1(C7);

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE,ESTIMATE_PERCENT=>NULL)

Now, let’s create a simple SQL statement that references the above table 3 times, twice in inline views and once again outside of an inline view:

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT
  T1.C1,
  T1.C2
FROM
  T1,
  (SELECT
     C1,
     C2,
     C3,
     C4
   FROM
     T1
   WHERE
     C1 BETWEEN 1 AND 10000) V1,
  (SELECT
     C1,
     C6,
     C7,
     C8
   FROM
     T1
   WHERE
     C1 BETWEEN 9000 AND 12000) V2
WHERE
  T1.C1=V1.C1
  AND T1.C1=V2.C1;

If I execute the above on Oracle Database 11.2.0.2 I see the following autotrace generated execution plan:

Execution Plan
----------------------------------------------------------
Plan hash value: 3832506612

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |  1002 | 10020 |    17   (0)| 00:00:22 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1           |  1002 | 10020 |    17   (0)| 00:00:22 |
|*  2 |   INDEX RANGE SCAN          | SYS_C0039297 |  1002 |       |     3   (0)| 00:00:04 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."C1">=9000 AND "T1"."C1"<=10000)

Notice in my query I did not reference any of the columns in view V1 or view V2 – the table T1 appears only a single time in the execution plan.  Let’s fix the query and try again:

SELECT
  T1.C1,
  T1.C2,
  V1.C3,
  V1.C4,
  V2.C6,
  V2.C7,
  V2.C8
FROM
  T1,
  (SELECT
     C1,
     C2,
     C3,
     C4
   FROM
     T1
   WHERE
     C1 BETWEEN 1 AND 10000) V1,
  (SELECT
     C1,
     C6,
     C7,
     C8
   FROM
     T1
   WHERE
     C1 BETWEEN 9000 AND 12000) V2
WHERE
  T1.C1=V1.C1
  AND T1.C1=V2.C1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3832506612

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |  1002 | 68136 |    17   (0)| 00:00:22 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1           |  1002 | 68136 |    17   (0)| 00:00:22 |
|*  2 |   INDEX RANGE SCAN          | SYS_C0039297 |  1002 |       |     3   (0)| 00:00:04 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."C1">=9000 AND "T1"."C1"<=10000)

The above was the desired result.

Let’s create a second table that is identical to the first table:

DROP TABLE T2 PURGE;

CREATE TABLE T2(
  C1 NUMBER,
  C2 NUMBER,
  C3 NUMBER,
  C4 DATE,
  C5 DATE,
  C6 DATE,
  C7 VARCHAR2(20),
  C8 VARCHAR2(20),
  C9 VARCHAR2(20),
  PRIMARY KEY(C1));

INSERT INTO
  T2
SELECT
  ROWNUM,
  DECODE(MOD(ROWNUM,5),0,NULL,ROWNUM),
  DECODE(MOD(ROWNUM,5),0,NULL,ROWNUM),
  TRUNC(SYSDATE)+TRUNC(ROWNUM/100),
  DECODE(MOD(ROWNUM,5),0,NULL,TRUNC(SYSDATE)+TRUNC(ROWNUM/100)),
  DECODE(MOD(ROWNUM,5),0,NULL,TRUNC(SYSDATE)+TRUNC(ROWNUM/100)),
  RPAD(CHR(MOD(ROWNUM-1,26)+65)||
    CHR(MOD(ROWNUM,26)+65)||
    CHR(MOD(ROWNUM+1,26)+65)||
    CHR(MOD(ROWNUM+2,26)+65),20,'A'),
  DECODE(MOD(ROWNUM,5),0,NULL,
   RPAD(CHR(MOD(ROWNUM-1,26)+65)||
    CHR(MOD(ROWNUM,26)+65)||
    CHR(MOD(ROWNUM+1,26)+65)||
    CHR(MOD(ROWNUM+2,26)+65),20,'A')),
  DECODE(MOD(ROWNUM,5),0,NULL,
   RPAD(CHR(MOD(ROWNUM-1,26)+65)||
    CHR(MOD(ROWNUM,26)+65)||
    CHR(MOD(ROWNUM+1,26)+65)||
    CHR(MOD(ROWNUM+2,26)+65),20,'A'))
FROM
  DUAL
CONNECT BY
  LEVEL<=200000;

COMMIT;

CREATE INDEX IND_T2_C2 ON T2(C2);
CREATE INDEX IND_T2_C7 ON T2(C7);

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2',CASCADE=>TRUE,ESTIMATE_PERCENT=>NULL)

Let’s modify the last query to access the table T2 in the second inline view and take a look at the autotrace generated execution plan for the query:

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT
  T1.C1,
  T1.C2,
  V1.C3,
  V1.C4,
  V2.C6,
  V2.C7,
  V2.C8
FROM
  T1,
  (SELECT
     C1,
     C2,
     C3,
     C4
   FROM
     T1
   WHERE
     C1 BETWEEN 1 AND 10000) V1,
  (SELECT
     C1,
     C6,
     C7,
     C8
   FROM
     T2
   WHERE
     C1 BETWEEN 9000 AND 12000) V2
WHERE
  T1.C1=V1.C1
  AND T1.C1=V2.C1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2810660667

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |  1001 | 73073 |    35   (3)| 00:00:44 |
|*  1 |  HASH JOIN                   |              |  1001 | 73073 |    35   (3)| 00:00:44 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1           |  1002 | 23046 |    17   (0)| 00:00:22 |
|*  3 |    INDEX RANGE SCAN          | SYS_C0039297 |  1002 |       |     3   (0)| 00:00:04 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T2           |  1002 | 50100 |    17   (0)| 00:00:22 |
|*  5 |    INDEX RANGE SCAN          | SYS_C0039300 |  1002 |       |     3   (0)| 00:00:04 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."C1"="C1")
   3 - access("T1"."C1">=9000 AND "T1"."C1"<=10000)
   5 - access("C1">=9000 AND "C1"<=10000)

If you do not like inline views you can instead take advantage of subquery factoring (WITH blocks):

WITH
  V1 AS (SELECT
     C1,
     C2,
     C3,
     C4
   FROM
     T1
   WHERE
     C1 BETWEEN 1 AND 10000),
  V2 AS (SELECT
     C1,
     C6,
     C7,
     C8
   FROM
     T2
   WHERE
     C1 BETWEEN 9000 AND 12000)
SELECT
  T1.C1,
  T1.C2,
  V1.C3,
  V1.C4,
  V2.C6,
  V2.C7,
  V2.C8
FROM
  T1,
  V1,
  V2
WHERE
  T1.C1=V1.C1
  AND T1.C1=V2.C1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2810660667

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |  1001 | 73073 |    35   (3)| 00:00:44 |
|*  1 |  HASH JOIN                   |              |  1001 | 73073 |    35   (3)| 00:00:44 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1           |  1002 | 23046 |    17   (0)| 00:00:22 |
|*  3 |    INDEX RANGE SCAN          | SYS_C0039297 |  1002 |       |     3   (0)| 00:00:04 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T2           |  1002 | 50100 |    17   (0)| 00:00:22 |
|*  5 |    INDEX RANGE SCAN          | SYS_C0039300 |  1002 |       |     3   (0)| 00:00:04 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."C1"="C1")
   3 - access("T1"."C1">=9000 AND "T1"."C1"<=10000)
   5 - access("C1">=9000 AND "C1"<=10000)

—

Hints may be used to change the optimization behavior of the query optimizer.  For instance, if we specify the NO_QUERY_TRANSFORMATION hint in the last of the above queries that used inline views, the autotrace generated execution plan appears as follows:

SELECT /*+ NO_QUERY_TRANSFORMATION */
  T1.C1,
  T1.C2,
  V1.C3,
  V1.C4,
  V2.C6,
  V2.C7,
  V2.C8
FROM
  T1,
  (SELECT
     C1,
     C2,
     C3,
     C4
   FROM
     T1
   WHERE
     C1 BETWEEN 1 AND 10000) V1,
  (SELECT
     C1,
     C6,
     C7,
     C8
   FROM
     T2
   WHERE
     C1 BETWEEN 9000 AND 12000) V2
WHERE
  T1.C1=V1.C1
  AND T1.C1=V2.C1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3681900949

------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |  1002 | 91182 |  1037   (1)| 00:21:33 |
|*  1 |  HASH JOIN                      |              |  1002 | 91182 |  1037   (1)| 00:21:33 |
|   2 |   NESTED LOOPS                  |              |       |       |            |          |
|   3 |    NESTED LOOPS                 |              |  1002 | 45090 |  1019   (0)| 00:21:11 |
|   4 |     VIEW                        |              |  1002 | 35070 |    17   (0)| 00:00:22 |
|   5 |      TABLE ACCESS BY INDEX ROWID| T1           |  1002 | 18036 |    17   (0)| 00:00:22 |
|*  6 |       INDEX RANGE SCAN          | SYS_C0039297 |  1002 |       |     3   (0)| 00:00:04 |
|*  7 |     INDEX UNIQUE SCAN           | SYS_C0039297 |     1 |       |     0   (0)| 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID  | T1           |     1 |    10 |     1   (0)| 00:00:02 |
|   9 |   VIEW                          |              |  1002 | 46092 |    17   (0)| 00:00:22 |
|  10 |    TABLE ACCESS BY INDEX ROWID  | T2           |  1002 | 50100 |    17   (0)| 00:00:22 |
|* 11 |     INDEX RANGE SCAN            | SYS_C0039300 |  1002 |       |     3   (0)| 00:00:04 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."C1"="V2"."C1")
   6 - access("C1">=9000 AND "C1"<=10000)
   7 - access("T1"."C1"="V1"."C1")
  11 - access("C1">=9000 AND "C1"<=10000)

For fun, on the Enterprise Edition of Oracle Database (if I remember correctly, the Standard Edition will behave differently – Edit Sept. 18, 2011: just tested 11.2.0.2 Standard Edition and 10.2.0.5 Standard Edition – both versions generated execution plans similar to what is displayed below, so the MATERIALIZE hint apparently does not behave differently as originally described) we can also use the MATERIALIZE hint using the subquery factored (WITH block) version of the query:

WITH
  V1 AS (SELECT /*+ MATERIALIZE */
     C1,
     C2,
     C3,
     C4
   FROM
     T1
   WHERE
     C1 BETWEEN 1 AND 10000),
  V2 AS (SELECT /*+ MATERIALIZE */
     C1,
     C6,
     C7,
     C8
   FROM
     T2
   WHERE
     C1 BETWEEN 9000 AND 12000)
SELECT
  T1.C1,
  T1.C2,
  V1.C3,
  V1.C4,
  V2.C6,
  V2.C7,
  V2.C8
FROM
  T1,
  V1,
  V2
WHERE
  T1.C1=V1.C1
  AND T1.C1=V2.C1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1193025797

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                           |   150 | 13650 |  1491   (1)| 00:31:00 |
|   1 |  TEMP TABLE TRANSFORMATION    |                           |       |       |            |          |
|   2 |   LOAD AS SELECT              | SYS_TEMP_0FD9D6625_D08144 |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1                        | 10000 |   224K|   151   (0)| 00:03:09 |
|*  4 |     INDEX RANGE SCAN          | SYS_C0039297              | 10000 |       |    20   (0)| 00:00:25 |
|   5 |   LOAD AS SELECT              | SYS_TEMP_0FD9D6626_D08144 |       |       |            |          |
|   6 |    TABLE ACCESS BY INDEX ROWID| T2                        |  3002 |   146K|    47   (0)| 00:00:59 |
|*  7 |     INDEX RANGE SCAN          | SYS_C0039300              |  3002 |       |     7   (0)| 00:00:09 |
|*  8 |   HASH JOIN                   |                           |   150 | 13650 |  1293   (1)| 00:26:53 |
|*  9 |    HASH JOIN                  |                           |  3002 |   164K|  1276   (1)| 00:26:31 |
|  10 |     VIEW                      |                           |  3002 |   134K|    12   (0)| 00:00:15 |
|  11 |      TABLE ACCESS FULL        | SYS_TEMP_0FD9D6626_D08144 |  3002 |   146K|    12   (0)| 00:00:15 |
|  12 |     TABLE ACCESS FULL         | T1                        |   200K|  1953K|  1263   (0)| 00:26:16 |
|  13 |    VIEW                       |                           | 10000 |   341K|    17   (0)| 00:00:22 |
|  14 |     TABLE ACCESS FULL         | SYS_TEMP_0FD9D6625_D08144 | 10000 |   224K|    17   (0)| 00:00:22 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("C1">=1 AND "C1"<=10000)
   7 - access("C1">=9000 AND "C1"<=12000)
   8 - access("T1"."C1"="V1"."C1")
   9 - access("T1"."C1"="V2"."C1")

Ouch, an estimated 31 minutes to complete?

SET AUTOTRACE OFF

SELECT
  PNAME,
  PVAL1
FROM
  SYS.AUX_STATS$
WHERE
  PNAME IN ('SREADTIM','MREADTIM');

PNAME         PVAL1
-------- ----------
SREADTIM   1247.132
MREADTIM   3575.567

Must be that darn floppy RAID 10 array again.  Let’s make it faster:

EXEC DBMS_STATS.SET_SYSTEM_STATS('SREADTIM',0.4)
EXEC DBMS_STATS.SET_SYSTEM_STATS('MREADTIM',0.8)

SET AUTOTRACE TRACEONLY EXPLAIN

WITH
  V1 AS (SELECT /*+ MATERIALIZE */
     C1,
     C2,
     C3,
     C4
   FROM
     T1
   WHERE
     C1 BETWEEN 1 AND 10000),
  V2 AS (SELECT /*+ MATERIALIZE */
     C1,
     C6,
     C7,
     C8
   FROM
     T2
   WHERE
     C1 BETWEEN 9000 AND 12000)
SELECT
  T1.C1,
  T1.C2,
  V1.C3,
  V1.C4,
  V2.C6,
  V2.C7,
  V2.C8
FROM
  T1,
  V1,
  V2
WHERE
  T1.C1=V1.C1
  AND T1.C1=V2.C1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1626848331

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                           |   150 | 13650 |  1211  (10)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION    |                           |       |       |            |          |
|   2 |   LOAD AS SELECT              | SYS_TEMP_0FD9D6627_D08144 |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1                        | 10000 |   224K|   158   (5)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | SYS_C0039297              | 10000 |       |    23  (14)| 00:00:01 |
|   5 |   LOAD AS SELECT              | SYS_TEMP_0FD9D6628_D08144 |       |       |            |          |
|   6 |    TABLE ACCESS BY INDEX ROWID| T2                        |  3002 |   146K|    49   (5)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN          | SYS_C0039300              |  3002 |       |     8  (13)| 00:00:01 |
|*  8 |   HASH JOIN                   |                           |   150 | 13650 |  1004  (11)| 00:00:01 |
|*  9 |    HASH JOIN                  |                           |  3002 |   164K|   987  (10)| 00:00:01 |
|  10 |     VIEW                      |                           |  3002 |   134K|    10  (10)| 00:00:01 |
|  11 |      TABLE ACCESS FULL        | SYS_TEMP_0FD9D6628_D08144 |  3002 |   146K|    10  (10)| 00:00:01 |
|  12 |     TABLE ACCESS FULL         | T1                        |   200K|  1953K|   950   (8)| 00:00:01 |
|  13 |    VIEW                       |                           | 10000 |   341K|    15  (20)| 00:00:01 |
|  14 |     TABLE ACCESS FULL         | SYS_TEMP_0FD9D6627_D08144 | 10000 |   224K|    15  (20)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("C1">=1 AND "C1"<=10000)
   7 - access("C1">=9000 AND "C1"<=12000)
   8 - access("T1"."C1"="V1"."C1")
   9 - access("T1"."C1"="V2"."C1")

A one second estimated completion time, that is better.  Bugs…

—

—

—

—

—

—

—

—

Now that hopefully everyone is on the same page about what an inline view is and how to use one in a query, let’s take a look at two quotes about inline views from the book Oracle 11g Performance Tuning Recipes:

Page 291 (click the link to see the quote in full context):

“While extremely useful, if misused or overused, inline views can cause database performance issues, especially in terms of the use of the temporary tablespace. Since inline views are created and used only for the duration of a query, their results are held in the program global memory area, and if too large, the temporary tablespace.”

Page 292 (note that this quote is quite a bit longer than I would prefer, but it is as short as possible without losing the context of the quote):

“In this query, there are three inline views created: the SERVICE_INFO view, the PRODUCT_INFO view, and the BILLING_INFO view. Each of these queries will be processed and the results stored in the program global area or the temporary tablespace before finally processing the true end-user query, which starts with the final SELECT statement shown in the query.  In examples such as these, it is generally more efficient at the database level to create tables that hold the data defined by the inline views—in this case, three separate tables. Then, the final query can be extracted from joining the three permanent tables to generate the results.”

What, if anything, is wrong with the above quote from the book?

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.


Comments : 7 Comments »

Categories : Quiz - Whose Answer is it Anyway?