oracle中避免全局临时表的方法 [英] ways to avoid global temp tables in oracle

问题描述

我们刚刚将sql server存储过程转换为oracle过程. Sql Server SP高度依赖于会话表(INSERT INTO #table1...)，这些表已转换为oracle中的全局临时表.我们最终为我们的400 SP赢得了500 GTT.

We just converted our sql server stored procedures to oracle procedures. Sql Server SP's were highly dependent on session tables (INSERT INTO #table1...) these tables got converted as global temporary tables in oracle. We ended up with aroun 500 GTT's for our 400 SP's

现在，我们发现由于性能和其他问题，在oracle中使用GTT被认为是最后的选择.

Now we are finding out that working with GTT's in oracle is considered a last option because of performance and other issues.

还有哪些其他选择?收藏吗?游标?

what other alternatives are there? Collections? Cursors?

我们对GTT的典型用法如下:

Our typical use of GTT's is like so:

插入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中获取数据.这些只是示例查询，实际上，查询确实非常复杂，包含许多联接和子查询.

and later on get the data out of the GTT. These are just sample queries, in actuality the queries are really complext with lot of joins and subqueries.

我有一个三部分的问题:

I have a three part question:

1. 有人可以展示如何进行变革 上面的示例查询到 集合和/或游标?
2. 以来 使用GTT，您可以在本地工作 与SQL ...为什么要远离 GTT?他们真的那么糟糕吗?
3. 应遵循的准则是什么 何时使用和何时避免使用GTT

1. Can someone show how to transform the above sample queries to collections and/or cursors?
2. Since with GTT's you can work natively with SQL...why go away from the GTTs? are they really that bad.
3. What should be the guidelines on When to use and When to avoid GTT's

推荐答案

让我们首先回答第二个问题:

为什么要离开GTT? 真的那么糟糕."

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

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

A couple of days ago I was knocking up a proof of concept which loaded a largish XML file (~18MB) into an XMLType. Because I didn't want to store the XMLType permanently I tried loading it into a PL/SQL variable (session memory) and a temporary table. Loading it into a temporary table took five times as long as loading it into an XMLType variable (5 seconds compared to 1 second). The difference is because temporary tables are not memory structures: they are written to disk (specifically your nominated temporary tablespace).

如果要缓存大量数据，则将其存储在内存中会给PGA造成压力，如果您有大量会话，则不好.因此，这需要在RAM和时间之间进行权衡.

If you want to cache a lot of data then storing it in memory will stress the PGA, which is not good if you have lots of sessions. So it's a trade-off between RAM and time.

"有人可以展示如何改变 以上示例查询集合 和/或光标?"

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

您发布的查询可以合并为一条语句:

The queries you post can be merged into a single statement:

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()语句可以用更简洁的nvl2(trim(a.column_a), a.column_a, b.data_a)代替).

(I have simply transposed your logic but that case() statement could be replaced with a neater nvl2(trim(a.column_a), a.column_a, b.data_a) ).

我知道您说您的查询更为复杂，但是您的第一个拨打电话应该是考虑重写它们.我知道将一个陈旧的查询分解为许多与PL/SQL结合在一起的婴儿SQL是多么诱人，但是纯SQL效率更高.

I know you say your queries are more complicated but your first port of call should be to consider rewriting them. I know how seductive it is to break a gnarly query into lots of baby SQLs stitched together with PL/SQL but pure SQL is way more efficient.

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

To use a collection it is best to define the types in SQL, because it gives us the flexibility to use them in SQL statements as well as 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;
/

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

Here's a sample function, which returns a result set:

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表类型时，这不是必需的:

In the function it is necessary to instantiate the type with the columns, in order to avoid the ORA-00947 exception. This is not necessary when populating a PL/SQL table type:

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> 

最后，准则

何时应遵循的准则是什么 使用和何时避免使用GTT"

"What should be the guidelines on When to use and When to avoid GTT's"

当我们需要在同一会话中的不同程序单元之间共享缓存的数据时，全局临时表非常有用.例如，如果我们有一个通用的报表结构，该报表结构是由一个函数提供的，该函数以GTT的形式提供，该GTT由多个过程之一填充. (尽管即使这样也可以使用动态ref游标实现...)

Global temp tables are very good when we need share cached data between different program units in the same session. For instance if we have a generic report structure generated by a single function feeding off a GTT which is populated by one of several procedures. (Although even that could also be implemented with dynamic ref cursors ...)

如果我们有很多中间处理，而这些中间处理太复杂而无法用单个SQL查询来解决，那么全局临时表也很好.尤其是如果必须将该处理应用于检索到的行的子集.

Global temporary tables are also good if we have a lot of intermediate processing which is just too complicated to be solved with a single SQL query. Especially if that processing must be applied to subsets of the retrieved rows.

但是，通常应该假定我们不需要使用临时表.所以

But in general the presumption should be that we don't need to use a temporary table. So

1. 在SQL中执行此操作，除非在任何情况下都很难...
2. ...在PL/SQL变量(通常是集合)中执行此操作，除非在这种情况下会占用过多的内存...
3. ...使用全局临时表来完成

这篇关于oracle中避免全局临时表的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！