Friday, 8 July 2011

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));

2 comments:

Peter A. said...

Very good explanation!!

Vishal Pathak said...

Thanks Peter. Appreciation is always an encouragement :)