I have a table TDATAMAP which has around 10 million records, I want to fetch all the records into a PL/SQL table type variable, match it with some criteria and finally insert all the required records in a staging table. Please tell me if its possible to do it using PL/SQL table typle variable and BULK INSERT/COLLECT . I am also concerned about the performance of the code.
1
-
what do you mean "match it with some criteria" exactly? I think there is a much better way to do what you want, but not enough info to tell. Can u give some sample code?tbone– tbone2012-02-10 12:12:30 +00:00Commented Feb 10, 2012 at 12:12
Add a comment
|
2 Answers
" I want to fetch all the records into a PL/SQL table type variable, match it with some criteria and finally insert all the required records in a staging table."
This may well not be the right approach to take. SQL is a set-based programming language. It has features which allow us to identify and work with just the set of records we want.
So, instead of a three stage process - extract, filter, insert - celebrate the joy of sets:
insert into your_staging_table
select whatver
from your_10m_table
where something = your_matching_criteria;
There will be scenarios when this is not good enough. But this is the approach to start with.
Comments
You can, but you probably should not, load 10 million records into memory at once - as long as there is sufficient memory to hold that much. Normally BULK COLLECT is used with the LIMIT clause to process a finite number of rows at a time e.g. 1000.
From the documentation:
The BULK COLLECT clause lets you fetch entire columns from the result set, or the entire result set at once. The following example, retrieves columns from a cursor into a collection:
DECLARE
TYPE NameList IS TABLE OF emp.ename%TYPE;
names NameList;
CURSOR c1 IS SELECT ename FROM emp WHERE job = 'CLERK';
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO names;
...
CLOSE c1;
END;
The following example uses the LIMIT clause. With each iteration of the loop, the FETCH statement fetches 100 rows (or less) into index-by table acct_ids. The previous values are overwritten.
DECLARE
TYPE NumList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
CURSOR c1 IS SELECT acct_id FROM accounts;
acct_ids NumList;
rows NATURAL := 100; -- set limit
BEGIN
OPEN c1;
LOOP
/* The following statement fetches 100 rows (or less). */
FETCH c1 BULK COLLECT INTO acct_ids LIMIT rows;
EXIT WHEN c1%NOTFOUND;
...
END LOOP;
CLOSE c1;
END;
Comments
