【sql調優之執行計劃】temp table transformation

使用系統臨時表的時候，執行計劃會有個TEMP TABLE TRANSFORMATION的operation，可以叫做臨時錶轉化，看一個具體的例子，使用hint：/*+ materialize */來強制讓oracle建立臨時表。

SQL> with temp as

  2  (

  3  select /*+ materialize */

  4  a.* from scott.emp a,scott.dept b

  5  where a.deptno = b.deptno

  6  and a.empno = 7369

  7  )

  8  select count(*) from temp a ;

 

  COUNT(*)

----------

         1

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 555904337

 

--------------------------------------------------------------------------------

-----------------------------

 

| Id  | Operation                     | Name                        | Rows  | By

tes | Cost (%CPU)| Time     |

 

--------------------------------------------------------------------------------

-----------------------------

 

|   0 | SELECT STATEMENT              |                             |     1 |

    |     3   (0)| 00:00:01 |

 

|   1 |  TEMP TABLE TRANSFORMATION    |                             |       |

    |            |          |

 

|   2 |   LOAD AS SELECT              |                             |       |

    |            |          |

 

|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP                         |     1 |

 39 |     1   (0)| 00:00:01 |

 

|*  4 |     INDEX UNIQUE SCAN         | PK_EMP                      |     1 |

    |     0   (0)| 00:00:01 |

 

|   5 |   SORT AGGREGATE              |                             |     1 |

    |            |          |

 

|   6 |    VIEW                       |                             |     1 |

    |     2   (0)| 00:00:01 |

 

|   7 |     TABLE ACCESS FULL         | SYS_TEMP_0FD9D6613_A0A5C4A5 |     1 |

 39 |     2   (0)| 00:00:01 |

 

--------------------------------------------------------------------------------

-----------------------------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - filter("A"."DEPTNO" IS NOT NULL)

   4 - access("A"."EMPNO"=7369)

 

 

Statistics

----------------------------------------------------------

        222  recursive calls

         10  db block gets

         30  consistent gets

          1  physical reads

       1652  redo size

        411  bytes sent via SQL*Net to client

        400  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL>

可以找到系統建立這個臨時表的sql：

SQL> select sql_text from v$sqltext a where a.SQL_ID = 'aamnajt3sq3zt' order by a.PIECE;

 

SQL_TEXT

----------------------------------------------------------------

CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D660E_A0A5C4A5

" ("C0" NUMBER(4),"C1" VARCHAR2(10),"C2" VARCHAR2(9),"C3" NUMBER

(4),"C4" DATE,"C5" NUMBER(10,2),"C6" NUMBER(10,2),"C7" NUMBER(2)

 ) IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 425

4950926 ) NOPARALLEL

這裡可以看到這個系統臨時表存放在記憶體中，使用的是create global temporary語句建立。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-671190/，如需轉載，請註明出處，否則將追究法律責任。