Account Options

  1. Sign in
The old Google Groups will be going away soon, but your browser is incompatible with the new version.
Google Groups Home
« Groups Home
comp.sys.tandem
+ new post
About this group
Subscribe to this group
This is a Usenet group - learn more
Message from discussion Discrepency between SQL/MP Explain plan, actual sequence of IOs
The group you are posting to is a Usenet group. Messages posted to this group will make your email address visible to anyone on the Internet.
Your reply message has not been sent.
Your post was successful
 
From:
To:
Cc:
Followup To:
Add Cc | Add Followup-to | Edit Subject
Subject:
Validation:
For verification purposes please type the characters you see in the picture below or the numbers you hear by clicking the accessibility icon. Listen and type the numbers you hear
 
HKREGCJABCUB@spammotel.co m  
View profile  
 More options Nov 28 2007, 12:29 am
Newsgroups: comp.sys.tandem
From: "HKREGCJAB...@spammotel.com" <HKREGCJAB...@spammotel.com>
Date: Tue, 27 Nov 2007 21:29:25 -0800 (PST)
Local: Wed, Nov 28 2007 12:29 am
Subject: Re: Discrepency between SQL/MP Explain plan, actual sequence of IOs
Print | View thread | Show original | Report this message | Find messages by this author
On Nov 27, 12:27 pm, "Brian Ray" <be_...@sbcglobal.net> wrote:

> <HKREGCJAB...@spammotel.com> wrote in message

> news:9473d4bd-f4c0-4956-8581-a8f35acb68c1@s36g2000prg.googlegroups.com...

> > On Nov 20, 11:34 am, "Brian Ray" <be_...@sbcglobal.net> wrote:
> [orig post snipped]

> [more snipping]

> > You might want to try a larger dataset with an ORDER BY clause (with
> > STATS updated of course).  It may produce a plan that uses a nested
> > join loop in the required order (much more efficient for cursors).
> > CONTROL QUERY INTERACTIVE ACCESS ON may coax the optimizer into
> > choosing this option.

> I was hoping for this behavior in my tiny little inhouse pathway but for
> lack of time to experiment further, I'm resigned to getting rid of the inner
> join. Next time 'round I'll find a better development database ...

> > Joe Bishop

> Heh, the Tandem world is too small a place for there to be two Joe Bishops.
> I was just in the Bay Area and stopped by the old FDS offices to say high to
> Eric, Jacob, and the handful of guys still there from My Old Days (1993 -
> 1997). Greetings once again.

> Brian

Hey Brian,
I thought I recognized that name!  I owe the FDS guys a visit too.

I did some more research, and I'm afraid I have to take back those
hash join steps.  They only apply during a parallel operation using a
"hybrid" hash join.

The revised version:

1. Read all the required data from the table with the smaller dataset
and hash each distinct join key.
2. Read from the larger dataset and, for each row, compare the hash of
its join key with the hash value from the first table.
3. If it matches, return the result.

If the 2nd step uses an index (or primary key), the rows are returned
in the order of that key (what some call an "ordered" hash join).

Therefore, to avoid a sort, the index could only begin to be accessed
after the other table had been completely read.

I still think you'd get better performance by forcing a nested join
instead of splitting it into multiple statements.

Good to see that many of the old FDS crowd are still in the Tandem
world,
Joe


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy
©2013 Google