Home » RDBMS Server » Performance Tuning » Bulk insert is faster then insert into select * ...... (Oracle 10g, Unix)
Bulk insert is faster then insert into select * ...... [message #380392] Sun, 11 January 2009 13:25 Go to next message
dasgupta.amitava@gmail
Messages: 32
Registered: November 2007
Member
Dear frnds,
In order to insert recs to a table I have two options:
a) "Insert into <table1> select <col1> from <table2>"
b) "select <col1> bulk collect into v_rec from <table2>"
and then "forall i in v_rec insert into <table1>"


Which approach is recomemded, for HUGE set of records and for smaller set of records???? I this option a is always the best option because that is actually hitting th DB once...Am I right if not please tell me the reason also.


Thanks in advance
Amitava
Re: Bulk insert is faster then insert into select * ...... [message #380452 is a reply to message #380392] Sun, 11 January 2009 23:54 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Neither.

You should
INSERT /*+ APPEND*/ INTO <table1> select <col1> from <table2>


Without the APPEND hint, it will use conventional path inserts which will run much slower and probably bust your UNDO segments.

Make sure you have indexes dropped beforehand.

There are two problems with BULK COLLECT:
- Your unlimited bulk fetch will blow out your memory by creating a huge array. This will then page to disk before ultimately running out of temp space. This can be mitigated by using the LIMIT clause inside a loop.
- It uses conventional path insert.

Ross Leishman
Re: Bulk insert is faster then insert into select * ...... [message #423244 is a reply to message #380452] Wed, 23 September 2009 01:23 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Hi all,

Smile Feeling good to come back to community after such a long time.
I have one question on similar topic, so I am not opening new thread.

I have one complex query using 5 tables. I want this query's result in one table, which will be then used by appication to show report data.

Earlier I was doing

truncate table <table_Name>
insert /*+ APPEND*/ into <table_Name>
<select Query>

This was taking around 15 minutes to execute.

Once I tried doing

Drop table <table_Name> ;
Create table <table_Name>
<select Query>

And It did my job within 5 minutes.

Before going to this solution for other similar natured requirements, I hardly want to know the your expertize suggestions on this.

I feel conceptually instade of appending records to existing table, creating new table is giving me faster result because that way oracle is getting chance to avoid some internal operations.

Please advice me if it is the better alternative.

Thanks..

Regards,
Dipali..
Re: Bulk insert is faster then insert into select * ...... [message #423247 is a reply to message #423244] Wed, 23 September 2009 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you recreate the table you have to also regrant all the privileges, recreate the indexes, triggers...

Regards
Michel
Re: Bulk insert is faster then insert into select * ...... [message #423249 is a reply to message #423247] Wed, 23 September 2009 01:47 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Hi Michel,

The table I am using either for insert or create is a plain table having no constraints, indexes etc. Its used to hold the result of that complex query.
As per application requirement, we need to generate such table which hold final data to be used by application.

Thanks,
Regards,
Dipali..
Re: Bulk insert is faster then insert into select * ...... [message #423250 is a reply to message #423249] Wed, 23 September 2009 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Temporary table?

Regards
Michel
Re: Bulk insert is faster then insert into select * ...... [message #423252 is a reply to message #423250] Wed, 23 September 2009 01:58 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Use of that table is kind of that.. But as we are using that tables data across the sessions, we are not using Temporary table.

Again just I checked responce time of both.
the create table as select is giving result in 2 seconds while insert into select is taking more than 20 seconds..

I need your suggestions..
Re: Bulk insert is faster then insert into select * ...... [message #423256 is a reply to message #423252] Wed, 23 September 2009 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have the answer, create is faster than insert, what suggestions do you want now?

Regards
Michel
Re: Bulk insert is faster then insert into select * ...... [message #423345 is a reply to message #423256] Wed, 23 September 2009 09:09 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

This is enough for Me Michel, that YOU told this.. Thank You..

However I forgot to mention that, I am doing all this within a stored procedure..
My miss that I should have ask it like
Creating a table inside procedure V/S Appending records in existing table inside the procedure..

For this what I did all the testings what I have mentioned earlier and wanted your feedback to go ahead.. Is your feedback still same Michel ?

Thanks so much..

Regards,
Dipali..
Re: Bulk insert is faster then insert into select * ...... [message #423362 is a reply to message #423345] Wed, 23 September 2009 12:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am doing all this within a stored procedure..

You don't have to mention it, it was obvious (given all the other posts on this kind of subject).

Regards
Michel
Re: Bulk insert is faster then insert into select * ...... [message #423388 is a reply to message #423256] Wed, 23 September 2009 22:44 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Michel Cadot wrote on Wed, 23 September 2009 14:37
You have the answer, create is faster than insert, what suggestions do you want now?

Regards
Michel


Dear Michel!

May you clear me more about the statement : Create is faster than insert?. I'll do an example.

C:\>sqlplus oravn/oravn

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Sep 24 10:43:09 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

oravn@ORAVN> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
A                              TABLE
CLUSTERED                      TABLE
NON_CLUSTERED                  TABLE
BIGTAB                         TABLE

oravn@ORAVN> drop table a purge;

Table dropped.

oravn@ORAVN>
oravn@ORAVN>
oravn@ORAVN> set timing on
oravn@ORAVN> set autotrace on
oravn@ORAVN> create table a as select * from bigtab;

Table created.

Elapsed: 00:00:39.46
oravn@ORAVN> select count(*) from a;

  COUNT(*)
----------
   1000000

Elapsed: 00:00:05.62

Execution Plan
----------------------------------------------------------
Plan hash value: 3918351354

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  3966   (1)| 00:00:48 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| A    |  1064K|  3966   (1)| 00:00:48 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         28  recursive calls
          0  db block gets
      14494  consistent gets
      14404  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

oravn@ORAVN> set autotrace off
oravn@ORAVN> drop table a purge;

Table dropped.

Elapsed: 00:00:01.15
oravn@ORAVN> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.43

oravn@ORAVN> create table a as select * from bigtab
  2  where 1=2;

Table created.

Elapsed: 00:00:00.26
oravn@ORAVN> set autotrace
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
oravn@ORAVN> set autotrace on
oravn@ORAVN> insert into a select * from bigtab;

1000000 rows created.

Elapsed: 00:00:21.28

Execution Plan
----------------------------------------------------------
Plan hash value: 441133017

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | INSERT STATEMENT  |        |  1002K|   134M|  3985   (1)| 00:00:48 |
|   1 |  TABLE ACCESS FULL| BIGTAB |  1002K|   134M|  3985   (1)| 00:00:48 |
----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
       4693  recursive calls
     128912  db block gets
      36589  consistent gets
          1  physical reads
  117469048  redo size
       1778  bytes sent via SQL*Net to client
       1142  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
         52  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

oravn@ORAVN> set autotrae off
SP2-0158: unknown SET option "autotrae"
oravn@ORAVN> set autotrace off
oravn@ORAVN> set timing off
oravn@ORAVN>


As the timing was switched on (of course, this is not all explanation), the time with "insert into A.." was less to the time with "Create table A as select ...".

Thank you!
Re: Bulk insert is faster then insert into select * ...... [message #423390 is a reply to message #423388] Wed, 23 September 2009 23:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
YOu extract the sentence from its context.
It is in OP's case CTAS is faster than truncate+insert. It was not a general sentence (although in this case, it is true à 99%).

In addition, your insert statement takes advantage of the blocks loaded in buffer from the first CTAS: see the physical reads.
The difference in time is perfectly explained by this: difference in time: 18s, difference in phyreads: 14000, so less than 0.8ms per phyread not so bad IO subsystem (but actually the time taken by phyreads are greater are the work done in CPU and memory by insert is much more due to flush).

Regards
Michel

[Updated on: Wed, 23 September 2009 23:17]

Report message to a moderator

Re: Bulk insert is faster then insert into select * ...... [message #423395 is a reply to message #380392] Wed, 23 September 2009 23:52 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you, Michel, for your explanation!

According to my mind, in the OP's - Online Production, the Insert into A select ....from B spend time much more Create table A as select ...from B because:

1 - The target table(s) (B,C,D... if the select statement join many tables) are not stable, the more transaction, the more activity always do DML at them. So that, block are change frequently. If Insert into A select ... from B, Oracle will fetch (read, load) all rows into a array, does insert into A after fetch-read-load successfully. However, when Oracle do this task, the other transaction do operations such as Update/Insert/Delete one or more rows, and Oracle may be fetch-read-load into a array against.

2- Create table as select ... , I did not imagine or think about, or got knowledge about internal activities.

May you clarify more?

Thank you!
Re: Bulk insert is faster then insert into select * ...... [message #423406 is a reply to message #423395] Thu, 24 September 2009 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Oracle will fetch (read, load) all rows into a array

This is wrong. Oracle fetches the rows when it needs it that is a couple of tens at each time. If it is an insert append Oracle does about the same thing than ctas.

Regards
Michel
Re: Bulk insert is faster then insert into select * ...... [message #423421 is a reply to message #380392] Thu, 24 September 2009 01:56 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you, Michel!
Re: Bulk insert is faster then insert into select * ...... [message #423431 is a reply to message #423421] Thu, 24 September 2009 03:11 Go to previous message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Thank you So much Michel and trantuananh24hg .

Before posting my issue over here, I tried to search about what exactly happens internally when we fier either CTAS or Inser into select statement from oracle. But I coundn't get that.

May I have the link reference if any for this knowledge.?

Thanks and Regards,
Dipali..
Previous Topic: Stale statistics and Dynamic Sampling
Next Topic: Need A Small help on Tuning this query.
Goto Forum:
  


Current Time: Sun Dec 04 02:55:40 CST 2016

Total time taken to generate the page: 0.20854 seconds