Optimizing Execution Dynamically :
You can apply the usage of dynamic SQL to make the SQL statement in such a way which optimizes the execution by combining the hints into a SQL statement behaving dynamically and it will show you the changes in the hints upon your current statistics without recompilation of database.
The following procedure uses a variable named My_hint which allow to the users to pass a hint option with the SELECT statement :
Code:
CREATE OR REPLACE PROCEDURE employees_query
(my_hint VARCHAR2) AS
TYPE cursor_tp IS REF CURSOR;
c cursor_tp;
BEGIN
OPEN c FOR 'SELECT ' || my_hint ||
' emp_id, emp_name, salary, job_id FROM employee WHERE emp_id = 9889';
END;
Bookmarks