Re: The reason for loving Usenet
From: joel garry <joel-garry_at_home.com>
Date: Fri, 27 Apr 2012 12:59:35 -0700 (PDT)
Message-ID: <00127e37-58d2-4320-9f38-e28beb0bad6f_at_h4g2000pbe.googlegroups.com>
On Apr 27, 11:46 am, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> 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 --
Date: Fri, 27 Apr 2012 12:59:35 -0700 (PDT)
Message-ID: <00127e37-58d2-4320-9f38-e28beb0bad6f_at_h4g2000pbe.googlegroups.com>
On Apr 27, 11:46 am, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> 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 --
I found it by searching for "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."
jg
-- _at_home.com is bogus. http://www.zdnet.com/blog/btl/oracle-cfo-we-never-wanted-this-lawsuit-with-google/75643Received on Fri Apr 27 2012 - 14:59:35 CDT