【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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16179598/viewspace-671190/