先回答第二个问题:
"为什么要远离GTTs?它们真的那么糟糕吗?"
几天前,我正在制作一个概念验证,将一个较大的XML文件(约18MB)加载到XMLType中。因为我不想永久存储XMLType,所以我尝试将它加载到PL/SQL变量(会话内存)和临时表中。将其加载到临时表中所需的时间是将其加载到XMLType变量中所需的时间的五倍(5秒对比1秒)。这种差异是因为临时表不是内存结构:它们写入磁盘(特定于您指定的临时表空间)。
如果您想缓存大量数据,则将其存储在内存中会给PGA带来压力,如果有很多会话,则这并不好。因此,这是RAM和时间之间的权衡。
关于第一个问题:
"有人能展示如何将上面的示例查询转换为集合和/或游标吗?"
您发布的查询可以合并为一条语句:
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)
替代。
我知道你说你的查询更复杂,但你首先应该考虑重写它们。将棘手的查询分解成许多由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>
最后,指导方针
"在什么情况下应该使用GTT以及在什么情况下应该避免使用GTT?"
当我们需要在同一会话中的不同程序单元之间共享缓存数据时,全局临时表非常好用。例如,如果我们有一个由单个函数生成的通用报告结构,该函数依赖于一个由多个过程填充的GTT。(尽管这也可以使用动态引用游标实现...)
如果我们有很多中间处理步骤,而这些步骤无法通过单个SQL查询解决,那么全局临时表也很好用。特别是如果该处理必须应用于检索行的子集。
但总的来说,我们应该默认情况下不需要使用临时表。因此:
- 除非太难,否则请使用SQL完成
- 除非占用太多内存,否则请使用PL/SQL变量(通常是集合)
- 如果以上两种方法均不可行,请使用全局临时表