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