Query on View is running slower than direct query - in Oracle

Question

I tried creating view with parameters to get the data dynamically.
(I cant use WHERE condition as the select statement itself changes based on the parameters)

For this, I wrote a procedure which returns me the required data as oracle object type.

FUNCTION get_data(p_pk_id  NUMBER, p_tab_type VARCHAR2)
RETURN M_TYPE_DATA_TAB
AS
v_table_collection M_TYPE_DATA_TAB;
BEGIN

-- my sql query which will change based on the params

RETURN v_table_collection;
END;

and I run the select query as follows.

SELECT * FROM TABLE(get_data(12345, 'MYTAB'));

which gives me data in less than 1 sec.

for the same select statement I have created a view as

CREATE OR REPLACE VIEW my_view
AS SELECT * FROM TABLE(get_data(12345, 'MYTAB'));

if I query the view

SELECT * FROM my_view

it takes more than 6 sec to get the same data.

Any idea why there is that much big difference to query the same data.
Will the veiw take more time than a normal query?

Answer 1

The execution plan on each statement will give you more detail on what is happening. Try using some of the provided oracle tools for investigating what exactly is happening in each case.

Try doing a:

 SELECT/*+gather_plan_statistics*/ * FROM TABLE(get_data(12345, 'MYTAB'));

then do a:

 SELECT/*+gather_plan_statistics*/ * FROM my_view

These will give you the actual execution plan for the statements.

By the way, you will need select on the V_$SQL_PLAN and V_$SQL views to use the gather_plan_statistics as above.

Answer 2

Google for "explain plan", and check the execution plan for both statements. Without the complete function, it's difficult to say anything. But i'd assume that the optimizer does some "generic optimizing" on the view, and when you select from the view, that generic optimizing is used. When you select directly, without the view, the optimizer considers the internals of your function as well.

Try explain plan on different statements selecting from the view. Is the "internal part", that executes the function, the same in each case?