Thanks for the inputs.
Here are some important results.
1)The insert into Original table with 43million records takes about 10minutes and the
explain plan as attached in
the text file "original table"
insert into original table.......
select ....... from a,b
where a.col1=b.col1 --->index columns
and a.col2=x -->non index
and b.col2=x -->non index
Note:Original table is having a composite primary key of 3 columns
2)I created a temp table similiar to the original table as
create table temp1 as select * from originaltable where 1=2;
this didn't create any index on the table and the insert got executed in 4 seconds.
I am attaching the explain plan in the attachment "new table"
3)I created a composite primary key of the 3 cols on this new table and the insert
took 13 seconds.
The execution can be found in the same file "new table" at the end.
pls advice
thanks
-----Original Message-----
Sent: Monday, February 24, 2003 7:04 PM
To: Multiple recipients of list ORACLE-L
Ignoring bulk insert, and assuming you are performing a single insert
statement have you looked at the following (as already suggested):
1) What, and how many, indexes exist on the destination table. Each index
requires updating as records are inserted. If there are indexes not
required then remove them. Alternatively you might even consider disabling
the indexes before inserting the data then rebuilding them... Although not
likely if you are performing a single insert with no other work.
2) Is col2 (in your example query) indexed in either table? What
proportion of the table meets this selection criteria? IE: If col2 was
gender and your data was evenly spread then selecting male would return
~50% of rows. If col2 was age and your data was evenly spread between 1
and 100 then selecting 20 would return ~1% of rows. If either of the
selection returns a relatively low number of rows then look at indexing
these columns.
3) Have you looked at an explain plan for this statement? If not, start
sql*plus, type "set autotrace on", then execute the query. This will show
if indexes are being used, etc. We may be able to help further if we know
this information and know about your data.
4) Have you tried running the select statement on it's own, or inserting
into a table which contains no indexes (and maybe has nologging set). This
may give an idea of whether the time is consumed retrieving the data or
inserting it into the destination. This will give you a good idea of where
to focus your tuning efforts.
5) Have you tried a parallel hint? This may help depending on your
physical configuration.
Regards,
Mark.
"Basavaraja, Ravindra"
<[EMAIL PROTECTED] To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
obile.com> cc:
Sent by: Subject: RE: slow insert
[EMAIL PROTECTED]
25/02/2003 13:14
Please respond to
ORACLE-L
I tried this way and infact bulk collect took about 1 minutes more than
my current case?
Any thoughts....
thanks
-----Original Message-----
Sent: Saturday, February 22, 2003 7:54 AM
To: Multiple recipients of list ORACLE-L
You can read more abt it in PL/SQL User's Guide and Reference - Chapter 4 -
Collections and Records !
anyway, below is the simple code to copy data from emp table. note that,
after u fetch from the cursor, u have to check for the notfound condition
at
the end. say, the emp table has 15 rows and u r fetching 2 rows at a time.
during the 1st 7 fetches u will be ok. but during the 8th fetch, the fetch
will bring only 1 row and the condition "notfound" will be true. thus u
will
never process the last set of rows. hence, check for the condition after u
have inserted/updated/deleted the rows.
finally, try playing with the limit clause in the fetch and see what number
is best for u. considering my hardware etc, i got better performance with
5000 fetches at a time.
also, till 8.1.7. u could not have any stmts in the FORALL loop. only 1,
i.e. insert/update/delete. but with 9i u can have a pl/sql block & any
dynamic stmts too. moreover, with 9i u can also trap for individual
exception whereas with 8.1.7. if there was an error, entire bulk process
would rollback.
hope this helps & let me know if u need any more help.
-- code follows
declare
cursor c_emp is
select empno, ename, hiredate from emp ;
type empno_arr_type is table of number(4);
v_empno_arr empno_arr_type;
type name_arr_type is table of varchar2(10);
v_ename_arr name_arr_type;
type date_arr_type is table of date;
v_hiredate_arr date_arr_type;
v_arr_idx binary_integer := 0;
v_arr_cnt binary_integer := 0;
begin
open c_emp;
loop
fetch c_emp bulk collect
into v_empno_arr, v_ename_arr, v_hiredate_arr
limit 2;
-- DO NOT CHECK for notfound here
v_arr_cnt := v_empno_arr.count();
dbms_output.put_line(v_arr_cnt);
forall v_arr_idx in 1 .. v_arr_cnt
insert into emp_copy(empno, ename, hiredate)
values(v_empno_arr(v_arr_idx),v_ename_arr(v_arr_idx),v_hiredate_arr(v_arr_id
x));
commit;
-- note : the condition has to be checked here ONLY
exit when c_emp%notfound;
end loop;
close c_emp;
end;
/
-----Original Message-----
Ravindra
Sent: Friday, February 21, 2003 7:09 PM
To: Multiple recipients of list ORACLE-L
Yes I am usng 8.1.7 EE.How do I use bulk inserts..Any docs or links on
metalink plss?
-----Original Message-----
Sent: Friday, February 21, 2003 3:34 PM
To: Multiple recipients of list ORACLE-L
if u r having oracle 8.1.7 EE or higher, try using bulk inserts ! that
surely will speed up !
and of course, u surely might have thought of APPEND hint &
dropping/disabling indexes etc.
-----Original Message-----
Ravindra
Sent: Friday, February 21, 2003 3:08 PM
To: Multiple recipients of list ORACLE-L
hi,
I have an insert statement that will insert about 400000 records into a
table having 43million records.The values for the
insert statement are from a select statement that has a join.This query
take
about 5-10minutes.What are the ways in
which we can speed up this process.the statement looks like
insert into .......
select ....... from a,b
where a.col1=b.col1 --->index columns
and a.col2=x -->non index
and b.col2=x -->non index
Thanks
--
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: <[EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Basavaraja, Ravindra
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: <[EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Basavaraja, Ravindra
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message
(or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
by reply e-mail or by telephone on (61 3) 9612-6999.
Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
that do not relate to the official business of
Transurban City Link Ltd
shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mark Richard
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Elapsed: 00:09:580.37
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE (Cost=27 Card=431196 Bytes
=4743156)
1 0 NESTED LOOPS* (Cost=27 Card=431196 Bytes=4743156) :Q480000
0
2 1 TABLE ACCESS* (FULL) OF 'TABLE2' (Cost=1 Card=13 By :Q480000
tes=39) 0
3 1 TABLE ACCESS* (BY INDEX ROWID) OF 'TABLE1' ( :Q480000
Cost=2 Card=431196 Bytes=3449568) 0
4 3 INDEX* (RANGE SCAN) OF 'IDX3_TABLE1' (NON- :Q480000
UNIQUE) (Cost=1 Card=431196) 0
1 PARALLEL_TO_SERIAL SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDE
X(A2 "IDX3_TABLE1") */ A
2 PARALLEL_COMBINED_WITH_PARENT
3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_COMBINED_WITH_PARENT
Statistics
----------------------------------------------------------
237 recursive calls
1325646 db block gets
17816 consistent gets
242723 physical reads
155667036 redo size
540 bytes sent via SQL*Net to client
835 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
434009 rows processed 1 INSERT INTO temp1
2 ( Col1, col2,col3, col4,col5,col6)
3 SELECT a.col1, a.col2, 709, 'N', SYSDATE, SYSDATE
4 FROM table1 a, TABLE2 b
5 WHERE a.Col1 = b.col1
6 AND a.col2 = 'N'
7* AND b.col2 = 'N'
21:10:27 SQL> /
434009 rows created.
Elapsed: 00:00:04.87
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE (Cost=222 Card=434009 Byte
s=4774099)
1 0 NESTED LOOPS* (Cost=222 Card=434009 Bytes=4774099) :Q483300
0
2 1 TABLE ACCESS* (FULL) OF 'TABLE1' (Cost=222 C :Q483300
ard=434009 Bytes=3472072) 0
3 1 INDEX* (RANGE SCAN) OF 'IDX2_TABLE2' (NON-UNIQUE) :Q483300
0
1 PARALLEL_TO_SERIAL SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDE
X(A2 "IDX2_TABLE2") */ A1.C0,A1
2 PARALLEL_COMBINED_WITH_PARENT
3 PARALLEL_COMBINED_WITH_PARENT
Statistics
----------------------------------------------------------
163 recursive calls
12322 db block gets
4593 consistent gets
2309 physical reads
18982212 redo size
541 bytes sent via SQL*Net to client
815 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
434009 rows processed
-------------------------------------------------------------------------------------
with the composite primary key of col1,col2,col3
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE (Cost=222 Card=434009 Byte
s=4774099)
1 0 NESTED LOOPS* (Cost=222 Card=434009 Bytes=4774099) :Q484100
0
2 1 TABLE ACCESS* (FULL) OF 'TABLE1' (Cost=222 C :Q484100
ard=434009 Bytes=3472072) 0
3 1 INDEX* (RANGE SCAN) OF 'IDX2_TABLE2' (NON-UNIQUE) :Q484100
0
1 PARALLEL_TO_SERIAL SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDE
X(A2 "IDX2_TABLE2") */ A1.C0,A1
2 PARALLEL_COMBINED_WITH_PARENT
3 PARALLEL_COMBINED_WITH_PARENT
Statistics
----------------------------------------------------------
318 recursive calls
240382 db block gets
15473 consistent gets
2308 physical reads
88302036 redo size
541 bytes sent via SQL*Net to client
822 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
434009 rows processed
