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?