2

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() "KOKBF$0" 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.

0
1

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 
7
  • 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. – Josh Aug 1 '20 at 0:07
  • 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? – Josh Aug 1 '20 at 0:14
  • @Josh, see my update – Wernfried Domscheit Aug 1 '20 at 12:00
  • 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. – Josh Aug 3 '20 at 14:37
  • 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. – Josh Aug 3 '20 at 15:30
1

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
3
  • 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? – Josh Jul 31 '20 at 14:21
  • @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. – Balazs Papp Jul 31 '20 at 14:41
  • 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? – Josh Jul 31 '20 at 16:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.