避免oracle全局临时表的方法

现在我们发现，由于性能和其他问题，与GTT在oracle中的合作被认为是最后一个选择。

还有什么其他选择？收藏？光标？

我们对GTT的典型使用就是这样：

插入GTT

INSERT INTO some_gtt_1
  (column_a,column_b,column_c)
  (SELECT someA,someB,someC
     FROM TABLE_A
    WHERE condition_1 = 'YN756'
      AND type_cd = 'P'
      AND TO_NUMBER(TO_CHAR(m_date,'MM')) = '12'
      AND (lname LIKE (v_LnameUpper || '%') OR
      lname LIKE (v_searchLnameLower || '%'))
      AND (e_flag = 'Y' OR
      it_flag = 'Y' OR
      fit_flag = 'Y'));

更新GTT

UPDATE some_gtt_1 a
SET column_a = (SELECT b.data_a FROM some_table_b b 
               WHERE a.column_b = b.data_b AND a.column_c = 'C')
WHERE column_a IS NULL OR column_a = ' ';

然后将数据从GTT中取出。这些只是示例查询，实际上查询真的很复杂，很多连接和子查询。

我有三个部分的问题：

有人可以显示如何转换
上面的示例查询
集合和/或游标？
>自从
与GTT的你可以在本地工作
与SQL …为什么要离开
GTTs？他们真的那么糟糕吗
>应该是什么指导
何时使用和何时避免GTT的

“why go away from the GTTs? are they
really that bad.”

几天前，我正在敲出一个概念证明，将一个大量的XML文件(?18MB)加载到XMLType中。因为我不想永久存储XMLType，所以我尝试将其加载到PL / SQL变量(会话内存)和临时表中。将其加载到临时表中，将其加载到XMLType变量(5秒，相对于1秒)为止的五倍。区别是因为临时表不是内存结构：它们被写入磁盘(特别是你指定的临时表空间)。

如果你想缓存大量的数据，那么将其存储在内存中就会强调PGA，如果你有很多会话，这不是很好。所以这是RAM和时间之间的权衡。

第一个问题：

“Can someone show how to transform the
above sample queries to collections
and/or cursors?”

您发布的查询可以合并到单个语句中：

SELECT case when a.column_a IS NULL OR a.column_a = ' ' 
           then b.data_a
           else  column_a end AS someA,a.someB,a.someC
FROM TABLE_A a
      left outer join TABLE_B b
          on ( a.column_b = b.data_b AND a.column_c = 'C' )
WHERE condition_1 = 'YN756'
  AND type_cd = 'P'
  AND TO_NUMBER(TO_CHAR(m_date,'MM')) = '12'
  AND (lname LIKE (v_LnameUpper || '%') OR
  lname LIKE (v_searchLnameLower || '%'))
  AND (e_flag = 'Y' OR
  it_flag = 'Y' OR
  fit_flag = 'Y'));

(我已经简单地转置了你的逻辑，但是这个case()语句可以用一个neater nvl2(trim(a.column_a)，a.column_a，b.data_a)来代替)。

我知道你说你的查询更复杂，但是你的第一个呼叫端口应该考虑重写它们。我知道如何将一个粗略的查询打破大量与PL / SQL拼接在一起的宝宝SQL，但纯SQL的效率更高。

要使用集合，最好在SQL中定义类型，因为它使我们能够灵活地在SQL语句和PL / SQL中使用它们。

create or replace type tab_a_row as object
    (col_a number,col_b varchar2(23),col_c date);
/
create or replace type tab_a_nt as table of tab_a_row;
/

这是一个示例函数，它返回一个结果集：

create or replace function get_table_a 
      (p_arg in number) 
      return sys_refcursor 
is 
    tab_a_recs tab_a_nt; 
    rv sys_refcursor; 
begin 
    select tab_a_row(col_a,col_b,col_c)  
    bulk collect into tab_a_recs 
    from table_a 
    where col_a = p_arg; 

    for i in tab_a_recs.first()..tab_a_recs.last() 
    loop 
        if tab_a_recs(i).col_b is null 
        then 
            tab_a_recs(i).col_b :=  'something'; 
        end if; 
    end loop;  

    open rv for select * from table(tab_a_recs); 
    return rv; 
end; 
/

在这里它是在行动：

SQL> select * from table_a
  2  /

     COL_A COL_B                   COL_C
---------- ----------------------- ---------
         1 whatever                13-JUN-10
         1                         12-JUN-10

SQL> var rc refcursor
SQL> exec :rc := get_table_a(1)

PL/SQL procedure successfully completed.

SQL> print rc

     COL_A COL_B                   COL_C
---------- ----------------------- ---------
         1 whatever                13-JUN-10
         1 something               12-JUN-10

SQL>

在函数中，有必要使用列实例化类型，以避免ORA-00947异常。填充PL / SQL表类型时，这不是必需的：

SQL> create or replace procedure pop_table_a
  2        (p_arg in number)
  3  is
  4      type table_a_nt is table of table_a%rowtype;
  5      tab_a_recs table_a_nt;
  6  begin
  7      select *
  8      bulk collect into tab_a_recs
  9      from table_a
 10      where col_a = p_arg;
 11  end;
 12  /

Procedure created.

SQL>

最后指导

“What should be the guidelines on When
to use and When to avoid GTT’s”

当我们需要在同一个会话中不同的程序单元之间共享缓存数据时，全局临时表非常好。例如，如果我们有一个单个函数生成的通用报表结构，排除由几个过程之一填充的GTT。 (尽管即使这样也可以用动态参考光标来实现…)

如果我们有很多中间处理，那么这个全局临时表也是很好的，这个中间处理太复杂了，不能用单个的SQL查询来解决。特别是如果该处理必须应用于检索行的子集。

但总的来说，推定应该是我们不需要使用临时表。所以

>在SQL中执行，除非它太难了，哪种情况…> …在PL / SQL变量(通常是集合)中执行它，除非它需要太多的内存，哪种情况…> …使用全局临时表

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!