Power of Analytic Function

Analytical Functions were introduced in Oracle 8i and were further enhanced. Before introduction of these functions, the only way, to accomplish the task performed by these functions, was PL/SQL block. This also had a performance impact.

I remember a mantra from Thomas Kyte that if you can accomplish a task using a single statement then do it in a single statement. Only, if it is impossible to make use of single statement then go ahead with a PL/SQL block.

Here, I had an oppurtunity to make use of an Analytic function to improve the performance of a pl/sql block which took almost 15-16 minutes and was finally optimized to less than a minute. There was a database procedure that was taking almost 15-16 minutes to execute. It was a simple procedure inserting Millions of records into another table. Some of the column values differed based on some condition. Since, I do not want to attach the original code of the customer, I am attaching a sample code that almost simulates the original. The db version is 8174.

To create a test case
---------------------
SQL> create table a_cdr as select * from all_objects;
Table created.

begin
for i in 1..10
loop
insert /*+ append */ into a_cdr select * from all_objects;
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.

SQL> select count(*) from a_cdr;
COUNT(*)
----------
668157

1 row selected.
SQL> update a_cdr set object_id=rownum;

668157 rows updated.

update a_cdr set data_object_id=round(dbms_random.value(1,9));

668157 rows updated.

SQL> commit;

Commit complete.

create table in_lt (
a_next_value number,
a_record_id number,
a_record_type number,
a_name varchar2(30),
a_temporary varchar2(10));

Table created.

create table app_sequence as select object_id a_next_value, 47 app_type from all_objects where rownum=1;

Pl/SQL block
--------------
declare
cursor cdr_main is
select a.* from a_cdr a;
v_record_id number;
v_sequence number;
begin
v_record_id := 0;
select a_next_value+1 into v_sequence from app_sequence where app_type=47;
update app_sequence set a_next_value=a_next_value+1 where app_type=47;
commit;
for x in cdr_mainloop
if x.data_object_id in (1,2,3) then
v_record_id:=v_record_id+1;
insert into in_lt values(v_sequence, v_record_id, 1,x.object_name, 'OTT');
end if;
if x.data_object_id in (4,5) then
v_record_id:=v_record_id+1;
insert into in_lt values(v_sequence, v_record_id, 1,x.object_name, 'FF');
end if;
if x.data_object_id in (6,7,8) then
v_record_id:=v_record_id+1;
insert into in_lt values(v_sequence, v_record_id, 1,x.object_name, 'SSE');
end if;
if x.data_object_id in (9) then
v_record_id:=v_record_id+1;
insert into in_lt values(v_sequence, v_record_id, 1,x.object_name, 'N');
insert into in_lt values(v_sequence, v_record_id, 2,x.object_name, 'NZ');
end if;
end loop;
commit;
end;


The procedure looked very simple but the challenge here was to insert 2 rows when the value of data_object_id is 9. Also, the column values will be different for this value. Hence, I used an Analytic Function DENSE_RANK and introduced a cartesian-product to generate 2 rows. The modified block is as under :

begin
insert /*+ append */ into in_lt
select (select a_next_value+1 from app_sequence where app_type=47) a_next_value,
dense_rank() over (order by r_id) r_id, b_part_id, object_name,
(case when data_object_id in (1,2,3) then 'OTT'
else case when data_object_id in (4,5) then 'FF'
else case when data_object_id in (6,7,8) then 'SSE'
else case when data_object_id = 9 and b_part_id=1 then 'N'
else 'NZ' end end end end) a_temporary
from
( select a_cdr.rowid r_id, 1 b_part_id, a_cdr.*
from a_cdr
where data_object_id in (1,2,3,4,5,6,7,8)
union
select a_cdr.rowid r_id, r_num b_part_id, a_cdr.*from a_cdr,
(select rownum r_num from all_objects where rownum<3)
where data_object_id=9
order by 1);
commit;
update app_sequence set a_next_value=a_next_value+1 where app_type=47;
commit;
end;

This block was much faster as I could accomplish the same functionality using a Single Statement. It ran in less than a minute.

Another challenge, I faced here was that once this code was submitted, the block failed to compile as INLINE Views, dense_rank and CASE statement are not supported in 8i. As a workaround, a parameterized view was created as under :

create or replace view in_lt_vw as
select
(select a_next_value+1
from app_sequence where app_type=sys_context('userenv','client_info')) a_next_value,
dense_rank() over (order by r_id) r_id, b_part_id, object_name,
(case when data_object_id in (1,2,3) then 'OTT'
else case when data_object_id in (4,5) then 'FF'
else case when data_object_id in (6,7,8) then 'SSE'
else case when data_object_id = 9 and b_part_id=1 then 'N'
else 'NZ' end end end end) a_temporary
from
( select a_cdr.rowid r_id, 1 b_part_id, a_cdr.*
from a_cdrwhere data_object_id in (1,2,3,4,5,6,7,8)
union
select a_cdr.rowid r_id, r_num b_part_id, a_cdr.*
from a_cdr, (select rownum r_num from all_objects where rownum<3)
where data_object_id=9order by 1);

And the block was modified as :

declare
anext number:=47;
begin
dbms_application_info.set_client_info(anext);
insert /*+ append */ into in_lt
select * from in_lt_vw;
commit;
update app_sequence set a_next_value=a_next_value+1
where app_type=47;
commit;
end;


Dense_Rank function was used to populate numbers ranging from 1 to total_number of records into a_record_id column. This could have been achieved using rownum function but the challenge was for data_object_id=9 where same value is to be repeated. Hence, this was achieved using dense_rank function.

If anyone has a better solution, please reply..

Happy reading....