Re: The reason for loving Usenet
From: joel garry <joel-garry_at_home.com>
Date: Wed, 25 Apr 2012 17:35:22 -0700 (PDT)
Message-ID: <6851586c-0a93-470e-b84e-fc7f79511801_at_jx17g2000pbb.googlegroups.com>
On Apr 25, 2:50 pm, Mladen Gogala <gogala.mla..._at_gmail.com> 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: Wed, 25 Apr 2012 17:35:22 -0700 (PDT)
Message-ID: <6851586c-0a93-470e-b84e-fc7f79511801_at_jx17g2000pbb.googlegroups.com>
On Apr 25, 2:50 pm, Mladen Gogala <gogala.mla..._at_gmail.com> 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
I think you may have goofed. If the distinct was disallowing the remote driving site, working around it by removing the distinct (which re-allows the remote ops, speeding things up), loading the GTT and running the distinct off that may not be so horrible. Otherwise I would have posted a comment there to the mod.
jg
-- _at_home.com is bogus. http://www.nbcsandiego.com/news/local/Broadband-Lines-Cut-Copper-Theft-Alpine-Defense-Dept-148669775.htmlReceived on Wed Apr 25 2012 - 19:35:22 CDT