Re: Select with view join takes too long

I generally leave tunning SQL to developers and stick to DBA work, but every now and then some developer asks for help and I have found that about half the time I can.

1. Let check the statistics and make sure that they are (A) current, and (B) were generated by a large sample size or full compute. If the statistics are updated rerun the explain plans to check for changes in the plan (not really expected)
2. When working with a view Oracle first (A) tries to merge the view code into the sql. If Oracle can not do this then Oracle (B) tries to merge the sql into the view code, and finally Oracle (C) fires the view and executes the sql against the results. From the explain plan (C) is true probably due the presence of the group by clause in the original view with no other where clause restrictions.

What I am going to suggest is if item 1 did not result in better results then try coding a hint in the sql to force the optimizer to use the index that the combined sql resulted in being used. Note if your are on 7.2 or 7.3 then you should be able to code the view right into the 'from' cluase of the query. This brings up the question? Do you really need to have the view. The way the view is written the view will result in a full table scan. Depending on the sql that will use the view I believe that the results you show in your note is going to happen in other queries too.

The use of hints might handle this, but so can the newer sql version supported by 7.2. Hope this helps.

Mark Powell -- The only advise that counts is the advise that you follow so follow your own advise Received on Fri Mar 28 1997 - 00:00:00 CST