The performance enhancing drug called Table function

Finally a post on APEX :)

I have seen a number of situations in development of both Oracle APEX (Interactive reports) and in Oracle BI Publisher where the developer has to implement complex functionality but he only has the freedom to write SQL statements and not PL SQL logic. Due to this he ends up writing the complex logic using ‘and clause’ and ‘or clause’.

Let me explain it by an example

I found the below query in a company which had some serious performance issues

All bind variables below are page items of APEX.

p_view  and p_category  are select lists in APEX.

The report is expected to behave differently for different values of these variables

SELECT st.assignment_number, st.assignment_name

  FROM some_table st

 -- View check starts here

WHERE  (   (:p_view = 'My Assignments' AND st.requestor = 'Requestor Role')

        OR (:p_view = 'Global Read-Only')

        OR (:p_view = 'Admin' AND st.typee <> 'Confidential')

       )

   --View check ends here

       -- Category check starts here

   AND (   (:p_category = 'Global Search')

        OR (    :p_category = 'Organization'

            AND (:p_bureau IS NULL OR :p_bureau = st.bureau_id)

            AND (:p_division IS NULL OR :p_division = st.division_id)

           )

       )                                           -- Category check ends here

So basically we end up producing the dynamic report with a bad plan and hence performance problems. We can clearly see that our query will always have all the predicates even when they might not be needed.

Again in one of my projects we had a requirement of running the same report from both APEX and BI Publisher. Since the report is the same so it makes sense that the same piece of code generates both of these reports as this helps in maintenance and bug fixing

Okie .. now lets jump into the solution

1.      Create  a row type which matches with the structure of the returned result set. So in our case we create the following type

CREATE OR REPLACE type rprt_type AS OBJECT (assignment_number number, assignment_name VARCHAR2 (300))

Now create a table of this type

CREATE OR REPLACE TYPE rprt_tbl_type AS TABLE OF rprt_type;

2.      Write a function that returns the newly created table type i.e. rprt_tbl_type

CREATE OR REPLACE FUNCTION interactive_rprt (

   p_view       VARCHAR2,

   p_category   VARCHAR2,

   p_bureau     VARCHAR2,

   p_division   VARCHAR2

)

   RETURN rprt_tbl_type PIPELINED

IS

   TYPE rc IS REF CURSOR;

   v_assignment_number   NUMBER;

   v_assignment_name     VARCHAR2 (200);

   l_rc                  rc;

   v_rprt_type           rprt_type        := NULL;

   v_query               VARCHAR2 (32767)

      := 'SELECT st.assignment_number, st.assignment_name

  FROM some_table st

 WHERE  ';

BEGIN

-- View check starts here

   IF p_view = 'My Assignments'

   THEN

      v_query := v_query || ' st.requestor = ''Requestor Role''';

   ELSIF p_view = 'Global Role'

   THEN

      v_query := v_query || ' 1 = 1 ';

   ELSIF p_view = 'Admin'

   THEN

      v_query := v_query || ' st.typee <> ''Confidential''';

   END IF;

   --View check ends here

       -- Category check starts here

   IF p_category = 'Global Search'

   THEN

      NULL;

   ELSIF p_category = 'Organization'

   THEN

      IF p_bureau IS NULL

      THEN

         NULL;

      ELSE

         v_query := v_query || ' and st.bureau_id = :p_bureau ';

      END IF;

      IF p_division IS NULL

      THEN

         NULL;

      ELSE
         v_query := v_query || ' and st.division_id = :p_division ';
      END IF;
   END IF;

-- Category check ends here

   IF p_category = 'Organization' AND p_bureau IS NOT NULL

   THEN

      OPEN l_rc FOR v_query USING p_bureau;

   END IF;

   IF p_category = 'Organization' AND p_division IS NOT NULL

   THEN

      OPEN l_rc FOR v_query USING p_division;

   END IF;

   LOOP

      FETCH l_rc

       INTO v_assignment_number, v_assignment_name;

      v_rprt_type := rprt_type (v_assignment_number, v_assignment_name);

      EXIT WHEN l_rc%NOTFOUND;

      PIPE ROW (v_rprt_type);

   END LOOP;

   CLOSE l_rc;

   RETURN; 
END interactive_rprt;

The above function dynamically creates a query which is lighter than the original query and stores it in a variable called v_query. The use of bind variables here allows us to pass values into the SQL engine (when the dynamic query is executed) and it also helps performance because only the soft parse of the query happens at run time if this query exists in the library cache

The use of pipelining the output again helps performance as the report does not wait for the entire output to be generated before displaying it

3.      Now call this function from the desired place (Like APEX or BI Publisher) using table function

SELECT assignment_number, assignment_name

  FROM TABLE (interactive_rprt (:p_view, :p_category, :p_bureau, :p_division));