Hello I have a procedure and questions about it. This procedure is used for extracting data then inserting them into one table. I realized that we dont use loop while in FORALL syntax. So my purpose is that extracting 10.000 records then inserting these 10.000 records into a single table. What I want to know is that, the procedure just stops after inserting 10.000 records or continues its' work? I am asking because there is no loop inside FORALL? My second question is that should I remove v_cnt and IF MOD (v_cnt, 10000) = 0 lines in the script?
I am adding scripts below, I am open to any fix advices or anything else thank you from now.
create or replace procedure GPU_DATA_EXTRACTOR_TEST(pid_billdate DATE) is
v_cnt NUMBER;
c_limit CONSTANT PLS_INTEGER DEFAULT 10000;
CURSOR c1 IS
SELECT DISTINCT intl_prod_id
FROM apld_bill_rt abr,
acct_bill ab
WHERE abr.CHRG_TP = 'INSTALLMENT'
AND abr.TAX_CATG_ID = 'NOTAX'
AND abr.acct_bill_id = ab.acct_bill_id
AND ab.bill_date = pid_billdate;
TYPE prod_ids_t IS TABLE OF apld_bill_rt.intl_prod_id%TYPE INDEX BY PLS_INTEGER;
l_prod_ids prod_ids_t;
begin
execute immediate 'truncate table GPU_INV_TEST';
v_cnt := 0;
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO l_prod_ids LIMIT c_limit;
EXIT WHEN l_prod_ids.COUNT = 0;
FORALL indx IN 1 .. l_prod_ids.COUNT
INSERT INTO GPU_INV_TEST
SELECT AB.ACCT_BILL_ID,
AB.BILL_NO,
AB.INV_ID,
AB.BILL_DATE,
ba2.bill_acct_id,
ba1.bill_acct_id parent_bill_acct_id,
AB.DUE_DATE,
PG.CMPG_ID,
ABR.NET_AMT,
AB.DUE_AMT,
P.PROD_NUM,
pds.DST_ID,
ABR.DESCR,
p.intl_prod_id
FROM apld_bill_rt abr,
acct_bill ab,
prod p,
FCBSADM.PROD_DST pds,
bill_acct_prod bap,
bill_acct ba1,
bill_acct ba2,
prod_cmpg pg
WHERE ab.intl_bill_acct_id = ba1.intl_bill_acct_id
AND AB.ACCT_BILL_ID = ABR.ACCT_BILL_ID
AND ba1.intl_bill_acct_id = ba2.parent_bill_acct_id
AND ba2.intl_bill_acct_id = bap.intl_bill_acct_id
AND bap.intl_prod_id = abr.intl_prod_id
AND ABR.CHRG_TP = 'INSTALLMENT'
AND bap.intl_prod_id = pds.intl_prod_id
AND bap.intl_prod_id = p.intl_prod_id
AND p.intl_prod_id = pg.intl_prod_id( )
AND ABR.intl_prod_id = l_prod_ids(indx)
UNION
SELECT AB.ACCT_BILL_ID,
AB.BILL_NO,
AB.INV_ID,
AB.BILL_DATE,
ba1.bill_acct_id,
ba1.bill_acct_id parent_bill_acct_id,
AB.DUE_DATE,
PG.CMPG_ID,
ABR.NET_AMT,
AB.DUE_AMT,
P.PROD_NUM,
pds.DST_ID,
ABR.DESCR,
p.intl_prod_id
FROM apld_bill_rt abr,
acct_bill ab,
prod p,
FCBSADM.PROD_DST pds,
bill_acct_prod bap,
bill_acct ba1,
prod_cmpg pg
WHERE ab.intl_bill_acct_id = ba1.intl_bill_acct_id
AND AB.ACCT_BILL_ID = ABR.ACCT_BILL_ID
--AND ba1.intl_bill_acct_id = ba2.parent_bill_acct_id
AND ba1.intl_bill_acct_id = bap.intl_bill_acct_id
AND bap.intl_prod_id = abr.intl_prod_id
AND ABR.CHRG_TP = 'INSTALLMENT'
AND bap.intl_prod_id = pds.intl_prod_id
AND bap.intl_prod_id = p.intl_prod_id
AND p.intl_prod_id = pg.intl_prod_id( )
AND ABR.intl_prod_id = l_prod_ids(indx);
v_cnt := v_cnt 1;
IF MOD (v_cnt, 10000) = 0
THEN
COMMIT;
END IF;
COMMIT;
END LOOP;
CLOSE c1;
end;
CodePudding user response：
Your procedure treats everything, it just works in bulks of 10000 records, thus prone to parallelism.
There is a loop in the forall, the insert will be executed for all the records (10K)
Regarding the v_cnt mod, you can remove it (but not advised to) with no issues other than performances, the original author most likely found that the redo segments might get really big, so he's just commiting everywhile and so (but since he truncates the table in the beginning, this isn't an issue at all).
CodePudding user response：
You don't need to do any bulk collect in that procedure. It is non sense. Why ? Because you are not collecting values in the type and applying logic over them, you just get the distinct intl_prod_id in a cursor and make a select for each one, something you can easily solve with a CTE expression.
If you use also direct path, by applying the hint append, then you will get a much better procedure and huge boost in performance.
Keep in mind that for each row in the cursor, you need to run the insert/select, that implies to join all the tables involved in the operation for each value in the cursor. Try to apply the principle, the easier the better.
create or replace procedure GPU_DATA_EXTRACTOR_TEST(pid_billdate DATE) is
begin
execute immediate 'truncate table GPU_INV_TEST';
INSERT /* append */ INTO GPU_INV_TEST
with x as
( SELECT /* materialize */ DISTINCT intl_prod_id as intl_prod_id
FROM apld_bill_rt abr,
acct_bill ab
WHERE abr.CHRG_TP = 'INSTALLMENT'
AND abr.TAX_CATG_ID = 'NOTAX'
AND abr.acct_bill_id = ab.acct_bill_id
AND ab.bill_date = pid_billdate
)
SELECT AB.ACCT_BILL_ID,
AB.BILL_NO,
AB.INV_ID,
AB.BILL_DATE,
ba2.bill_acct_id,
ba1.bill_acct_id parent_bill_acct_id,
AB.DUE_DATE,
PG.CMPG_ID,
ABR.NET_AMT,
AB.DUE_AMT,
P.PROD_NUM,
pds.DST_ID,
ABR.DESCR,
p.intl_prod_id
FROM apld_bill_rt abr,
acct_bill ab,
prod p,
FCBSADM.PROD_DST pds,
bill_acct_prod bap,
bill_acct ba1,
bill_acct ba2,
prod_cmpg pg ,
x
WHERE ab.intl_bill_acct_id = ba1.intl_bill_acct_id
AND AB.ACCT_BILL_ID = ABR.ACCT_BILL_ID
AND ba1.intl_bill_acct_id = ba2.parent_bill_acct_id
AND ba2.intl_bill_acct_id = bap.intl_bill_acct_id
AND bap.intl_prod_id = abr.intl_prod_id
AND ABR.CHRG_TP = 'INSTALLMENT'
AND bap.intl_prod_id = pds.intl_prod_id
AND bap.intl_prod_id = p.intl_prod_id
AND p.intl_prod_id = pg.intl_prod_id( )
AND ABR.intl_prod_id = x.intl_prod_id
UNION
SELECT AB.ACCT_BILL_ID,
AB.BILL_NO,
AB.INV_ID,
AB.BILL_DATE,
ba1.bill_acct_id,
ba1.bill_acct_id parent_bill_acct_id,
AB.DUE_DATE,
PG.CMPG_ID,
ABR.NET_AMT,
AB.DUE_AMT,
P.PROD_NUM,
pds.DST_ID,
ABR.DESCR,
p.intl_prod_id
FROM apld_bill_rt abr,
acct_bill ab,
prod p,
FCBSADM.PROD_DST pds,
bill_acct_prod bap,
bill_acct ba1,
prod_cmpg pg,
x
WHERE ab.intl_bill_acct_id = ba1.intl_bill_acct_id
AND AB.ACCT_BILL_ID = ABR.ACCT_BILL_ID
--AND ba1.intl_bill_acct_id = ba2.parent_bill_acct_id
AND ba1.intl_bill_acct_id = bap.intl_bill_acct_id
AND bap.intl_prod_id = abr.intl_prod_id
AND ABR.CHRG_TP = 'INSTALLMENT'
AND bap.intl_prod_id = pds.intl_prod_id
AND bap.intl_prod_id = p.intl_prod_id
AND p.intl_prod_id = pg.intl_prod_id( )
AND ABR.intl_prod_id = x.intl_prod_id;
COMMIT;
end;