oracle oracle中避免全局临时表的方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2918466/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ways to avoid global temp tables in oracle
提问by Omnipresent
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
我们只是将我们的 sql server 存储过程转换为 oracle 过程。Sql Server SP 高度依赖于会话表 ( INSERT INTO #table1...)，这些表在 oracle 中被转换为全局临时表。我们最终为 400 个 SP 获得了大约 500 个 GTT
Now we are finding out that working with GTT's in oracle is considered a last option because of performance and other issues.
现在我们发现由于性能和其他问题，在 oracle 中使用 GTT 被认为是最后的选择。
what other alternatives are there? Collections? Cursors?
还有哪些其他选择？收藏？光标？
Our typical use of GTT's is like so:
我们对 GTT 的典型用法是这样的：
Insert into 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'));
Update the GTT
更新 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 = ' ';
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.
然后从 GTT 中获取数据。这些只是示例查询，实际上查询非常复杂，有很多连接和子查询。
I have a three part question:
我有一个由三部分组成的问题：
- Can someone show how to transform the above sample queries to collections and/or cursors?
- Since with GTT's you can work natively with SQL...why go away from the GTTs? are they really that bad.
- What should be the guidelines on When to use and When to avoid GTT's
- 有人可以展示如何将上述示例查询转换为集合和/或游标吗？
- 既然有了 GTT，你就可以在本地使用 SQL 了……为什么要远离 GTT？他们真的那么糟糕吗？
- 关于何时使用和何时避免 GTT 的指南应该是什么？
回答by APC
Let's answer the second question first:
我们先回答第二个问题：
"why go away from the GTTs? are they really that bad."
“为什么要远离 GTT？它们真的那么糟糕。”
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).
几天前，我正在敲一个概念证明，它将一个较大的 XML 文件（~18MB）加载到 XMLType 中。因为我不想永久存储 XMLType，所以我尝试将它加载到 PL/SQL 变量（会话内存）和临时表中。将其加载到临时表中所需的时间是将其加载到 XMLType 变量中的时间的五倍（5 秒与 1 秒相比）。不同之处在于临时表不是内存结构：它们被写入磁盘（特别是您指定的临时表空间）。
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.
如果您想缓存大量数据，那么将其存储在内存中会给 PGA 带来压力，如果您有大量会话，这将是不利的。所以这是 RAM 和时间之间的权衡。
To the first question:
对于第一个问题：
"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'));
(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)).
（我只是简单地转换了您的逻辑，但case()可以用更简洁的 替换该语句nvl2(trim(a.column_a), a.column_a, b.data_a)）。
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.
我知道你说你的查询更复杂，但你的第一个停靠港应该是考虑重写它们。我知道将一个粗糙的查询分解成许多用 PL/SQL 拼接在一起的小 SQL 是多么诱人，但纯 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.
要使用集合，最好在 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;
/
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;
/
And here it is in action:
这是在行动：
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>
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:
在函数中需要用列实例化类型，以避免 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>
Finally, guidelines
最后，指导方针
"What should be the guidelines on When to use and When to avoid GTT's"
“关于何时使用和何时避免 GTT 的指南应该是什么”
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 ...)
当我们需要在同一会话中的不同程序单元之间共享缓存数据时，全局临时表非常有用。例如，如果我们有一个由单个函数生成的通用报告结构，该函数由几个过程之一填充的 GTT 馈送。（虽然这也可以用动态引用游标来实现......）
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.
如果我们有很多中间处理，这些处理太复杂而无法用单个 SQL 查询来解决，那么全局临时表也很好。特别是如果该处理必须应用于检索到的行的子集。
But in general the presumption should be that we don't need to use a temporary table. So
但总的来说，假设应该是我们不需要使用临时表。所以
- Do it in SQL unless it is too hard it which case ...
- ... Do it in PL/SQL variables (usually collections) unless it takes too much memory it which case ...
- ... Do it with a Global Temporary Table
- 在 SQL 中执行它，除非它太难了，在这种情况下......
- ... 在 PL/SQL 变量（通常是集合）中执行此操作，除非它占用太多内存，在这种情况下...
- ...用全局临时表来做
回答by Gary Myers
Generally I'd use a PL/SQL collection for storing small volumes of data (maybe a thousand rows). If the data volumes were much larger, I'd use a GTT so that they don't overload the process memory.
通常我会使用 PL/SQL 集合来存储少量数据（可能是一千行）。如果数据量更大，我会使用 GTT，这样它们就不会使进程内存过载。
So I might select a few hundred rows from the database into a PL/SQL collection, then loop through them to do some calculation/delete a few or whatever, then insert that collection into another table.
所以我可能会从数据库中选择几百行到一个 PL/SQL 集合中，然后遍历它们来进行一些计算/删除一些或其他什么，然后将该集合插入到另一个表中。
If I was dealing with hundreds of thousands of rows, I would try to push as much of the 'heavy lifting' processing into large SQL statements. That may or may not require GTT.
如果我正在处理数十万行，我会尝试将尽可能多的“繁重”处理推送到大型 SQL 语句中。这可能需要也可能不需要 GTT。
You can use SQL level collection objects as something that translates quite easily between SQL and PL/SQL
您可以使用 SQL 级别的集合对象作为在 SQL 和 PL/SQL 之间轻松转换的对象
create type typ_car is object (make varchar2(10), model varchar2(20), year number(4));
/
create type typ_coll_car is table of typ_car;
/
select * from table (typ_coll_car(typ_car('a','b',1999), typ_car('A','Z',2000)));
MAKE MODEL YEAR
---------- -------------------- ---------------
a b 1,999.00
A Z 2,000.00
declare
v_car1 typ_car := typ_car('a','b',1999);
v_car2 typ_car := typ_car('A','Z',2000);
t_car typ_coll_car := typ_coll_car();
begin
t_car := typ_coll_car(v_car1, v_car2);
FOR i in (SELECT * from table(t_car)) LOOP
dbms_output.put_line(i.year);
END LOOP;
end;
/
