Skip to content

Functions in Select and Where

It is possible to embed functions within SQL statements. Even though this is a nice feature, this need to be done with careful consideration, since SQL PLSQL context switching is performance wise expensive. Use of function calls in the where clause could be even more expensive because:

  • Function call might be executed more often than expected (for each row in the data source)
  • Function call may disable the use of an index, resulting in, un-necessary full table scans
  • Prevent the optimizer from assigning valid cardinality or selectivity estimates which, can in turn, effect the overall execution plan and the join method

Limit the use of PLSQL Methods in Select List

Example 1

Select statement with a API call.

SELECT hq.company_id company_id,
       hq.emp_no emp_no,
       Company_Emp_API.Get_Person_Id(company_id, emp_no) person_id,
       hq.absence_type_id description_one,
       hq.request_plan_id description_two,
       hq.half_day_from description_three,
       hq.half_day_to description_four,
       hq.objstate description_six,
       TO_NUMBER(hq.hours) description_seven,
       TO_CHAR(hq.objkey) objkey
FROM   holiday_req_plan_uxx hq;

Solution

This can be rewritten by taking off the method call in the SELECT statement. Then write a join with COMPANY_EMP_TAB to get the person ID from that.

SELECT hq.company_id company_id,
       hq.emp_no emp_no,
       ce.person_id person_id,
       hq.absence_type_id description_one,
       hq.request_plan_id description_two,
       hq.half_day_from description_three,
       hq.half_day_to description_four,
       hq.objstate description_six,
       TO_NUMBER(hq.hours) description_seven,
       TO_CHAR(hq.objkey) objkey
FROM   holiday_req_plan_uxx hq,
       company_emp_tab      ce
WHERE  hq.company_id = ce.company
AND    hq.emp_no = ce.employee_id;

Example 2

Original Cursor

PROCEDURE Plsql_In_Select (
   contract_ IN VARCHAR2,
   part_no_  IN VARCHAR2 )
IS   
   CURSOR get_inv_purch_receipt(contract_ IN VARCHAR2, part_no_ IN VARCHAR2) IS
      SELECT   polp.order_no, polp.line_no, polp.release_no,
               Purchase_Order_API.Get_Vendor_No(pr.order_no) vendor_no,
               polp.note_id
      FROM     purchase_order_line_part polp, purchase_receipt pr
      WHERE    pr.order_no   = polp.order_no
      AND      pr.line_no    = polp.line_no
      AND      pr.release_no = polp.release_no
      AND      polp.contract = contract_
      AND      polp.part_no  = part_no_;
BEGIN
   FOR rec_ IN get_inv_purch_receipt(contract_, part_no_) LOOP
      -- do something
      Purchase_Order_API.Modify_Revision(rec_.order_no);
      -- do something more
   END LOOP;
END Plsql_In_Select;

Solution

The above can be rewritten by taking off the method call in the SELECT statement. Instead the method call can be placed inside plsql code, in order to eliminate the context switch.

PROCEDURE Plsql_Moved_From_Select (
   contract_ IN VARCHAR2,
   part_no_  IN VARCHAR2 )
IS
   vendor_no_ VARCHAR2(30);
   CURSOR get_inv_purch_receipt(contract_ IN VARCHAR2, part_no_ IN VARCHAR2) IS
      SELECT   polp.order_no, polp.line_no, polp.release_no,
               polp.note_id
      FROM     purchase_order_line_part polp, purchase_receipt pr
      WHERE    pr.order_no   = polp.order_no
      AND      pr.line_no    = polp.line_no
      AND      pr.release_no = polp.release_no
      AND      polp.contract = contract_
      AND      polp.part_no  = part_no_;
BEGIN
   FOR rec_ IN get_inv_purch_receipt(contract_, part_no_) LOOP
      vendor_no_ := Purchase_Order_API.Get_Vendor_No(rec_.order_no);
      -- do something
      Purchase_Order_API.Modify_Revision(rec_.order_no);
      -- do something more
   END LOOP;
END Plsql_Moved_From_Select;

Limit the use of PLSQL methods in the WHERE clause

Example

PROCEDURE Plsql_In_Where (
   contract_ IN VARCHAR2,
   part_no_  IN VARCHAR2 )
IS
   CURSOR get_vat_no IS  
      SELECT *
      FROM   supplier_info_vat_tab
      WHERE  LENGTH(vat_no) = 11
      AND    Supplier_Info_API.Get_Country_Code(supplier_id) = 'BE'
      FOR    UPDATE OF vat_no;
BEGIN
   FOR rec_ IN get_vat_no LOOP
      rec_.vat_no := 'BE0' || substr(rec_.vat_no, 3);
      Modify___(rec_);
   END LOOP;
END Plsql_In_Where;

Solution

Move the function call from cursor into PLSQL code as below

PROCEDURE Plsql_Move_From_Where (
   contract_ IN VARCHAR2,
   part_no_  IN VARCHAR2 )
IS   
   CURSOR get_vat_no IS  
      SELECT *
      FROM   supplier_info_vat_tab
      WHERE  LENGTH(vat_no) = 11
      FOR    UPDATE OF vat_no;
BEGIN
   FOR rec_ IN get_vat_no LOOP
      IF (Supplier_Info_API.Get_Country_Code(rec_.supplier_id) = 'BE') THEN
         rec_.vat_no := 'BE0' || substr(rec_.vat_no, 3);
         Modify___(rec_);
      END IF;
   END LOOP;
END Plsql_Move_From_Where;

Limit the use of PLSQL methods in VIEWS

Often, it is very easy to place stored functions in the private base method views. This will lead to ”large”, also known as ”fat” views. It is better to place the function calls as columns in the client than placing them in views of the logical unit. Since the views might be taken from anywhere, and very often from the client, the rules from above should be considered when designing views. Please note that the client server communication model in Foundation1 is constructed to use ”thin base views”.

  • In where clauses of view definitions Having functions on column values in the WHERE-clause will dramatically affect the performance, when no indexes may be used and a query can lead to a full table scans in the database. If security aspects are included in the (base) view, try to include it with a join or sub select rather than a PLSQL method.
  • In key columns in the base view definition Using stored functions in key columns will always lead to performance bottlenecks for logical units containing normal amount of data, even if queries are made on "key columns".
  • In base views When using many functions in the view definition, this extra work must be made, even for the operations when the view is used inside the implementation of the base methods. This will of course affect performance. A superior way is to define the function at the client side as a pseudo-column