sql - 优化读取多表的SQL函数(去掉UNION ALL)

Question

我们在 SQL (postgresql) 中有一个函数需要从多个周表中读取单位(最多单位数和最大周数)。

单位在几个表(类似)中，在另一个表中定义。每张 table 有 > 22,000,000 个单位。

在某些情况下，我们需要搜索多个表以获得足够的单位来满足我们的需要，但在其他一些情况下，我们可能只需要一个。

基本上，我们需要给定商店最近几周的大量样本。

我们目前有:

CREATE OR REPLACE FUNCTION get_units(_cursor REFCURSOR, 
                                    _obs_time timestamp without time zone, 
                                    _unit_store integer, _unit_limit integer, 
                                    _week_limit integer) RETURNS REFCURSOR
    LANGUAGE plpgsql
    AS $BODY$
DECLARE
    _week_cursor REFCURSOR; 
    _table_name TEXT;
    _query_sql TEXT;
    _command TEXT := '(SELECT ''0001-01-01'' AS obs_time,
                              0::smallint detail
                       WHERE FALSE)';
    _week_count INTEGER;
    _result_count INTEGER := 0;
    _current_unit_limit INTEGER;
BEGIN

    OPEN _week_cursor FOR
        SELECT table_name 
          FROM week_table 
         WHERE create_time < _obs_time 
      ORDER BY create_time desc
         LIMIT _week_limit;

    _current_unit_limit := _unit_limit;  

    LOOP
        FETCH _week_cursor INTO _table_name;
        EXIT WHEN NOT FOUND;

        _query_sql := 
         'FROM ' || _table_name || ' u
         WHERE u.unit_store = ' || _unit_store || ' ';

        EXECUTE 'SELECT count(*) ' || _query_sql || ' LIMIT ' || _current_unit_limit INTO _week_count;

        _result_count := _result_count + _week_count;
        _current_unit_limit := _unit_limit - _result_count;

        IF _week_count > 0 THEN
            _command := _command || ' UNION ALL 
                        (SELECT u.obs_time obs_time,
                             u.detail detail '
                         || _query_sql
                         || ' ORDER BY u.obs_time DESC'
                         || ' LIMIT ' || _week_count || ')';
        END IF;
        IF (_result_count >= _unit_limit) THEN
            EXIT;
        END IF;
    END LOOP;
    CLOSE _week_cursor;
    OPEN _cursor FOR EXECUTE _command;

    RETURN _cursor;
END;
$BODY$;

在大多数情况下，结果都不错，但在一些最坏的情况下(几个星期，每次只有几条记录)，我们的速度会变慢。

任何建议可以改进什么以及如何改进？例如，删除 UNION ALL 临时表？会产生重大变化的东西。

Answer 1

我根据我的评论设置了一个例子

declare
   type tt is table of t1%rowtype;
   tt_arr_temp tt;
   tt_arr tt := tt();
   c sys_refcursor;
   cursor c_tables is 
    select  table_name 
    from    user_tables 
    where   table_name in ('T1','T2');
begin
    for x in c_tables loop
        open c for 'select * from '||x.table_name;
        fetch c bulk collect into tt_arr_temp limit 3;
        close c;
        -- union collections
        tt_arr :=  tt_arr multiset union tt_arr_temp;
    end loop;

    -- print -- just for debug
    for i in tt_arr.first .. tt_arr.last loop
        dbms_output.put_line(tt_arr(i).id || ' , ' || tt_arr(i).name);
    end loop;
exception when others then 
    close c;
end;
/

结果将在 tt_arr 中。你可以用它做任何你想做的事。

这是我的输出

SQL> select * from t1;

        ID N
---------- -
         1 A
         2 B
         3 C
         4 D
         5 E
         6 F

6 rows selected.

SQL> select * from t2;

        ID N
---------- -
        20 B
        30 C

SQL>
SQL> declare
  2     type tt is table of t1%rowtype;
....
 25  end;
 26  /
1 , A
2 , B
3 , C
20 , B
30 , C

PL/SQL procedure successfully completed.