CODEWITHSUNDEEP
oracle-databaseoptimizationsql-insert
Arun Palanisamy
Arun Palanisamy

Reputation: 5469

How to optimise insert into..select Query?

I have a below insert query:

Insert into MAP_REL_ATTRIBUTE_PHYID
    (MAP_RELNAME, MAP_ATTRNAME,MAP_ATTRVALUE,Source_PHYID,MAP_REL_PHYID) 
Select  a.map_relname,a.MAP_ATTRNAME,a.MAP_ATTRVALUE,a.id,b.ID 
from key_attribute a ,
           target_attribute b,
           map_rel_phyid c 
 where  a.id = c.Source_phyid 
 and b.id=c.map_rel_phyid 
 and  a.map_relname = 'Connected By' 
 and  a.map_attrname= b.attr_name  
 and  dbms_lob.compare(a.MAP_ATTRVALUE,b.ATTR_VALUE)=0

For DDL and sample data please refer : Check here

This select query returns around 20 million records and thus taking infinite time to insert the data's into the table. I'm trying to optimise this query. I'm new to oracle. Based on the suggestions I found there can be two possibilities to optimise this:

  1. Creating Indexes in which I'm not sure on which columns to index and what kind of indexes I should create.

  2. Using Bulk Processing with BULK COLLECT and FORALL.

I don't know whether the above solutions are correct. Can somebody please suggest me on this? And let me know if there are any other way to improve the performance.

Upvotes: 1

Views: 4918

Answers (1)

Rusty
Rusty

Reputation: 2138

1) Use append hint for insert

2) Do not use any indexes if you select ALL rows from the table

3) Use parallel hints for insert and select (make sure that parallel DML is enabled first)

alter session enable parallel dml;

Insert /*+ APPEND PARALLEL(4) */ into MAP_REL_ATTRIBUTE_PHYID
    (MAP_RELNAME, MAP_ATTRNAME,MAP_ATTRVALUE,Source_PHYID,MAP_REL_PHYID) 
Select /*+ PARALLEL(4) USE_HASH(a b c) */ a.map_relname,a.MAP_ATTRNAME,a.MAP_ATTRVALUE,a.id,b.ID 
from key_attribute a ,
           target_attribute b,
           map_rel_phyid c 
 where  a.id = c.Source_phyid 
 and b.id=c.map_rel_phyid 
 and  a.map_relname = 'Connected By' 
 and  a.map_attrname= b.attr_name  
 and  dbms_lob.compare(a.MAP_ATTRVALUE,b.ATTR_VALUE)=0;

Upvotes: 2

Related Questions