I want to process a large amount of records and I am using bulk collect instruction to fetch the data into a table of records. However, I am sure there is a more adequate way to achieve what I am looking for. Here is the code:
FUNCTION GET_STORE_ITEMS(I_store IN STORE.STORE%TYPE,
I_item_status IN WIN_STORE.STATUS%TYPE,
----------- output ------------
O_item_data OUT NB_TAB_ITEM_DETAIL,
----------- error -------------
O_error_message OUT VARCHAR2)
RETURN BOOLEAN IS
-- ----------------------------------
-- VARIABLES --
-- ----------------------------------
L_program VARCHAR2(100) := 'NB_ITEM_INFO_SQL.GET_ITEM_DETAIL';
L_error_message VARCHAR2(500) := '';
--
-- ----------------------------------
-- CURSORS --
-- ----------------------------------
-- 003 begin
CURSOR C_get_info IS
-- #004 - begin
SELECT NB_REC_ITEM_DETAIL(sku, desc_up, system_ind, status, division, div_name, group_no, group_name,
dept, dept_name, class, class_name, subclass, sub_name, nb_var_weight,
(SELECT upc as primary_ean
FROM upc_ean u
WHERE u.sku = tab.sku
AND u.primary_upc_ind = 'Y'
AND rownum = 1),
CAST( multiset(SELECT u.upc
FROM upc_ean u
WHERE u.sku = tab.sku
AND u.primary_upc_ind <> 'Y')
AS NB_TAB_ITEM_EAN )
)
FROM (SELECT info.sku,
info.desc_up,
info.nb_var_weight,
info.system_ind,
info.status,
div.division,
div.div_name,
c.group_no,
b.group_name,
info.dept,
c.dept_name,
info.class,
a.class_name,
info.subclass,
e.sub_name
FROM (SELECT w.store,
w.sku,
nvl(wa.nb_var_weight, 'N') nb_var_weight,
w.status,
ts.desc_up,
ts.system_ind,
ts.dept,
ts.class,
ts.subclass
FROM win_store w,
win_attributes wa,
desc_look ts,
deps d
WHERE w.sku = wa.sku
AND w.store = I_store
AND wa.sku = ts.sku
-- AND (w.status = I_item_status OR I_item_status IS NULL)
AND d.dept = ts.dept
UNION ALL
SELECT st.store,
st.sku,
'N' nb_var_weight,
st.status,
ts.desc_up,
ts.system_ind,
ts.dept,
ts.class,
ts.subclass
FROM rag_skus_st st,
desc_look ts,
deps d
WHERE st.store = I_store
AND ts.sku = st.sku
-- AND (st.status = I_item_status OR I_item_status IS NULL)
AND d.dept = ts.dept
UNION ALL
SELECT ps.store,
ps.pack_no,
'N' nb_var_weight,
ps.status,
ts.desc_up,
ts.system_ind,
ts.dept,
ts.class,
ts.subclass
FROM packstore ps,
desc_look ts,
deps d
WHERE ps.store = I_store
AND ps.pack_no = ts.sku
-- AND (ps.status = I_item_status OR I_item_status IS NULL)
AND d.dept = ts.dept
)info,
deps c,
groups b,
division div,
class a,
subclass e
WHERE c.dept = info.dept
AND b.group_no = c.group_no
AND div.division = b.division
AND a.dept = info.dept
AND a.class = info.class
AND e.dept = info.dept
AND e.class = info.class
AND e.subclass = info.subclass
) tab;
--
BEGIN
--
-- Instantiate output structure object.
--
O_item_data := NB_TAB_ITEM_DETAIL();
--
OPEN C_get_info;
FETCH C_get_info BULK COLLECT INTO O_item_data;
CLOSE C_get_info;
--
RETURN TRUE;
--
EXCEPTION
--
WHEN OTHERS THEN
--
IF C_get_info%ISOPEN THEN
--
CLOSE C_get_info;
--
END IF;
--
O_error_message := SQL_LIB.CREATE_MSG('PACKAGE_ERROR',
SQLERRM,
L_program,
TO_CHAR(SQLCODE));
RETURN FALSE;
--
--
END GET_STORE_ITEMS;
At the worst case, the number of rows returned is ~1.4 million. Is there any way I can boost the performance of the query for such a large number of records? Or should i try a different approach when fetching the data to the output structure? Thanks in advance.
I actually solved the problem by adding pagination. As Jon Heller said in the comments, this is not a performance issue related to PL/SQL code. Due to the large number of records returned by the function, there is a need to control how much data it is supposed to return when called. Based on that, I improved the cursor to limit the returned subset of data and optimized the query in an attempt to reduce its cost. I also introduced pagination and defined a limit of records of 1000.
FUNCTION GET_STORE_ITEMS(I_store IN STORE.STORE%TYPE,
I_item_status IN WIN_STORE.STATUS%TYPE,
--------- pagination ----------
I_offset IN PLS_INTEGER DEFAULT NULL,
I_limit IN PLS_INTEGER DEFAULT NULL,
O_total_reg OUT PLS_INTEGER,
----------- output ------------
O_item_data OUT SYS_REFCURSOR,
----------- error -------------
O_error_message OUT VARCHAR2)
RETURN BOOLEAN IS
--
L_error_message VARCHAR2(500) := '';
L_skip PLS_INTEGER := I_offset;
L_take PLS_INTEGER := I_limit;
L_limit_take PLS_INTEGER := 1000;
L_tab_item_detail NB_TAB_ITEM_DETAIL := NB_TAB_ITEM_DETAIL();
--
CURSOR C_get_total_rec IS
--
SELECT COUNT(1)
FROM (
-- based on the main query --
-- ...
);
-- Get store items information.
CURSOR C_get_info IS
--
SELECT NB_REC_ITEM_DETAIL(sku => sku,
item_desc => desc_up,
system_ind => system_ind,
status => status,
div_no => division,
div_desc => div_name,
group_no => group_no,
group_desc => group_name,
dept_no => dept,
dept_desc => dept_name,
class_no => class,
class_desc => class_name,
subclass_no => subclass,
subclass_desc => sub_name,
var_weight_ind => nb_var_weight,
primary_ean => (SELECT upc as primary_ean
FROM upc_ean u
WHERE u.sku = tab.sku
AND u.primary_upc_ind = 'Y'
AND rownum = 1)
)
FROM ( -- main query --
-- ...
) tab
WHERE ROWNUM <= L_skip + L_take;
--
BEGIN
-- get the total number of records.
OPEN C_get_total_rec;
FETCH C_get_total_rec INTO O_total_reg;
CLOSE C_get_total_rec;
-- Set the pagination parameters to get all records by default.
IF L_skip IS NULL OR L_skip < 0 THEN
--
L_skip := 0; -- setting offset to 0
--
END IF;
--
IF L_take > L_max_take OR L_take < 0 OR L_take IS NULL THEN
--
L_take := L_max_take;
--
END IF;
--
OPEN C_get_info;
FETCH C_get_info BULK COLLECT INTO L_tab_item_detail LIMIT 1000;
CLOSE C_get_info;
--
-- Open item data sys_refcursor.
--
OPEN O_item_data for
--
SELECT *
FROM TABLE(L_tab_item_detail);
--
RETURN TRUE;
--
EXCEPTION
--
WHEN OTHERS THEN
--
-- ...
--
RETURN FALSE;
--
--
END GET_STORE_ITEMS;
Thank you all!