Performance

[ofsouto]

Wich example can I get a better performance?

EXAMPLE 1
=========================================================================================
insert into table_1
select tbl2.column_a, tbl3.column_b
SUM(tbl2.column_b) value,
COUNT(tbl2.column_b) quantity
from table_2 tbl2, table_3 tbl3
where tbl2.column_a = tbl3.column_a
group by tbl2.column_a, tbl3.column_b;


EXAMPLE 2
=========================================================================================
counter pls_integer;
sum number(16,2);
description varchar2;
identity number(10) := 0;

CURSOR cursor_1
IS
SELECT tbl2.column_a, tbl2.column_b
from table_2 tbl2, table_3 tbl3
where tbl2.column_a = tbl3.column_a;

r0 cursor_1%ROWTYPE;
BEGIN
OPEN cursor_1;

LOOP
FETCH cursor_1 INTO r0;
EXIT WHEN cursor_1%NOTFOUND;

BEGIN
if identity <> r0.column_a then
if identity <> 0 then
select tbl3.column_b to description
from table_3 tbl3
where tbl3.column_a = identity;
insert into table_1 values (identity, description, sum, counter);
identity := r0.column_a;
counter := 0;
sum := 0;
end if;
counter := counter + 1;
sum := sum + tbl2.column_b;
END;
END LOOP;
CLOSE cursor_1;

select tbl3.column_b to description
from table_3 tbl3
where tbl3.column_a = identity;
insert into table_1 values (identity, description, sum, counter);

Thanks in advance.

 

[sem]

Pure sql approach will provide better performance.

 

[ThomVF]

You can boost the performance of the pure SQL by parallelizing (provided you have more than 1 CPU):

This example assumes you have 4 or more CPUs
====================================================
ALTER SESSION ENABLE PARALLEL DML;
insert /*+ parallel(table1,4) */
into table_1
select /*+ parallel(table2,4), parallel(table3,4) */
tbl2.column_a, tbl3.column_b
SUM(tbl2.column_b) value,
COUNT(tbl2.column_b) quantity
from table_2 tbl2, table_3 tbl3
where tbl2.column_a = tbl3.column_a
group by tbl2.column_a, tbl3.column_b;
ALTER SESSION DISABLE PARALLEL DML;
====================================================


Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com