From: wbreidbach <wolfgang.breidb...@bv-zahlungssysteme.de>
Date: Wed, 21 Nov 2007 00:42:45 -0800 (PST)
Local: Wed, Nov 21 2007 3:42 am
Subject: Re: Discrepency between SQL/MP Explain plan, actual sequence of IOs
On 20 Nov., 19:34, "Brian Ray" <be_...@sbcglobal.net> wrote:
> Working on a Pathway server that does the ole' "grab a screen's worth ofHi Brian,
> data for display in a list." Basic first page, next page, previous page kind
> of stuff. It opens a cursor consisting of a two-table inner join, let's call
> them TABLE1 and TABLE2. There's a lot of predicate filtering on several of
> TABLE1's columns, for which we built an index, and a tiny bit of filtering
> on one of TABLE2's columns (beyond the implicit filtering of the inner
> join). So on inhouse SQL compilation we get an Explain much like the
> - Operation 1) Scan TABLE1 via the aforementioned purpose-built index.
> - Operation 2) Scan TABLE2 via one of its indexes containing the one column
> - Operation 2.1) Hash join the result to the result of Operation 1.
> And hooray, no sorts required. In this dev pathway our datasets are pretty
> So then it's just a few dozen fetches until we hit end of cursor or fill up
> Now Measure was showing some unusually high RECORDS USED and RECORDS
> - Cursor open: A block's worth (?) of *TABLE2's* rows accessed, and a few
> - Each fetch: 1 row accessed and used from *TABLE1*, none for TABLE2.
> Which seems like the exact opposite of the strategy that the Explain plan
> And as mentioned, I'm confused why all the SQLSTMT's records accessed, or at
> Brian Ray
perhaps you should use Measure to check this. Just do an "ADD SQLSTMT
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.