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

From: Tony <rigatony1_at_gmail.com>
Date: Sat, 02 Jun 2007 09:54:19 -0700
Message-ID: <1180803259.403010.68370@m36g2000hse.googlegroups.com>

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! Received on Sat Jun 02 2007 - 11:54:19 CDT