Re: The reason for loving Usenet
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Fri, 27 Apr 2012 11:46:11 -0700 (PDT)
Message-ID: <2933542.3972.1335552371693.JavaMail.geo-discussion-forums_at_vbez20>
On Wednesday, April 25, 2012 5:50:43 PM UTC-4, Mladen Gogala wrote:
> 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 oracle-
> l, 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
Date: Fri, 27 Apr 2012 11:46:11 -0700 (PDT)
Message-ID: <2933542.3972.1335552371693.JavaMail.geo-discussion-forums_at_vbez20>
On Wednesday, April 25, 2012 5:50:43 PM UTC-4, Mladen Gogala wrote:
> 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 oracle-
> l, 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
How about a link to your post then we could judge it for ourselves.
HTH -- Mark D Powell -- Received on Fri Apr 27 2012 - 13:46:11 CDT