user12869307 wrote:Theoretically speaking it can be viewed as a parellel processing. It certainly doesn't load all the data from the source query into memory before inserting it, if that's what you are thinking.
One more help I asked is when we do
insert into target_table_name
select * from source_table;
how will this query execute. select query once and then insert into the table
OR select and insert parallely?
user12869307 wrote:You can generate data, lots of people do it all the time. Here is an example that will generate one million lines, but is configurable.
--- we can't check the performance of this too now since we do not have enough data.
DEFINE NUMROWSA = 1000 define numrowsb = 1000 create table test_a ( key1, vn1, vn2, vn3, vd1, nomenc, vv1, vv2, vv3, vv4) as with a as (select level lvl from dual connect by level <= &numrowsa) , b as (select level lvl from dual connect by level <= &numrowsb) select rownum , round(dbms_random.value(0, 99999999)) , round(dbms_random.value(0, 99999999)) , round(dbms_random.value(0, 99999999)) , trunc(sysdate) , 'N' || mod(rownum,10) , rpad('vv', 70 + round(dbms_random.value(0, 20)), '*') , rpad('vv'||rownum, 70 + round(dbms_random.value(0, 20)), '*') , rpad('vv', 70 + round(dbms_random.value(0, 20)), '*') , rpad('vv', 56 + round(dbms_random.value(0, 32)), '*') from a,b;
One doubt in the above insert is, will the select query will be executed only once and all the rows will be inserted into table E? or it will be inserted and select parallely?The whole statement, both the SELECT part and the INSERT part, will be done in one session. For parallel execution, you need to do "direct path insert" using the /*+ APPEND */ hint, plus you need to enable and use "parallel DML".
user12869307 wrote:You would only do BULK COLLECT + FORALL if the input requires some processing before insertion, and this processing cannot be done in SQL.
Thanks for the reply. actually a smalll correction two tables which I mentioned will increase by 100 millions every week. in what kind of scenario we can use bulk collect forall statements for inserting?
insert into table e
Edited by: Keith Jamieson on Sep 5, 2012 12:19 PM
create view e as select a.col1, b.col2, c.col3, d.col4 from a,b,c,d where a.col1 = b.col1 and b.col2 = c.col2 and c.col3 = d.col3;
user12869307 wrote:Seriously, you're going to have 5.2 billion records per year inserted to these tables? Is this a large supermarket chain shopping transactions database?
we will be adding to the existing table. we are not recreating our target table every week. thanks for your reply on using bulk_collect, forall.. to make clear, I am mentioning again four source tables joined and result inserted into one target table.
two sourcet table have 1.5 million records. two source tables increase by 100 million records every week.