I have a view which is accessed via DBLink in Oracle. This view is made up of different tables. Now we have to execute queries on this view. Example:
Select * from Viewname@dblink where columnname='somevalue';
Now this view contains lakhs and crores of data. Even this simple select statement with a where clause takes a long time. This query consumes more CPU utilization.
We received some suggestions such as put the query in the functions (since they are precompiled).
But by putting the query in the function will it really make a difference?
Kindly suggest some methods for optimization of this query. Any code samples will be appreciated.