Mejorar procedimiento
Necesito mejorar la performance del siguiente procedimiento... pero no consigo reducir el tiempo significativamente.... alguna sugerencia?
Muchas gracias
PROCEDURE LOAD
IS
nimporte NUMBER;
ncuentafilas NUMBER;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE WL_AMOUNTS_MONTH';
ncuentafilas := 0;
FOR reg IN (SELECT cod_account, cod_variant, cod_period, YEAR, MONTH,
cod_phase, cod_client, cod_network, cod_country,
amount
FROM table)
LOOP
CASE
WHEN reg.MONTH = 1 OR reg.cod_account = 676
THEN
nimporte := reg.amount;
ELSE
BEGIN
SELECT reg.amount - amount
INTO nimporte
FROM table
WHERE cod_account = reg.cod_account
AND cod_variant = reg.cod_variant
AND cod_period = reg.cod_period
AND cod_phase = reg.cod_phase
AND MONTH = reg.MONTH - 1
AND YEAR = reg.YEAR;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
nimporte := reg.amount;
END;
END CASE;
INSERT INTO table_year
(cod_account, cod_variant, cod_period,
YEAR, MONTH, cod_phase, cod_client,
cod_network, cod_country, amount
)
VALUES (reg.cod_account, reg.cod_variant, reg.cod_period,
reg.YEAR, reg.MONTH, reg.cod_phase, reg.cod_client,
reg.cod_network, reg.cod_country, nimporte
);
IF ncuentafilas > 50000
THEN
COMMIT;
ncuentafilas := 0;
ELSE
ncuentafilas := ncuentafilas + 1;
END IF;
END LOOP;
COMMIT;
END LOAD;
Muchas gracias
PROCEDURE LOAD
IS
nimporte NUMBER;
ncuentafilas NUMBER;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE WL_AMOUNTS_MONTH';
ncuentafilas := 0;
FOR reg IN (SELECT cod_account, cod_variant, cod_period, YEAR, MONTH,
cod_phase, cod_client, cod_network, cod_country,
amount
FROM table)
LOOP
CASE
WHEN reg.MONTH = 1 OR reg.cod_account = 676
THEN
nimporte := reg.amount;
ELSE
BEGIN
SELECT reg.amount - amount
INTO nimporte
FROM table
WHERE cod_account = reg.cod_account
AND cod_variant = reg.cod_variant
AND cod_period = reg.cod_period
AND cod_phase = reg.cod_phase
AND MONTH = reg.MONTH - 1
AND YEAR = reg.YEAR;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
nimporte := reg.amount;
END;
END CASE;
INSERT INTO table_year
(cod_account, cod_variant, cod_period,
YEAR, MONTH, cod_phase, cod_client,
cod_network, cod_country, amount
)
VALUES (reg.cod_account, reg.cod_variant, reg.cod_period,
reg.YEAR, reg.MONTH, reg.cod_phase, reg.cod_client,
reg.cod_network, reg.cod_country, nimporte
);
IF ncuentafilas > 50000
THEN
COMMIT;
ncuentafilas := 0;
ELSE
ncuentafilas := ncuentafilas + 1;
END IF;
END LOOP;
COMMIT;
END LOAD;