The reason for loving Usenet

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Wed, 25 Apr 2012 21:50:43 +0000 (UTC)
Message-ID: <jn9rji$glb$1_at_solani.org>



I was partaking in the "Oracle Support Community" forum and someone has posted a query that queries remote data. The complaint was that the query was exhausting the temporary space. Gentleman was slurping all of the remote tables into temporary tablespace and doing join locally. Predictably, the thing was taking forever and dying before completion from the exhausted TEMP tablespace.

A "guru" writes the following:



Hello,

heavy queries over remote databases is not easy for the optimizer... Maybe you could use a GLOBAL TEMPORARY TABLE "mygtt": insert all the rows in mygtt (without "distinct"), then replace the original statement by "INSERT INTO ihub.tmp_component_substutes SELECT DISTINCT ... FROM mygtt".

One remark: there is probably a mistake (well, 2) in your statement: you assume that the current month has 31 days... "end of month in 3 years" is more simply given by: ADD_MONTHS( TRUNC( sysdate, 'MM' ), 37 ) - 1

HTH, Bruno Vroman.


So, the "guru" wants to cure the temp tablespace exhaustion by creating a GTT? My response was the following, far more restrained that it would have been here:



Global temporary table??? What a horrible idea! A view should be created on the remote side to retrieve all the needed records. Also, the query is horribly formed, I can see a missing join or two.

After that reply, I received a warning from a moderator to be polite:



Thank you for your post. We welcome participation by all members in these communities. It is meant to be a place where members can become involved with helping others, as well as receiving help for issues that you post. Please participate and be courteous in your postings.

Moderator objects to my posting, probably based on the phrase "horrible idea"? That's a new one. Steve Adams used to enforce morality on oraclel,  now decaying and completely boring forum, but this is a new low. This guy would be ideal for oracle-l, to finish it off. I imagine that he and Steve would have a lot in common. I love Usenet because there are no moderators with the authority to enforce particular form of the English language on a database forum.
BTW, I am aware of the fact that Steve is no longer a moderator of oracle- l, but the list is so completely un-interesting that I don't have an interest in it.

-- 
http://mgogala.byethost5.com
Received on Wed Apr 25 2012 - 16:50:43 CDT

Original text of this message