I have a large Procedure which passes up to 60 arguments; most of these arguments are optional - the exception being "ad_from date" and "ad_to date".
We are contemplating rewriting the Procedure using dynamic SQL to only add SQL to the "Where" clause when an argument is present.
Here is my procedure:
CREATE PROCEDURE fw_ticket_settle_query_detail
(as_bin_number IN varchar2,
ad_bill_of_lading_date IN date,
as_bill_of_lading_number IN varchar2,
as_business_type IN varchar2,
as_commodity_brand IN varchar2,
as_commodity_group IN varchar2,
as_commodity_id IN varchar2,
as_commodity_product IN varchar2,
an_contract_id IN number,
as_currency_code IN varchar2,
ac_date_type IN char,
as_delivery_sheet IN varchar2,
as_destination_id IN varchar2,
as_field_barn_id IN varchar2,
ad_from_date IN date,
ac_include_unapplied IN char,
as_last_product_hauled IN varchar2,
as_location_id IN varchar2,
as_lot_no IN varchar2,
as_name_address_filter IN varchar2,
as_name_address_id IN varchar2,
as_origin_id IN varchar2,
as_origin_destination_city IN varchar2,
as_payment_indicator_code IN varchar2,
as_pc_number IN varchar2,
as_pricing_type_code IN varchar2,
as_producer_id IN varchar2,
as_product_no IN varchar2,
as_relationship IN varchar2,
as_seal_number IN varchar2,
as_settlement_no IN varchar2,
as_ship_from_id IN varchar2,
as_ship_mode_code IN varchar2,
as_ship_to_id IN varchar2,
as_smartsoft_user_name IN varchar2,
as_spot_ticket_contract IN char,
an_ticket_number IN varchar2,
an_ticket_no IN varchar2,
as_ticket_owner_id IN varchar2,
as_ticket_reference IN varchar2,
as_ticket_status IN varchar2,
as_ticket_type_code IN char,
ad_to_date IN date,
as_tow_id IN varchar2,
as_vehicle_id IN varchar2,
as_miscfield1 IN varchar2,
as_miscfield2 IN varchar2,
as_miscfield3 IN varchar2,
as_miscfield4 IN varchar2,
as_miscfield5 IN varchar2,
as_miscfield6 IN varchar2,
as_miscfield7 IN varchar2,
as_miscfield8 IN varchar2,
as_miscfield9 IN varchar2,
as_miscfield10 IN varchar2,
as_miscfield11 IN varchar2,
as_miscfield12 IN varchar2,
as_miscfield13 IN varchar2,
as_miscfield14 IN varchar2,
as_miscfield15 IN varchar2,
an_page_no IN number,
an_page_size IN number,
result_set IN
OUT PagingUtility.PagedResultType,
order_by IN long
)
I use a Global Temporary table to contain the results:
INSERT INTO gtt_ticket_settle_query_detail
SELECT ...
The "From" clause looks like this:
FROM fw_ticket_settle_applied_v
I created the above view to minimize the amount of data that the Procedure will need to process.
I have coded the "Where" clause like this:
WHERE (as_commodity_brand IS NULL OR
commodity_brand = as_commodity_brand
)
AND (as_commodity_group IS NULL OR
commodity_group = as_commodity_group
)
I would rewrite the above SQL as dynamic SQL like this:
IF as_commodity_brand IS NOT NULL THEN
SQL_STRING := SQL_STRING || ' AND commodity_brand = as_commodity_brand'
END IF;
IF as_commodity_group IS NOT NULL THEN
SQL_STRING := SQL_STRING || ' AND commodity_group = as_commodity_group'
END IF;
After processing all of the arguments I would execute the SQL_STRING as:
EXEC IMMEDIATE SQL_STRING;
Will I gain a significant performance increase using the above method??
How does the SQL Optimizer create a plan for dynamic SQL; do I lose the benefits of having (for example) indexes on my "from_date" and "to_date" columns??
Are there other considerations I am overlooking?
Thanks
Murray Sobol