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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
