Re: to_number question

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Fri, 16 Jul 2004 09:14:38 -0600
Message-id: <40F7F0DE.4070809@sun.com>

I'm going from memory. This scenario was used on a 7.3 distributed database (I have not been able to duplicate it on an 8+ db, so the issued must have been addressed).

A query was accessing both local tables and 1 remote table. The sql was constructed in a traditional join construct. The performance was terrible with repeated trips to the remote database as the major issue. We rewrote the query so that the access to the remote table was an inline subquery and performed a join on that row source. This had the effect of retrieving *ALL* rows from the remote db in one operation instead of retrieving them individually as the query was processed. I think we calculated that over 75% of the remote rows were thrown away, but it was still quicker to retrieve them all in one operation. IIRC, it reduced the run time of the query from 4 hours to less than 10 minutes.

In this case, a subquery was used to impose a processing order.

In other cases, I have used subqueries to return row sources that would be difficult (in some cases impossible) to code in plain sql. For example, outer joining a hierarchical query. In a recent example, it was to return a row source to the calling query in a specific order to be processed.

I am approaching this from 'how can I get the job done quickly'. Yes, other languages would be better suited for the task. It works, it was coded rapidly, the user was happy.

Regards,
Daniel Fink

Wolfgang Breitling wrote:
<snip>
> Subqueries are a means to structure a query such that its logic is more easily
> grasped. They are NOT a means to impose a processing order.
<snip>

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Jul 16 2004 - 10:11:47 CDT