Can someone help me to confirm -
From the access plan tree of a query, if one table is referred (TABLE SCAN)
three times. Does that mean this table was scanned (either from disk or
bufferpool) three times during runtime?
TIA
FRX

"Fan Ruo Xin" <fanruox@sbcglobal.net> wrote in message
news:7RuVb.2570$PY.839@newssvr26.news.prodigy.com. ..
> Can someone help me to confirm -
> From the access plan tree of a query, if one table is referred (TABLE
SCAN)
> three times. Does that mean this table was scanned (either from disk or
> bufferpool) three times during runtime?
> TIA
> FRX
>
That is certainly possible, especially if there is a subselect. It might
also indicate intra-partition or inter-partition parallelism. But it is hard
to know exactly what you are referring to without seeing the explain output.

All data is accessed by applications from the bufferpool. If the page which
contains the needed row is not in the bufferpool, DB2 puts it in the
bufferpool for access by the application. The explain does not take into
consideration any physical I/O to put needed pages in the bufferpool if they
are not already there. However, the explain will indicate prefetch activity,
which is attempt by DB2 to place pages in the bufferpool before they are
asked for by the application.

"Mark A" <ma@switchboard.net> wrote in message
news:HevVb.489$Jj6.41784@news.uswest.net...
> "Fan Ruo Xin" <fanruox@sbcglobal.net> wrote in message
> news:7RuVb.2570$PY.839@newssvr26.news.prodigy.com. ..
> > Can someone help me to confirm -
> > From the access plan tree of a query, if one table is referred (TABLE
> SCAN)
> > three times. Does that mean this table was scanned (either from disk or
> > bufferpool) three times during runtime?
> > TIA
> > FRX
> >
> That is certainly possible, especially if there is a subselect. It might
> also indicate intra-partition or inter-partition parallelism. But it is
hard
> to know exactly what you are referring to without seeing the explain
output.
============
That is a good point. Parallel read might refer a table (especially a small
table) more times than a non-parallel read.
If I used neither intra_parallelism, nor inter_parallelism. I want to know
when you check the access plan, and you find out that a table is referred
three times. Does that mean this table need to be read three times?

>
> All data is accessed by applications from the bufferpool. If the page
which
> contains the needed row is not in the bufferpool, DB2 puts it in the
> bufferpool for access by the application. The explain does not take into
> consideration any physical I/O to put needed pages in the bufferpool if
they
> are not already there. However, the explain will indicate prefetch
activity,
> which is attempt by DB2 to place pages in the bufferpool before they are
> asked for by the application.
=========
In fact, my question is not this part. Through take a view of access plan.
You can't determine if the read is physical or logical ...
>
>