Making your report faster

2009-06-25T20:09:00.000-07:00

First of all, i want to introduce an easy method to make our reporting faster then usual. This method is changing from view to piperow function, here is the example :
we have view like this :

CREATE VIEW v_table
AS
SELECT *
FROM table_1 t1, table_2 t2, table_3 t3, table_4 t4
WHERE t1.id1 = t2.id1 AND t2.id2 = t3.id2 AND t3.id3 = t4.id3

Sometimes, if we use this view to show alot of record (+1000), it will take a long time to process. But, we can make this process simple and need less resource by using a piperow function. Assume that we use several parameter for displaying the data.

CREATE OR REPLACE FUNCTION adm_cc.fv_table (
i_t_tel69_submit_request_id IN NUMBER,
i_p_network_type_id IN NUMBER
)
RETURN tb_v_table PIPELINED
AS
TYPE c_ref_cursor IS REF CURSOR;

TYPE rc_ref_cursor IS RECORD (
t_tel69_request_id NUMBER (12),
t_subscription_id NUMBER (8),
t_nw_service_line_no_id NUMBER (9),
network_id NUMBER (8),
tipe_jaringan VARCHAR2 (32 BYTE),
p_organization_id NUMBER (3),
p_address_id NUMBER (8),
pt_payment_type_id NUMBER (2)
);

main_cursor c_ref_cursor;
prc_work rc_ref_cursor;
prc_obj field_v_table;
ls_where VARCHAR2 (4000);
BEGIN
prc_obj := field_v_table (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
ls_where := NULL;

IF i_t_tel69_submit_request_id IS NOT NULL
THEN
ls_where :=
ls_where
|| ' and t1.t_tel69_submit_request_id >= '
|| i_t_tel69_submit_request_id;
END IF;

IF i_p_network_type_id IS NOT NULL
THEN
ls_where :=
ls_where || ' and t2.p_network_type_id = ' || i_p_network_type_id;
END IF;

OPEN main_cursor FOR 'select * '
|| ' from table_1 t1, '
|| ' table_2 t2,'
|| ' table_3 t3,'
|| ' table_4 t4'
|| ' where '
|| ' t1.id1 = t2.id1 '
|| ' and t2.id2 = t3.id2 '
|| ' and t3.id3 = t4.id3 '
|| vwhere;

LOOP
FETCH main_cursor
INTO prc_work;

EXIT WHEN main_cursor%NOTFOUND;
prc_obj.t_tel69_submit_request_id := prc_work.t_tel69_submit_request_id;
prc_obj.t_subscription_request_id := prc_work.t_subscription_request_id;
prc_obj.t_nw_service_line_no_id := prc_work.t_nw_service_line_no_id;
prc_obj.network_id := prc_work.network_id;
prc_obj.tipe_jaringan := prc_work.tipe_jaringan;
prc_obj.p_organization_id := prc_work.p_organization_id;
prc_obj.p_address_id := prc_work.p_address_id;
prc_obj.pt_payment_type_id := prc_work.pt_payment_type_id;
PIPE ROW (prc_obj);
END LOOP;

RETURN;
END;

create type for handle the data,

CREATE OR REPLACE TYPE "ADM_CC"."FIELD_V_TABLE" AS OBJECT (
t_tel69_submit_request_id NUMBER (12),
t_subscription_request_id NUMBER (8),
t_nw_service_line_no_id NUMBER (9),
network_id NUMBER (8),
tipe_jaringan VARCHAR2 (32 BYTE),
p_organization_id NUMBER (3),
p_address_id NUMBER (8),
pt_payment_type_id NUMBER (2)
);

CREATE OR REPLACE TYPE adm_cc.tb_v_table AS TABLE OF field_v_table;

Oracle for beginner

Making your report faster