referred more than once in access plan means?

[Fan Ruo Xin]

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

[Mark A]

Re: referred more than once in access plan means?

"Fan Ruo Xin" <fanruox@sbcglo bal.net> wrote in message
news:7RuVb.2570 $PY.839@newssvr 26.news.prodigy .com...[color=blue]
> Can someone help me to confirm -
> From the access plan tree of a query, if one table is referred (TABLE[/color]
SCAN)[color=blue]
> three times. Does that mean this table was scanned (either from disk or
> bufferpool) three times during runtime?
> TIA
> FRX
>[/color]
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.

[Fan Ruo Xin]

Re: referred more than once in access plan means?


"Mark A" <ma@switchboard .net> wrote in message
news:HevVb.489$ Jj6.41784@news. uswest.net...[color=blue]
> "Fan Ruo Xin" <fanruox@sbcglo bal.net> wrote in message
> news:7RuVb.2570 $PY.839@newssvr 26.news.prodigy .com...[color=green]
> > Can someone help me to confirm -
> > From the access plan tree of a query, if one table is referred (TABLE[/color]
> SCAN)[color=green]
> > three times. Does that mean this table was scanned (either from disk or
> > bufferpool) three times during runtime?
> > TIA
> > FRX
> >[/color]
> That is certainly possible, especially if there is a subselect. It might
> also indicate intra-partition or inter-partition parallelism. But it is[/color]
hard[color=blue]
> to know exactly what you are referring to without seeing the explain[/color]
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_paralleli sm, nor inter_paralleli sm. 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?
[color=blue]
>
> All data is accessed by applications from the bufferpool. If the page[/color]
which[color=blue]
> 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[/color]
they[color=blue]
> are not already there. However, the explain will indicate prefetch[/color]
activity,[color=blue]
> which is attempt by DB2 to place pages in the bufferpool before they are
> asked for by the application.[/color]
=========
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 ...[color=blue]
>
>[/color]