Oracle 临时表用法的比较

临时表是复杂SQL性能优化中常见手段，总结一下。

   

1） Global Temporary 句  
  分为会话级和事务级两种。

   

•   会话级的临时表

    【语法】

          Create Global Temporary Table Table_Name   
          (the aggregation SQL statement) On Commit Preserve Rows；    

    【特点】

               临时表数据自动清空后，但是临时表的结构以及元数据还存储在用户的数据字典中。表的定义对所有的会话可见。
               临时表不需要DML锁。
               可以索引临时表和在临时表基础上建立视图。
               在临时表上的索引也是临时的，也是只对当前会话或者事务有效。
               临时表可以拥有触发器。
               可以用export和import工具导入导出临时表的定义，但是不能导出数据。
   【适用场合】 

              当某一个SQL语句多表关联，并且和一些小表关联。可以采用将大表进行分拆并且得到比较小的结果集合存放在临时表中。
              程序执行过程中多次使用的临时数据，这些数据在整个程序的会话过程中都需要用的等等。
    【清除时点】 

               当某一个SESSION退出之后自动清除数据（表结构保留）
    【不足】 

               不支持lob对象，这也许是设计者基于运行效率的考虑，但实际应用中确实需要此功能时就无法使用临时表了。

               不支持主外键关系。
     
    例子： 
      CREATE GLOBAL TEMPORARY TABLE my_temporary
       (birthdate DATE,
        enddate   DATE,
        name CHAR(20)) ON COMMIT PRESERVE ROWS;

 

•   事务级的临时表    

    【语法】

                Create Global Temporary Table Table_Name    
                   (the aggregation SQL statement) On Commit Delete Rows；  
    【清除时点】  当执行COMMIT之后自动清除数据（表结构保留）

     其他方面与会话级的临时表相同，不再列出。

 

2） with句

      准确的说with语句不叫临时表，但它与临时表使用概念上非常接近，同样列出。

     【语法】

                WITH  
                     subquery_name  
                  AS  
                    (the aggregation SQL statement) 
                  SELECT  
                    (query naming subquery_name);

    【特点】 

               附着于一个SQL 语句，也只对一个SQL语句产生作用。
               可以同时生成几张临时表。
               生存期极短，易于管理。
    【适用场合】 

                Global Temporary 的适用场合都适用，只是针对一个SQL的场合
                特别方便融入常量或集合运算结果，有简化SQL的作用。
    【清除时点】

                查询完成后立即清除（数据及表结构）。

    例子： 
      WITH
      sum_sales AS
        ( select /*+ materialize */
          sum(quantity) all_sales from stores ),
      number_stores AS
        ( select /*+ materialize */
          count(*) nbr_stores from stores ),
      sales_by_store AS
        ( select /*+ materialize */
        store_name, sum(quantity) store_sales from
        store natural join sales )
      SELECT
         store_name
      FROM
         store,
         sum_sales,
         number_stores,
         sales_by_store
      where
         store_sales > (all_sales / nbr_stores)
   

    (注：使用/*+ materialize */让Oracle基于cost-based（基于成本）的优化策略生成临时表。  )