Thanks for the question, Mohit.
Asked: November 30, 2023 - 2:10 am UTC
Last updated: December 04, 2023 - 6:32 am UTC
Version: 21c
Viewed 1000+ times
You Asked
Hi Tom,
Below are the scripts for your reference wherein it works like if a row is not present in a table then insert it based on one column value.
1.My query to you is simply looking at below scripts (without any alteration)
which method do you think is better w.r.t to good execution time and performance and why?
2.If needed to check execution plan/time of a plsql block (as in first method ) of below scripts .How should we do it?
--table creation
create table test1(id number,name varchar2(500),city varchar2(500));
--method1 using plsql block
declare
vtemp number(10,0):=0;
begin
begin
select 1 into vtemp from dual
where not EXISTS
(
select * from test1
where city='New York' --based on city
);
exception
when no_data_found then null;
end;
if vtemp=1
then
insert into test1 values('1','Alexa','New York');
commit;
end if;
end;
/
OR
----method2 using insert select
insert into test1(id,name,city)
select 1,'Alexa','New York' from dual
where
'New York' not in (select city from test1 where city='New York');
—- based on city new york will inserting in table
Thanks
and Connor said...
I would recommend a 3rd option
insert into test1(id,name,city)
select 1,'Alexa','New York' from dual
where not exists
(select 1 from test1 where city='New York');
which will be quicker than the PLSQL alternative.
Note that this does NOT guarantee the elimination of duplicates - only a unique key can do that, but the existence check can still make things more efficient because avoiding an error is quicker than responding to an error, eg
SQL> create table t ( x int primary key );
Table created.
SQL>
SQL> insert into t
2 select rownum*2
3 from dual
4 connect by level <= 100000;
100000 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> set timing on
SQL> begin
2 for i in 1 .. 200000 loop
3 insert into t
4 select i from dual
5 where not exists ( select 1 from t where x = i );
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.85
SQL>
SQL> create table t ( x int primary key );
Table created.
SQL>
SQL> insert into t
2 select rownum*2
3 from dual
4 connect by level <= 100000;
100000 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> set timing on
SQL> begin
2 for i in 1 .. 200000 loop
3 begin
4 insert into t
5 values (i);
6 exception
7 when dup_val_on_index then null;
8 end;
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:16.41