Re: ORA-0113 Generated by Specific Query through DB-Link

On Jun 2, 12:54 pm, Tony <rigato..._at_gmail.com> wrote:
> Hi,
>
> Thought I posted a reply to this thread earlier in the week, but I
> don't see it.
>
> First - thank you everyone for the suggestions. I learned a bit more
> today. Here are the results for reference...
>
> It turns out re-arranging the query and using the /*+ ordered */ hint
> solves my original performance problem (which is definitely preferable
> to building the IN() clause on the fly!).
>
> So - the following works in about 350 msec:
>
> SELECT /*+ ordered */ bv.*
> FROM (SELECT linkid
> FROM PROJECTGAS_MAIN_LINKS
> WHERE projectid = 116448
> AND blocklist = 'N') lk,
> gas_mains_mgcbaseview bv
> WHERE lk.linkid = bv.objectid
> ORDER BY bv.physical_length DESC
>
> Leaving the hint off takes 12 seconds.
>
> Case closed!

That is great that you were able to solve the performance problem. Just one word of warning: consider adding DISTINCT between SELECT & linkid, as you saw in the posts by Jonathan Lewis and me. Without DISTINCT, if there are two rows in the PROJECTGAS_MAIN_LINKS table/ view with the same linkid that also have projectid = 116448 and blocklist = 'N', the query will return twice as many rows as expected, which means that it would not be equivalent to your original query.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sun Jun 03 2007 - 08:14:43 CDT