Hi, I have a script inserting data into a table.
I have 49 millions of records to upload into a table reading from external table (based on a csv file).
If I use cursor with Bulk Collect, I have to set the “Limit” (here 5 millions). Now, if I decide to create a loop to cover the 49 millions records, I think I have to add “order by” to the query, in order to avoid fetching same rows again multiple times.
I don't think is good idea to add “order by” to 49 millions records?
If I don't add additional loop, then I have no choice then to run this script manually multiple times, which implies that I must
add the following condition to the query, to exclude the previously inserted rows. That is very bad for performance as well.
“where not exists (select ani from ANI_BY_TELCO_OPERATORS_PP pp where pp.ani = ''55''||br.subscript_version_tn)”
Any advice how to do it, without affecting the performance, and without using too much space in tablespace?
DB version is:
The Script:
DECLARE
TYPE cur_report IS REF CURSOR;
c_cursor cur_report;
l_query varchar2(1000);
l_oper_id telco_operators_preprod.te_oper_id%TYPE;
TYPE tab_rec IS RECORD (
l_country_iso_code telco_operators_preprod.country_iso_code%TYPE,
l_ani ani_by_telco_operators_prep.ani%TYPE,
l_current_cic telco_operators_preprod.cic%TYPE,
l_prev_carrier_id ani_by_telco_operators_prep.prev_te_oper_id%TYPE,
l_transformation_id ani_by_telco_operators_prep.transformation_id%TYPE,
l_status char(1),
l_action_date date,
l_oper_id varchar2(255)
);
TYPE t_Strings IS TABLE OF tab_rec
INDEX BY binary_integer;
l_Strings t_Strings;
BEGIN
l_query := 'select ''BR'' as country_iso_code
,''55''||subscript_version_tn as ani
, Subscript_rn1 as current_cic
, NULL prev_carrier_id
, version_id as transformation_id
, ''A'' status
, subscript_activ_timestp as action_date
, replace(replace(operator_id,chr(10),''''),chr(13),'''') as operator_id
from BR_PORT_NUM_TMP_FULL_OPER';
OPEN c_cursor FOR l_query;
FETCH c_cursor BULK COLLECT INTO l_Strings LIMIT 5000000;
CLOSE c_cursor;
FOR i IN 1..l_Strings.COUNT
LOOP
BEGIN
INSERT INTO ANI_BY_TELCO_OPERATORS_PP ( ANI, PREV_TE_OPER_ID, TRANSFORMATION_ID, LAST_MODIFIED, CREATED, TE_OPER_ID)
VALUES ( l_Strings(i).l_ani, l_Strings(i).l_prev_carrier_id, l_Strings(i).l_transformation_id, l_Strings(i).l_action_date, l_Strings(i).l_action_date,l_Strings(i).l_oper_id);
EXCEPTION
WHEN NO_DATA_FOUND THEN
ISSUE_LOG_PREPROD('RECIPIENT_CIC_NOT_FOUND PP', 'Current Carrier: ' || l_Strings(i).l_current_cic || ' not exists in LNP system.',l_Strings(i).l_current_cic,'BR');
WHEN OTHERS THEN
ISSUE_LOG_PREPROD('ERROR','BR FULL BDO Ports proc. '||l_Strings(i).l_ANI|| CHR(13) || SQLERRM || CHR(13)||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,NULL,'BR');
--null;-- ANI exists
END;
END LOOP;
COMMIT;
END;
/