Re: The reason for loving Usenet
Date: Thu, 26 Apr 2012 09:25:19 -0700 (PDT)
Message-ID: <91b4dc69-6b0a-4cff-8ecc-dae745787161_at_h4g2000pbe.googlegroups.com>
On Apr 25, 6:39 pm, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
> On Wed, 25 Apr 2012 17:35:22 -0700, joel garry wrote:
> > 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.
>
> Joel, distinct in the join disallows shipping the entire query to the
> remote database. The right solution would be to only ship what is needed
> from the remote database, which probably means creating a view on the
> remote location. Anyway, suggesting GTT when the actual error is caused
> by running out of temporary tablespace doesn't strike me as a good idea
> any way you turn it.
> Besides, my comment was far from being rude or inappropriate. I just
> expressed my opinion, moderator's intervention was totally uncalled for
> and completely out of place.
>
> --http://mgogala.byethost5.com
(Sorry I had to be a bit brief there, had to run off to catch a train.) Yes, creating a view in the remote location is likely the correct answer for the general case. In this case, the OP has gone a bit further, and discovered that simply removing the distinct changes from shipping and dealing with hundreds of TB to some reasonable number. I haven't looked at the queries, so I don't know if the query you said was horrible was indeed missing some joins, so I concede that, and I wouldn't at all be surprised if there were additional unstated changes by the OP to get to the query that runs fast. So what I should be more clear about is I think you jumped too fast from temp tablespace to remote view, without considering that the query without distinct doesn't have the temp tablespace problem. The GTT is not solving the temp tablespace problem directly, it is merely working around the problem that creates the temp space issue. Without the distinct, only what is needed is shipped and so much of the work is done remotely. It's not a horrible idea, it's (potentially) a nice hack.
What actually happened is I was trying to compose an answer to the moderator of the type "you should give an example of how he should have answered rather than..." blablabla, then I started to type something like "assuming Mladen is technically correct..." At that point, the dinging in the back of my head about distinct became more distinct, and I took another look at the thread and went, oh my, this distinct thing sounds familiar. Poking about on the tubes, I found a procedure for dynamically generating remote views, and just went, jeez, that could be even worse. At that point, I realized, sometimes it is appropriate to just work around a problem, rather than doing it right or 90 degrees from right. The danger lies, of course, if people think the workaround is the general answer, and that is indeed horrible. We just don't know the scope beyond the single query in question, and of course, TIMTOWTDI.
Just to be clear, I think we're on the same page about moderation. We can have different responses to it. My dad had an expression "like water off a ducks back." I know how hard that can be with a direct attack. I also think shining a spotlight on mod-abuse is a good thing. Such a light needs to be constructive criticism, even if it is likely to be pointless. Not that there's anything wrong with a good rant now and then. On the other hand, some places are just plain pantywaisted, and _should_ be left in a huff.
I don't go to that community often because that interface is even worse than the stupid jive on the otn forums.
jg
-- _at_home.com is bogus. TINCReceived on Thu Apr 26 2012 - 11:25:19 CDT