Efficient way to insert values in an Oracle SQL table

Question

I have two tables like

 create table nodes_tbl as (
 select 'a' as nodeid, 'some string' as dummy_string, 0 as subnetid from dual union all
 select 'b', 'qwe', 0 from dual  union all
 select 'c', 'asd', 0  from dual union all
 select 'd', 'zxc', 0 from dual union all
 select 'e', 'rty', 0 from dual);

And

 create table subnets as (
 select 'a' as nodeid, 1 as subnetid from dual union all
 select 'b', 2 from dual  union all
 select 'c', 2 from dual union all
 select 'd', 3 from dual union all
 select 'e', 4 as nodeid from dual);

With several millions of records a join works fast.

select  n.NODEID, n.DUMMY_STRING, s.subnetid
   from nodes_tbl n, subnets s where s.nodeid=n.nodeid

Writes are fast as well

create table test_tbl as  n.NODEID, s.subnetid 
 from nodes_tbl n, subnets s where s.nodeid=n.nodeid  --10M records in 2s.

However, when I try to update table and add values to the column the query is very slow

      UPDATE nodes_tbl n
       SET subnetid = (SELECT subnetid
                             FROM subnets s
                            WHERE s.nodeid = n.nodeid)
    WHERE EXISTS (
    SELECT subnetid  FROM subnets s
                            WHERE s.nodeid = n.nodeid)  --8 minutes for 100K records

Why is insert so much slower than a create table from a select statement? What is the most efficient way to do this insert?

I know about create view option, but want to avoid it.

Littlefoot · Accepted Answer · 2019-02-27 19:24:31Z

3

Try MERGE instead:

merge into nodes_tbl n
  using (select s.subnetid, s.nodeid 
         from subnets s
        ) x
  on (x.nodeid = n.nodeid)
when matched then update set
  n.subnetid = x.subnetid;

Any improvement?

By the way, did you create index on NODEID column in both tables?

answered Feb 27, 2019 at 19:24

Littlefoot

144k15 gold badges42 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stepan Over a year ago

Runs at least 500 times faster. Thank you.

Collectives™ on Stack Overflow

Efficient way to insert values in an Oracle SQL table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related