Переписать код SQL в PL/SQL, используя коллекцию BULK

Question

Я в начале изучения того, как оптимизировать PL/SQL-запросы. Итак, в моей процедуре есть два FOR LOOPS и SQL как INSERT заявления. И я хочу переписать его, используя BULK коллекции.

CURSOR cur0 IS
    SELECT cal.id, cal.dt, cal.id_period, per.period_name
    FROM xxkkbi_dim_calendar cal, xxkkbi_dim_period per
    WHERE (cal.dt BETWEEN p_start_date AND p_end_date)
    AND per.id = cal.id_period
    ORDER BY cal.dt;

row0          cur0%ROWTYPE;
l_period_name VARCHAR2(20);
l_id_period   NUMBER;
l_id_date     NUMBER;
l_id_budget   NUMBER;
l_budget_code VARCHAR2(2 CHAR);

BEGIN

FOR row0 IN cur0 LOOP
    l_date        := row0.dt;
    l_period_name := row0.period_name;
    l_id_period   := row0.id_period;
    l_id_date     := row0.id;

    IF TO_CHAR(row0.dt, 'dd.mm') = '01.01' THEN
        l_period_name := 'КОР-' || SUBSTR(TO_NUMBER(TO_CHAR(row0.dt, 'YYYY')) - 1, 3);
    END IF;

    FOR row2 IN 
        (SELECT id id_budget, code
        FROM xxkkbi_dim_budget_type
        WHERE id > -1
        AND marker_127 = 'N') 
    LOOP
        l_id_budget   := row2.id_budget;
        l_budget_code := row2.code;

        DELETE FROM fct_5_52
        WHERE id_date = l_id_date
        AND id_budget_type = l_id_budget
        AND id_period = l_id_period;

        INSERT INTO fct_5_52
            (id_date,
            id_period,
            id_gu,
            id_region,
            id_budget_type,
            id_financing_source,
            id_expense_fk,
            id_specific,
            amount_period,
            amount_year)
        SELECT 
            l_id_date AS id_date,
            l_id_period,
            (SELECT gu.id
                FROM xxkkbi_dim_gu gu
                WHERE gu.code = cc.segment6) id_gu,
            (SELECT region.id
                FROM xxkkbi_dim_region region
                WHERE region.code = cc.segment2) id_region,
            (SELECT budget.id
                FROM xxkkbi_dim_budget_type budget
                WHERE budget.code = cc.segment1) id_budget_type,
            (SELECT dfs.id
                FROM xxkkbi_dim_financing_source dfs
                WHERE dfs.code = cc.segment7) id_financing_source,
            (SELECT kbk.id
                FROM xxkkbi_dim_expense_fk kbk
                WHERE kbk.code = cc.segment3) id_expense_fk,
            (SELECT ds.id
                FROM xxkkbi_dim_specific ds
                WHERE ds.code = cc.segment4) id_specific,
            SUM(NVL(jljh.entered, 0)) circle,
            SUM(NVL(bal.begin_balance_dr, 0) - NVL(bal.begin_balance_cr, 0) + NVL(jljh.entered, 0)) saldo
        FROM apps_gl_balances bal
        JOIN apps_gl_code_combinations cc 
        ON (bal.code_combination_id = cc.code_combination_id 
            AND bal.currency_code = 'KZT' 
            AND bal.actual_flag = 'A' 
            AND bal.ledger_id = 2021 
            AND NVL(bal.translated_flag, 'x') IN ('Y', 'N', 'x') 
            AND cc.chart_of_accounts_id = 50408 
            AND cc.template_id IS NULL 
            AND segment5 = '0' 
            AND segment1 between '01' and '03' 
            AND segment3 between '100000000' and '999999999' 
            AND segment4 BETWEEN '000111' AND'000999' 
            AND bal.period_name = l_period_name 
            AND (
                    (NVL(bal.period_net_dr, 0) - NVL(bal.period_net_cr, 0)) != 0 OR
                    (NVL(bal.begin_balance_dr, 0) - NVL(bal.begin_balance_cr, 0)) != 0
                )
            )
        LEFT OUTER JOIN 
            (SELECT 
                jl.code_combination_id,
                SUM(NVL(jl.entered_dr, 0) - NVL(jl.entered_cr, 0)) entered
            FROM 
                gl_gl_je_lines jl, 
                gl_gl_je_headers jh
            WHERE jl.period_name = l_period_name
            AND jl.effective_date <= l_date
            AND jh.je_header_id = jl.je_header_id
            AND jh.period_name = jl.period_name
            AND jh.currency_code = 'KZT'
            AND jh.ledger_id = 2021
            AND jh.actual_flag = 'A'
            GROUP BY jl.code_combination_id) jljh 
        ON 
            (jljh.code_combination_id = cc.code_combination_id)
        GROUP BY 
            cc.segment1,
            cc.segment2,
            cc.segment3,
            cc.segment4,
            cc.segment6,
            cc.segment7;
    COMMIT;
    END LOOP;
END LOOP;

И я знаю, что мне нужно изменить PL/SQL NVL функция к SQL COALESCE функция для уменьшения контекста переключения.

Answer 1

Ваш код может быть выполнен без циклов, в одиночных SQL-запросах - один INSERT и один DELETE, Если твой INSERT оператор работает с большим количеством данных, вы можете разделить ваши данные на порции и сделать несколько меньше INSERTs - насколько я понимаю, это именно то, что вы сделали. Но использование коллекций здесь не поможет. Запросы с коллекциями могут быть такими же эффективными, как запросы чистого SQL, но не более. Если у вас есть проблемы с производительностью, вам нужно искать проблемы в другом месте. Попробуйте оптимизировать свой SELECT подзапросы. Я думаю, что проблемы здесь.

Также вам нужно измерить время на выполнение одного цикла. Насколько большой это? Если у вас много (сотни или тысячи) быстрых циклов (менее 1 секунды на один цикл), попробуйте изменить размер ваших частей данных.

Также, NVL - это функция SQL ( http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions001.htm), она не должна переключать контекст (насколько я знаю).