Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: 9i Slow SQL Performance - EXPERTS apply within

Re: 9i Slow SQL Performance - EXPERTS apply within

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 5 Apr 2006 18:22:54 +0100
Message-ID: <sbednTV3E6nvYa7ZnZ2dnUVZ8tKdnZ2d@bt.com>

"Johne_uk" <edgarj_at_tiscali.co.uk> wrote in message news:1144254044.600885.60080_at_t31g2000cwb.googlegroups.com...
> Jonathan,
>
> Thanks for the reply. The first table in the sql is actually a view as
> you suggest. However, adding 'select /*+ no_merge
> (chops_web.va_voyage_list)' yielded no speed increase. I think the
> problem could be related to the differences between how 8i and 9i
> execute queries. Our developers are now working on reconstructing the
> query and have already yielded some improvements (the code in the
> posting is a subset of the sql that I was running).
>
> BTW apologies for my posts appearing twice. Seems to be a problem with
> my Groups account.
>
> regards
> John
>

I wasn't careful enough explaining the use of the hint. Hints that name tables and views have to use the alias that appears in the query block, not the schema_name.object_name.

In your case /*+ no_merge(a) */

In general it is better practice to put hints in the front-end SQL, rather than embedding them in stored views.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Received on Wed Apr 05 2006 - 12:22:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US