How to create index to improve performance of an aggregate function that creates a table in oracle

Question 1

I am creating an Oracle ORDS API using APEX_JSON. I recently started using bind variables instead of string concatenation using ||. I am trying to use an in clause in my where condition.

The problems begin here. The field I need to have on the left side of in is a number and the parameter to my stored procedure needs to be varchar2 as it is a comma seperated list of numbers.

Example (edited for brevity)

CREATE OR REPLACE PROCEDURE GET_CATEGORYPRODS (
 PCATEGORYID IN NUMBER,
 COMMASEPPRODUCTIDS IN VARCHAR2
) AS
l_cursor SYS_REFCURSOR;
v_stmt_str STRING(5000);
v_name NUMBER; --PRODUCT.NAME%TYPE;
v_displayorder NUMBER; --PRODUCTCATEGORY%TYPE;
BEGIN
 v_stmt_str := 'SELECT 
 P.NAME, 
 PC.DISPLAYORDER
FROM 
 PRODUCT P
INNER JOIN
 PRODUCTCATEGORY PC
ON P.PRODUCTID = PC.PRODUCTID
WHERE 
 PC.CATEGORYID := :CATEGORYID
AND
 (P.PRODUCTID IN (SELECT * FROM TABLE(STRING_TO_TABLE_NUM(:COMMASEPPRODUCTIDS))) -- PREVIOUSLY WHERE || OCCURRED
 OR (:COMMASEPPRODUCTIDS IS NULL))';
s_counter := 0;
OPEN l_cursor FOR v_stmt_str
 USING pcategoryid, commasepproductids, commasepproductids;
FETCH l_cursor INTO
 v_productid,
 v_displayorder;
APEX_JSON.OPEN_ARRAY;
LOOP
 EXIT WHEN l_cursor%notfound;
 apex_json.open_object;
 apex_json.write('ProductID', v_productid);
 apex_json.write('DisplayOrder', v_displayorder);
 apex_json.close_object;
END LOOP;
apex_json.close_all;
END GET_CATEGORYPRODS;

Sample of parameters '97187,142555,142568,48418,43957,44060,45160,45171,333889,333898'

To handle this problem, I created an aggregate function that takes in a string, splits on the commas, and pipes the row to a custom type.

Custom Type

create or replace type tab_number is table of number;

Aggregate Function

create or replace FUNCTION string_to_table_num (
 p VARCHAR2
)
 RETURN tab_number
 PIPELINED IS
BEGIN
 FOR cc IN (SELECT rtrim(regexp_substr(str, '[^,]*,', 1, level), ',') res
 FROM (SELECT p || ',' str FROM dual)
 CONNECT BY level <= length(str) 
 - length(replace(str, ',', ''))) LOOP
 PIPE ROW(lower(cc.res));
 END LOOP;
 
END;

The query slowed down significantly. I figured some optimization was needed but I had never done any sort of optimization before. After some research, I found EXPLAIN PLAN and ran it on the orginal query. I couldn't get a good answer because of the bind variables, so I decided to run it on the aggregate function.

EXPLAIN PLAN QUERIES

explain plan for select * from TABLE(string_to_table_num('97187,142555,142568,48418,43957,44060,45160,45171,333889,333898'));
SELECT * 
FROM TABLE(DBMS_XPLAN.DISPLAY);

When I ran EXPLAIN PLAN for the aggregate function the results were:

Plan hash value: 127161297
 
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| STRING_TO_TABLE_NUM | 8168 | 16336 | 29 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

As I stated before, I am a noob to analyzing and optimizing queries, but 8168 Rows and 16336 bytes seems to be a lot for such a simple function. I looked into it, and found that the problem may be the lack of indexing of the pipelined table. I tried to add an index to the type tab_number but it turned it into a PL/SQL object that needed to be declared in a query, not a function.

I am pretty lost with this one. If you have any suggestions for any of the scenarios I mentioned, I am all ears. Thanks in advance.

EDIT After following the steps in Balazs's answer below, I ran EXPLAIN PLAN for both the aggregate function and the select from my procedure.

Aggregate Function

Plan hash value: 229973419
 
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| F_CONVERT2 | 8168 | 16336 | 29 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Select Function

Plan hash value: 1690769838
 
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 2790 | 972 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS OUTER | | 65 | 30225 | 972 (0)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 65 | 27950 | 842 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 65 | 27365 | 777 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| PRODUCTCATEGORY | 65 | 845 | 712 (0)| 00:00:01 |
|* 6 | INDEX SKIP SCAN | SYS_C0012982 | 65 | | 709 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID | PRODUCTNEW | 1 | 408 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | SYS_C0013161 | 1 | | 0 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | SYS_C0012993 | 1 | 9 | 1 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | IDX_URLMAPPER_PRODUCTID_FRIENDLYURL | 1 | 35 | 2 (0)| 00:00:01 |
|* 11 | COLLECTION ITERATOR PICKLER FETCH | F_CONVERT2 | 1 | 2 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
 1 - filter(:COMMASEPPRODUCTIDS IS NULL OR EXISTS (SELECT 0 FROM TABLE() "KOKBF0ドル" WHERE VALUE(KOKBF$)=:B1))
 6 - access("pc"."CATEGORYID"=TO_NUMBER(:PCATEGORYID))
 filter("pc"."CATEGORYID"=TO_NUMBER(:PCATEGORYID))
 7 - filter("p"."PUBLISHED"=1 AND "p"."DELETED"=0)
 8 - access("p"."PRODUCTID"="pc"."PRODUCTID")
 9 - access("p"."PRODUCTID"="pm"."PRODUCTID"(+))
 10 - access("p"."PRODUCTID"="um"."PRODUCTID"(+))
 11 - filter(VALUE(KOKBF$)=:B1)

Do these numbers seem to be within a reasonable range?

EDIT 2

create or replace PROCEDURE GET_CATEGORYPRODS2 
(
 COMMASEPPRODUCTIDS IN VARCHAR2 
, COMMASEPPRODUCTSKUS IN VARCHAR2 
, PCATEGORYID IN NUMBER 
, SORTBY IN VARCHAR2 
)
....
IF sortby IS NULL OR sortby = 'null' OR sortby = '' THEN
 v_sortby := '"p".Discontinued, "pc".DisplayOrder ';
 ELSIF sortby = 'PriceAscending' THEN
 v_sortby := '"p".discontinued, "p".price ';
 ELSIF sortby = 'PriceDescending' THEN
 v_sortby := '"p".discontinued, "p".price DESC ';
 ELSIF sortby = 'Name' THEN
 v_sortby := '"p".discontinued, "p".name ';
 ELSE
 v_sortby := '"p".discontinued, "pc".displayorder ';
 END IF;
....

I was also using a bind variable to add this to the end of the query.

Question 2

Try it like this:

create or replace type tab_number is table of number;
create or replace FUNCTION string_to_table_num (p VARCHAR2) RETURN tab_number
 res tab_nubmer;
BEGIN
 select regexp_substr(p,'[^,]+', 1, level) 
 bulk collect into res
 from dual
 connect by regexp_substr(p, '[^,]+', 1, level) is not null;
 return res;
END;

and use it similar to this:

 ids tab_nubmer := string_to_table_num(COMMASEPPRODUCTIDS);
begin
 OPEN l_cursor FOR 
 SELECT 
 P.NAME, 
 PC.DISPLAYORDER
 FROM PRODUCT P
 INNER JOIN PRODUCTCATEGORY P ON P.PRODUCTID = PC.PRODUCTID
WHERE PC.CATEGORYID = CATEGORYID
 AND P.PRODUCTID MEMBER of ids;

You don't need dynamic SQL.

An ORDER BY can be done like this:

ORDER BY "p".discontinued,
 CASE sortby 
 WHEN 'PriceAscending' THEN "p".price
 WHEN 'PriceDescending' THEN "p".price DESC
 WHEN 'Name' THEN "p".name
 ELSE "pc".DisplayOrder -- includes 'null' or ''
 END

Question 3

oh wow i like that a lot better than the function I was using. I appreciate the tip! If i were to want to sort on a dynamic value, would that be possible without dynamic SQL? Say if there was SORTBY IN VARCHAR2 and it went through an IF loop. I'll put an example in my question.

Question 4

The list of product ids can also be null, I took it out of the original question because I did not think it could be done without dynamic SQL. Is there a way to handle that without dynamic SQL?

Question 5

@Josh, see my update

Question 6

I tried it that way, but it did not like "p".price DESC for some reason. It said invalid expression. But regardless, that is a small piece of the puzzle that i can work out at a later date.

Question 7

It actually gave me a ORA-00905: missing keyword and underlined DESC as where the error is occurring. Not an invalid expression error. My apologies.

Question 8

8168 rows and 16336 bytes are just default values.

If you want more realistic values, you can use ASSOCIATE STATISTICS.

Here is an example:

https://oracle-base.com/articles/misc/pipelined-table-functions

Without ASSOCIATE STATISTICS:

CREATE OR REPLACE FUNCTION get_tab_ptf (p_cardinality IN INTEGER DEFAULT 1)
 RETURN t_tf_tab PIPELINED AS
BEGIN
 FOR i IN 1 .. 10 LOOP
 PIPE ROW (t_tf_row(i, 'Description for ' || i));
 END LOOP;
 RETURN;
END;
/
SET AUTOTRACE TRACE EXPLAIN
-- Return 10 rows.
SELECT *
FROM TABLE(get_tab_ptf(10));
Execution Plan
----------------------------------------------------------
Plan hash value: 822655197
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 8 (0)| 00:02:19 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| GET_TAB_PTF | 8168 | 16336 | 8 (0)| 00:02:19 |
-------------------------------------------------------------------------------------------------
SET AUTOTRACE OFF

With ASSOCIATE STATISTICS:

CREATE OR REPLACE TYPE t_ptf_stats AS OBJECT (
 dummy INTEGER,
 
 STATIC FUNCTION ODCIGetInterfaces (
 p_interfaces OUT SYS.ODCIObjectList
 ) RETURN NUMBER,
 STATIC FUNCTION ODCIStatsTableFunction (
 p_function IN SYS.ODCIFuncInfo,
 p_stats OUT SYS.ODCITabFuncStats,
 p_args IN SYS.ODCIArgDescList,
 p_cardinality IN INTEGER
 ) RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY t_ptf_stats AS
 STATIC FUNCTION ODCIGetInterfaces (
 p_interfaces OUT SYS.ODCIObjectList
 ) RETURN NUMBER IS
 BEGIN
 p_interfaces := SYS.ODCIObjectList(
 SYS.ODCIObject ('SYS', 'ODCISTATS2')
 );
 RETURN ODCIConst.success;
 END ODCIGetInterfaces;
 STATIC FUNCTION ODCIStatsTableFunction (
 p_function IN SYS.ODCIFuncInfo,
 p_stats OUT SYS.ODCITabFuncStats,
 p_args IN SYS.ODCIArgDescList,
 p_cardinality IN INTEGER
 ) RETURN NUMBER IS
 BEGIN
 p_stats := SYS.ODCITabFuncStats(NULL);
 p_stats.num_rows := p_cardinality;
 RETURN ODCIConst.success;
 END ODCIStatsTableFunction;
END;
/
ASSOCIATE STATISTICS WITH FUNCTIONS get_tab_ptf USING t_ptf_stats;

And the result:

SET AUTOTRACE TRACE EXPLAIN
SELECT *
FROM TABLE(get_tab_ptf(p_cardinality => 10));
Execution Plan
----------------------------------------------------------
Plan hash value: 822655197
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 20 | 8 (0)| 00:02:19 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| GET_TAB_PTF | 10 | 20 | 8 (0)| 00:02:19 |
-------------------------------------------------------------------------------------------------
SELECT *
FROM TABLE(get_tab_ptf(p_cardinality => 10000));
Execution Plan
----------------------------------------------------------
Plan hash value: 822655197
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 20000 | 8 (0)| 00:02:19 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| GET_TAB_PTF | 10000 | 20000 | 8 (0)| 00:02:19 |
-------------------------------------------------------------------------------------------------
SET AUTOTRACE OFF

Question 9

Thank you for clearing that up. To use the associative statistics with my query, I would run ASSOCIATE STATISTICS WITH FUNCTIONS string_to_table_num USING t_ptf_stats; and then just run the query while AUTOTRACE is set to TRACE?

Question 10

@Josh AUTOTRACE is used in the above example only for demonstration purpose, to show the execution plan. You can just run your query and use select * from table(dbms_xplan.display_cursor); to display the execution plan of the last statement.

Question 11

I edited my question to include the results from the EXPLAIN PLAN with the associate statistics. Do they seem to be within an acceptable range?

score 1 · Accepted Answer · 2020-07-31 21:18:35Z

Try it like this:

create or replace type tab_number is table of number;
create or replace FUNCTION string_to_table_num (p VARCHAR2) RETURN tab_number
 res tab_nubmer;
BEGIN
 select regexp_substr(p,'[^,]+', 1, level) 
 bulk collect into res
 from dual
 connect by regexp_substr(p, '[^,]+', 1, level) is not null;
 return res;
END;

and use it similar to this:

 ids tab_nubmer := string_to_table_num(COMMASEPPRODUCTIDS);
begin
 OPEN l_cursor FOR 
 SELECT 
 P.NAME, 
 PC.DISPLAYORDER
 FROM PRODUCT P
 INNER JOIN PRODUCTCATEGORY P ON P.PRODUCTID = PC.PRODUCTID
WHERE PC.CATEGORYID = CATEGORYID
 AND P.PRODUCTID MEMBER of ids;

You don't need dynamic SQL.

An ORDER BY can be done like this:

ORDER BY "p".discontinued,
 CASE sortby 
 WHEN 'PriceAscending' THEN "p".price
 WHEN 'PriceDescending' THEN "p".price DESC
 WHEN 'Name' THEN "p".name
 ELSE "pc".DisplayOrder -- includes 'null' or ''
 END

oh wow i like that a lot better than the function I was using. I appreciate the tip! If i were to want to sort on a dynamic value, would that be possible without dynamic SQL? Say if there was SORTBY IN VARCHAR2 and it went through an IF loop. I'll put an example in my question.
The list of product ids can also be null, I took it out of the original question because I did not think it could be done without dynamic SQL. Is there a way to handle that without dynamic SQL?
I tried it that way, but it did not like "p".price DESC for some reason. It said invalid expression. But regardless, that is a small piece of the puzzle that i can work out at a later date.
It actually gave me a ORA-00905: missing keyword and underlined DESC as where the error is occurring. Not an invalid expression error. My apologies.

Stack Exchange Network

How to create index to improve performance of an aggregate function that creates a table in oracle

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Hot Network Questions

How to create index to improve performance of an aggregate function that creates a table in oracle

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Related

Hot Network Questions