> Working on a Pathway server that does the ole' "grab a screen's worth of
> 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
> following:

> - Operation 1) Scan TABLE1 via the aforementioned purpose-built index.

> - Operation 2) Scan TABLE2 via one of its indexes containing the one column
> used in the predicate filter and then

> - 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
> small and the join method usually changes when we get the software into a
> bigger production database. But I'm happy because it's concentrating on
> TABLE1's index right out of the gate; this is the most selective step.

> So then it's just a few dozen fetches until we hit end of cursor or fill up
> the reply message with result rows. Cursor is BROWSE ACCESS.

> Now Measure was showing some unusually high RECORDS USED and RECORDS
> ACCESSED counts for this cursor, but low LOGICAL READS on the corresponding
> tables, so I popped a some SQLSADISPLAY statements after the cursor open and
> the fetch. The behavior reported by SQLSADISPLAY was:

> - Cursor open: A block's worth (?) of *TABLE2's* rows accessed, and a few
> dozen less rows used--all the rows that would satisfy the SELECT. None of
> TABLE1's rows accessed or used.

> - 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
> laid out. Between SQL compilation and runtime, can the query processor flip
> strategies on the fly? And why, if the cursor is BROWSE ACCESS, does it
> scoop up all the rows from one of the tables? This cursor does not trigger a
> sort. Nor is it locking any rows. Since the entire result set of one of the
> scans resided in a single block (I'm guessing), did the thing say "Aha, no
> point in going back to the disk/disk cache for TABLE2's rows for these
> fetches, I'll flip the whole access plan on its head."?

> And as mentioned, I'm confused why all the SQLSTMT's records accessed, or at
> least records used, are not reflected in the corresponding FILEs' logical
> reads--the SQLSTMT counters are much higher than the FILE counters. Do the
> records accessed/used on a cursor open somehow "not count"? Do only the rows
> accessed/used during the fetch count?

> Thanks,

> Brian Ray