Re: to_number question

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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: