Re: Sybrand Bakker - request for a list of insults

jdevroberto_at_yahoo.com wrote:

[snip]

> And now a generalish-technical question...
>
> I have a mildly complex query which contains 7 inline views.
>
> I have some joins in there along with some NOT EXISTS sub queries.
>
> This query is 90 lines long.
>
> The optimizer likes it; the query runs in about 2 seconds.
>
> So, I took this query and wrapped with a view.
>
> When I SELECT * from the view, it takes 70 seconds.

Would there be unique constraints, foreign key constraints, primary keys and so on on the tables involved? If so, then they're probably invisible to the optimiser unless you have 9i and care to add constraints to your views. Such constraints are not actually enforced (they must be created in the disable novalidate state, but with the RELY flag set), but they 'publish' the real, operative constraints up the level of the view where the optimiser can suddenly realise what's actually going on at the underlying table level.

Just a thought. It might be worth looking at, anyway. Depends on your version, though (which you neglected to mention...)

Regards
HJR
>
> SQL tuning gurus...
> can you give me any tips on how to make this view run in 2 seconds?
>
> I wish I could get the view to use the same execution plan as
> the naked query.
>
> I've fiddled around with a variety of hints (both inside the view and
> global)
> and the view is still 70 seconds slow.
>
> Also I used
> SET AUTOTRACE TRACE
> inside of SQLPLUS to compare the two execution plans and they are
> indeed different (but similar).
>
> Help!
>
Received on Sun Dec 12 2004 - 02:06:49 CST