Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
2  
Have you tested it? Which was quicker? Can you post the output of the explain plan on the view. It sounds like you're missing some indexes. Generally Never use PL/SQL when you can use SQL. This sounds like the wrong way to go. – Ben Jun 20 '12 at 6:15
2  
Have you considered creating a local materialized view? – archimede Jun 20 '12 at 7:07
@Ben, if you want efficiency never use ANSII standard SQL when PL/SQL is optimizzed for the job. The only time you shoudl consider using ANSII standard is when you must have multiple db backends which is a situation to be avoided unless you are making COTS software. – HLGEM Jun 21 '12 at 14:49
Is there a suitable index built on the remote table? – ipip Jun 21 '12 at 14:58
1  
Try to run this on the remote database: EXPLAIN PLAN FOR Select * from Viewname where columnname='somevalue'; SELECT * FROM TABLE(DBMS_XPLAN.display); This will provide the explain plan mentioned by @Ben. Most probably the remote view is not optimal. – ipip Jun 21 '12 at 15:06
show 1 more comment

migrated from stackoverflow.com Jun 21 '12 at 12:29

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.