Hi,
i created two table emp, emp2 from hr.employees.
after that i executed below insert statements
now in both tables employee_id is unique.
insert into emp select * from emp; --4 times
insert into emp2 select * from emp2; --4 times
now in both tables employee_id is not unique.
then i ran below update statements
update emp set employee_id = rownum;
now employee_id is unique in emp table but not in emp2
after that i ran below updates on 2 tables.
update emp set employee_id = employee_id;
update emp2 set employee_id = employee_id;
then i noticed that the update statement on Emp2 table is taking lesser time than update on emp table.
both table are created from same table and has the same data except employee_id column.
Why update on emp2 submitting faster than update on EMP table.
Thanks in Advance.
So the LiveSQL script shows the update on emp taking 5cs vs. 4cs on emp2. A difference of 0.01s. That's not worth talking about!
Particularly as this is the smallest unit of measurement for dbms_utility.get_time. This could be the result of rounding or collection errors.
Indeed, when I run it they both come out at 6cs:
create table emp as select * from hr.employees;
create table emp2 as select * from hr.employees;
insert into emp select * from emp;
insert into emp2 select * from emp2;
insert into emp select * from emp;
insert into emp2 select * from emp2;
insert into emp select * from emp;
insert into emp2 select * from emp2;
insert into emp select * from emp;
insert into emp2 select * from emp2;
update emp set employee_id = rownum;
commit;
declare
x number;
begin
x := dbms_utility.get_time;
dbms_output.put_line( 'Time - ' || x );
update emp
set employee_id = employee_id;
dbms_output.put_line( 'Time - ' || ( dbms_utility.get_time - x ) );
end;
/
Time - 1803110186
Time - 6
declare
x number;
begin
x := dbms_utility.get_time;
dbms_output.put_line( 'Time - ' || x );
update emp2
set employee_id = employee_id;
dbms_output.put_line( 'Time - ' || ( dbms_utility.get_time - x ) );
end;
/
Time - 1803110244
Time - 6
When running performance tests, ensure you:
- Repeat it several times. The first execution is often slower due to parsing, etc. And you may get slow executions by chance on other runs. Multiple runs allow you to spot these problems
- Have meaningfully different performance between the tests! One test being faster than the other by a single unit of the smallest measurement available isn't significant in my book.