SQL Access Path

Richard Tan

SQL Access Path
Dear all,

I have an SQL join between two tables, say table A and B. I am weighting
whether to using UNION ALL for the following situation:
TableA
has 40,000 rows
Non Unique IndexA1: ADDRESS_ID, SUMM_ID

TableB
has 63,000 rows
Primary Key IndexB1: DISTRICT, SUMM_ID
Unique IndexB2: DISTRICT, SUMM_ID, STATUS

SELECT .....
FROM TableA A ,
TableB B
WHERE A.ADDRESS_ID = :ADDRESS-ID
AND B.DISTRICT = :DISTRICT
AND B.SUMM_ID = A.SUMM_ID
AND B.STATUS = 'D'
AND B.ORDER_NUM = ' '
UNION ALL
SELECT .....
FROM TableA A ,
TableB B
WHERE A.ADDRESS_ID = :ADDRESS-ID
AND B.DISTRICT = :DISTRICT
AND B.SUMM_ID = A.SUMM_ID
AND B.STATUS = 'V'
AND B.ORDER_NUM = ' '

This Explain plan would look something like this:

QB PL MD Table Name AX MC Index Name XO PF UJOGUJOG FE XS Tab Card MCROWS
-----------------------------------------------------------------------------------------------------------------------------------------------------
2 1 0 TableA I 1 IndexA1 Y NNNNNNNN 0 40,000 5
2 2 1 TableB I 3 IndexB2 N NNNNNNNN 0 63,000 1
1 1 0 TableA I 1 IndexA1 Y NNNNNNNN 0 40,000 5
1 2 1 TableB I 3 IndexB2 N NNNNNNNN 0 63,000 1
------------------------------------------------------------------------------------------------------------------------------------------------------

Without using UNION ALL

SELECT .....
FROM TableA A ,
TableB B
WHERE A.ADDRESS_ID = :ADDRESS-ID
AND B.DISTRICT = :DISTRICT
AND B.SUMM_ID = A.SUMM_ID
AND B.STATUS IN ('D', 'V')
AND B.ORDER_NUM = ' '

QB PL MD Table Name AX MC Index Name XO PF UJOGUJOG FE XS
Tab Card MCROWS

-----------------------------------------------------------------------------------------------------------------------------------------------------

1 1 0 TableA I 1 IndexA1
Y NNNNNNNN 0 40,000 5
1 2 1 TableB I 2 IndexB1
N NNNNNNNN 0 63,000 1

------------------------------------------------------------------------------------------------------------------------------------------------------

Field Description
----- -----------
MD Method ie what sort of join or sort used
AX Access type used in this step
MC Number of matching columns in index
XO Index only used
PF I/O prefetch of data flag
S - sequential prefetch
L - list prefetch
UJOGUJOG Unique, Join, Order By, Group By sorts used
FE When column function is evaluated
XS Sequence of steps in multiple index access
Tab Card Number of rows in the table referenced
MCROWS An estimate of the number of rows returned
when the last predicate operator in matchcols is
equivalence

All feedback are highly appreciated.

Richard Tan



Terry Purcell

Re: SQL Access Path
(in response to Richard Tan)
Richard,

The simple answer is that one pass through the data is more efficient than
doing two passes through the same (similar) data. Therefore use the IN list
rather than the UNION ALL.

Not only is their CPU reduction in reducing the number of SQL calls, but
with the single statement DB2 has greater capabilities to optimize the query
utilizing parallelism of the IN list (in V6 if IN list is inner table of
join, or V7 less restriction).

Keep it simple if you can, but go complex whenever you can't go simple!
Regards
Terry Purcell
Yevich Lawson & Associates
[login to unmask email]
WWW.YLASSOC.COM
WWW.DB2-SYMPOSIUM.COM

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Richard Tan
Sent: Monday, December 18, 2000 10:06 PM
To: [login to unmask email]
Subject: SQL Access Path


Dear all,

I have an SQL join between two tables, say table A and B. I am weighting
whether to using UNION ALL for the following situation:
TableA
has 40,000 rows
Non Unique IndexA1: ADDRESS_ID, SUMM_ID

TableB
has 63,000 rows
Primary Key IndexB1: DISTRICT, SUMM_ID
Unique IndexB2: DISTRICT, SUMM_ID, STATUS

SELECT .....
FROM TableA A ,
TableB B
WHERE A.ADDRESS_ID = :ADDRESS-ID
AND B.DISTRICT = :DISTRICT
AND B.SUMM_ID = A.SUMM_ID
AND B.STATUS = 'D'
AND B.ORDER_NUM = ' '
UNION ALL
SELECT .....
FROM TableA A ,
TableB B
WHERE A.ADDRESS_ID = :ADDRESS-ID
AND B.DISTRICT = :DISTRICT
AND B.SUMM_ID = A.SUMM_ID
AND B.STATUS = 'V'
AND B.ORDER_NUM = ' '

This Explain plan would look something like this:

QB PL MD Table Name AX MC Index Name XO PF UJOGUJOG FE XS
Tab Card MCROWS
---------------------------------------------------------------------------
--------------------------------------------------------------------------
2 1 0 TableA I 1 IndexA1
Y NNNNNNNN 0 40,000 5
2 2 1 TableB I 3 IndexB2
N NNNNNNNN 0 63,000 1
1 1 0 TableA I 1 IndexA1
Y NNNNNNNN 0 40,000 5
1 2 1 TableB I 3 IndexB2
N NNNNNNNN 0 63,000 1
---------------------------------------------------------------------------
---------------------------------------------------------------------------

Without using UNION ALL

SELECT .....
FROM TableA A ,
TableB B
WHERE A.ADDRESS_ID = :ADDRESS-ID
AND B.DISTRICT = :DISTRICT
AND B.SUMM_ID = A.SUMM_ID
AND B.STATUS IN ('D', 'V')
AND B.ORDER_NUM = ' '

QB PL MD Table Name AX MC Index Name XO PF UJOGUJOG FE XS
Tab Card MCROWS

----------------------------------------------------------------------------
-------------------------------------------------------------------------

1 1 0 TableA I 1 IndexA1
Y NNNNNNNN 0 40,000 5
1 2 1 TableB I 2 IndexB1
N NNNNNNNN 0 63,000 1

----------------------------------------------------------------------------
--------------------------------------------------------------------------

Field Description
----- -----------
MD Method ie what sort of join or sort used
AX Access type used in this step
MC Number of matching columns in index
XO Index only used
PF I/O prefetch of data flag
S - sequential prefetch
L - list prefetch
UJOGUJOG Unique, Join, Order By, Group By sorts used
FE When column function is evaluated
XS Sequence of steps in multiple index access
Tab Card Number of rows in the table referenced
MCROWS An estimate of the number of rows returned
when the last predicate operator in matchcols is
equivalence

All feedback are highly appreciated.

Richard Tan








Sanjeev (CTS) S

Re: SQL Access Path
(in response to Terry Purcell)
Hi Richard,
First of all it is nice to see that the EXPLAIN report is in good format in
the mail otherwise it become very painful to interpret it.

Coming back to your analysis, i would like to ask did you weigh the
estimated cost between the two queries ?. I believe using the UNION ALL will
cost more in this case because if we look at the MCROWS (in total) then it
is same for the join and UNION ALL and repeated for the UNION ALL because of
two join queries. If at all the read pages from the first join(of UNION ALL)
are not found in the pool then additional I/O is required other than reading
the pages for STATUS = V. Another important thing you should look at tha
total number of getpages. If you are using V5 and above then Index Screening
will do everything for you what you are achieving with the matchcols = 3. I
personally feel that there are high chances of getting the dynamic prefetch
using the normal join than UNION ALL. With the IN predicate specified if DB2
establishes the range and if it qualifies the criteria of using the
sequential detection then the performance will really improve. As Explain do
not show any prefetches and you are having good number of qualifying rows
depending on how much selected rows from Index are having ORDER_NUM = ' ' .

The best thing is that you are getting the Index only access on A1 ,even
then you might fall into scanning the complete index.Only way we can
minimize the random I/O by using some range predicates. What is the
Cardinality of Address_Id and SUMM_ID ?.

Have a look at the response time, I/O(all types) and CPU for both the
queries. These are all few guesses which let me feel that 2nd option is
better than UNION ALL. Also we would be debating depending on the replies
from others.

HTH'

Regards
Sanjeev

> -----Original Message-----
> From: Richard Tan [SMTP:[login to unmask email]
> Sent: Tuesday, December 19, 2000 9:36 AM
> To: [login to unmask email]
> Subject: SQL Access Path
>
> Dear all,
>
> I have an SQL join between two tables, say table A and B. I am weighting
> whether to using UNION ALL for the following situation:
> TableA
> has 40,000 rows
> Non Unique IndexA1: ADDRESS_ID, SUMM_ID
>
> TableB
> has 63,000 rows
> Primary Key IndexB1: DISTRICT, SUMM_ID
> Unique IndexB2: DISTRICT, SUMM_ID, STATUS
>
> SELECT .....
> FROM TableA A ,
> TableB B
> WHERE A.ADDRESS_ID = :ADDRESS-ID
> AND B.DISTRICT = :DISTRICT
> AND B.SUMM_ID = A.SUMM_ID
> AND B.STATUS = 'D'
> AND B.ORDER_NUM = ' '
> UNION ALL
> SELECT .....
> FROM TableA A ,
> TableB B
> WHERE A.ADDRESS_ID = :ADDRESS-ID
> AND B.DISTRICT = :DISTRICT
> AND B.SUMM_ID = A.SUMM_ID
> AND B.STATUS = 'V'
> AND B.ORDER_NUM = ' '
>
> This Explain plan would look something like this:
>
> QB PL MD Table Name AX MC Index Name XO PF UJOGUJOG FE XS
> Tab Card MCROWS
>
> --------------------------------------------------------------------------
> --------------------------------------------------------------------------
> -
> 2 1 0 TableA I 1 IndexA1
> Y NNNNNNNN 0 40,000 5
> 2 2 1 TableB I 3 IndexB2
> N NNNNNNNN 0 63,000 1
> 1 1 0 TableA I 1 IndexA1
> Y NNNNNNNN 0 40,000 5
> 1 2 1 TableB I 3 IndexB2
> N NNNNNNNN 0 63,000 1
>
> --------------------------------------------------------------------------
> --------------------------------------------------------------------------
> --
>
> Without using UNION ALL
>
> SELECT .....
> FROM TableA A ,
> TableB B
> WHERE A.ADDRESS_ID = :ADDRESS-ID
> AND B.DISTRICT = :DISTRICT
> AND B.SUMM_ID = A.SUMM_ID
> AND B.STATUS IN ('D', 'V')
> AND B.ORDER_NUM = ' '
>
> QB PL MD Table Name AX MC Index Name XO PF UJOGUJOG FE XS Tab
> Card MCROWS
> --------------------------------------------------------------------------
> --------------------------------------------------------------------------
> -
> 1 1 0 TableA I 1 IndexA1 Y
> NNNNNNNN 0 40,000 5
> 1 2 1 TableB I 2 IndexB1 N
> NNNNNNNN 0 63,000 1
>
> --------------------------------------------------------------------------
> --------------------------------------------------------------------------
> --
>
> Field Description
> ----- -----------
> MD Method ie what sort of join or sort used
> AX Access type used in this step
> MC Number of matching columns in index
> XO Index only used
> PF I/O prefetch of data flag
> S - sequential prefetch
> L - list prefetch
> UJOGUJOG Unique, Join, Order By, Group By sorts used
> FE When column function is evaluated
> XS Sequence of steps in multiple index access
> Tab Card Number of rows in the table referenced
> MCROWS An estimate of the number of rows returned
> when the last predicate operator in matchcols is
> equivalence
>
> All feedback are highly appreciated.
>
> Richard Tan
>
>
>
>
>
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message. Any unauthorised review, use, disclosure,
dissemination, forwarding, printing or copying of this email or any action taken in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------



Richard Tan

Re: SQL Access Path
(in response to Sanjeev (CTS) S)
Sanjeev,

Thanks very much for your response. This is the first time I am posting in
DB2-L. I am suprise with the quick response.
I am convince that without UNION ALL is a better SQL, however, to convince
our Design team without backup info is not an easy task.
that is why I am seeking support from the wider DB2 communities to confirm
my view.

Thanks again,
Richard Tan



"S, Sanjeev (CTS)" <[login to unmask email]> on 19/12/2000 05:22:41 pm

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Richard Tan/Australia/IBM)
Subject: Re: SQL Access Path




Hi Richard,
First of all it is nice to see that the EXPLAIN report is in good format
in
the mail otherwise it become very painful to interpret it.

Coming back to your analysis, i would like to ask did you weigh the
estimated cost between the two queries ?. I believe using the UNION ALL
will
cost more in this case because if we look at the MCROWS (in total) then it
is same for the join and UNION ALL and repeated for the UNION ALL because
of
two join queries. If at all the read pages from the first join(of UNION
ALL)
are not found in the pool then additional I/O is required other than
reading
the pages for STATUS = V. Another important thing you should look at tha
total number of getpages. If you are using V5 and above then Index
Screening
will do everything for you what you are achieving with the matchcols = 3. I
personally feel that there are high chances of getting the dynamic prefetch
using the normal join than UNION ALL. With the IN predicate specified if
DB2
establishes the range and if it qualifies the criteria of using the
sequential detection then the performance will really improve. As Explain
do
not show any prefetches and you are having good number of qualifying rows
depending on how much selected rows from Index are having ORDER_NUM = ' ' .

The best thing is that you are getting the Index only access on A1 ,even
then you might fall into scanning the complete index.Only way we can
minimize the random I/O by using some range predicates. What is the
Cardinality of Address_Id and SUMM_ID ?.

Have a look at the response time, I/O(all types) and CPU for both the
queries. These are all few guesses which let me feel that 2nd option is
better than UNION ALL. Also we would be debating depending on the replies
from others.

HTH'

Regards
Sanjeev

> -----Original Message-----
> From: Richard Tan [SMTP:[login to unmask email]
> Sent: Tuesday, December 19, 2000 9:36 AM
> To: [login to unmask email]
> Subject: SQL Access Path
>
> Dear all,
>
> I have an SQL join between two tables, say table A and B. I am weighting
> whether to using UNION ALL for the following situation:
> TableA
> has 40,000 rows
> Non Unique IndexA1: ADDRESS_ID, SUMM_ID
>
> TableB
> has 63,000 rows
> Primary Key IndexB1: DISTRICT, SUMM_ID
> Unique IndexB2: DISTRICT, SUMM_ID, STATUS
>
> SELECT .....
> FROM TableA A ,
> TableB B
> WHERE A.ADDRESS_ID = :ADDRESS-ID
> AND B.DISTRICT = :DISTRICT
> AND B.SUMM_ID = A.SUMM_ID
> AND B.STATUS = 'D'
> AND B.ORDER_NUM = ' '
> UNION ALL
> SELECT .....
> FROM TableA A ,
> TableB B
> WHERE A.ADDRESS_ID = :ADDRESS-ID
> AND B.DISTRICT = :DISTRICT
> AND B.SUMM_ID = A.SUMM_ID
> AND B.STATUS = 'V'
> AND B.ORDER_NUM = ' '
>
> This Explain plan would look something like this:
>
> QB PL MD Table Name AX MC Index Name XO PF UJOGUJOG FE
XS
> Tab Card MCROWS
>
>
--------------------------------------------------------------------------
>
--------------------------------------------------------------------------
> -
> 2 1 0 TableA I 1 IndexA1
> Y NNNNNNNN 0 40,000 5
> 2 2 1 TableB I 3 IndexB2
> N NNNNNNNN 0 63,000 1
> 1 1 0 TableA I 1 IndexA1
> Y NNNNNNNN 0 40,000 5
> 1 2 1 TableB I 3 IndexB2
> N NNNNNNNN 0 63,000 1
>
>
--------------------------------------------------------------------------
>
--------------------------------------------------------------------------
> --
>
> Without using UNION ALL
>
> SELECT .....
> FROM TableA A ,
> TableB B
> WHERE A.ADDRESS_ID = :ADDRESS-ID
> AND B.DISTRICT = :DISTRICT
> AND B.SUMM_ID = A.SUMM_ID
> AND B.STATUS IN ('D', 'V')
> AND B.ORDER_NUM = ' '
>
> QB PL MD Table Name AX MC Index Name XO PF UJOGUJOG FE XS Tab
> Card MCROWS
>
--------------------------------------------------------------------------
>
--------------------------------------------------------------------------
> -
> 1 1 0 TableA I 1 IndexA1 Y
> NNNNNNNN 0 40,000 5
> 1 2 1 TableB I 2 IndexB1 N
> NNNNNNNN 0 63,000 1
>
>
--------------------------------------------------------------------------
>
--------------------------------------------------------------------------
> --
>
> Field Description
> ----- -----------
> MD Method ie what sort of join or sort used
> AX Access type used in this step
> MC Number of matching columns in index
> XO Index only used
> PF I/O prefetch of data flag
> S - sequential prefetch
> L - list prefetch
> UJOGUJOG Unique, Join, Order By, Group By sorts used
> FE When column function is evaluated
> XS Sequence of steps in multiple index access
> Tab Card Number of rows in the table referenced
> MCROWS An estimate of the number of rows returned
> when the last predicate operator in matchcols is
> equivalence
>
> All feedback are highly appreciated.
>
> Richard Tan
>
>
>
> the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
can
>
-----------------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------------

This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged
information.
If you are not the intended recipient, please contact the sender by reply
e-mail and
destroy all copies of the original message. Any unauthorised review, use,
disclosure,
dissemination, forwarding, printing or copying of this email or any action
taken in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------------









Terry Purcell

Re: SQL Access Path
(in response to Richard Tan)
Richard,

If you are still having problems convincing the design team, then you may be
able to sway them with old school logic. If I was to read a sequential file
into a batch program, would I apply all criteria against the data in one
pass, or apply some to the first pass and do a second pass to apply the
remainder? Simple answer : 1 pass. The SQL is the same, just not as extreme.

However, there is one minor optimizer issue we have neglected. The second
(simpler) SQL has now chosen INDEXB1 (matchcols 2), rather than INDEXB2
(matchcols 3). The optimizer (V5 did) decides that a single probe of the
unique 2 column index is better than multiple IN list probes of the unique 3
column index. Why do multiple when it can do one? Except when the 3rd
matchcol may discard rows at index retrieval time rather than data
retrieval.

This will only be an issue if these values do significant filtering or
DISTRICT and SUMM_ID have a low cardinality (or DISTRICT low, SUMM_ID high).
In which case you can try and force the use of INDEXB2 by ordering by all of
it's columns (In this case the single SELECT can avoid a sort, whereas the
UNION ALL will always sort).

Sanjeev,

As both queries stand, there is no index screening being done; so that
comment is not applicable. Also, index screening has been around since
(approx) V2.3. V6 (and V5 APAR) introduced index screening on list prefetch;
but there is no list prefetch occuring.

Why would I scan the entire index? Unless the leading column has a
cardinality of 1. And how will a range predicate minimise random I/O? What
random I/O? Access is via the primary key index, which I assume is
clustered.

Regards
Terry Purcell
Yevich Lawson & Associates
[login to unmask email]
WWW.YLASSOC.COM
WWW.DB2-SYMPOSIUM.COM

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Richard Tan
Sent: Tuesday, December 19, 2000 12:44 AM
To: [login to unmask email]
Subject: Re: SQL Access Path


Sanjeev,

Thanks very much for your response. This is the first time I am posting in
DB2-L. I am suprise with the quick response.
I am convince that without UNION ALL is a better SQL, however, to convince
our Design team without backup info is not an easy task.
that is why I am seeking support from the wider DB2 communities to confirm
my view.

Thanks again,
Richard Tan



"S, Sanjeev (CTS)" <[login to unmask email]> on 19/12/2000 05:22:41 pm

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Richard Tan/Australia/IBM)
Subject: Re: SQL Access Path




Hi Richard,
First of all it is nice to see that the EXPLAIN report is in good format
in
the mail otherwise it become very painful to interpret it.

Coming back to your analysis, i would like to ask did you weigh the
estimated cost between the two queries ?. I believe using the UNION ALL
will
cost more in this case because if we look at the MCROWS (in total) then it
is same for the join and UNION ALL and repeated for the UNION ALL because
of
two join queries. If at all the read pages from the first join(of UNION
ALL)
are not found in the pool then additional I/O is required other than
reading
the pages for STATUS = V. Another important thing you should look at tha
total number of getpages. If you are using V5 and above then Index
Screening
will do everything for you what you are achieving with the matchcols = 3. I
personally feel that there are high chances of getting the dynamic prefetch
using the normal join than UNION ALL. With the IN predicate specified if
DB2
establishes the range and if it qualifies the criteria of using the
sequential detection then the performance will really improve. As Explain
do
not show any prefetches and you are having good number of qualifying rows
depending on how much selected rows from Index are having ORDER_NUM = ' ' .

The best thing is that you are getting the Index only access on A1 ,even
then you might fall into scanning the complete index.Only way we can
minimize the random I/O by using some range predicates. What is the
Cardinality of Address_Id and SUMM_ID ?.

Have a look at the response time, I/O(all types) and CPU for both the
queries. These are all few guesses which let me feel that 2nd option is
better than UNION ALL. Also we would be debating depending on the replies
from others.

HTH'

Regards
Sanjeev



Sanjeev (CTS) S

Re: SQL Access Path
(in response to Terry Purcell)
Hi Terry,
Thanks for pointing out some wrong messages delivered by me. I overlooked
few things like INDEX B1 is used for the 2nd query, i thought INDEX B2 is
used with matchcols of 2. However INDEX B1 is more logical.
Considering INDEX B2 is used, there is one thing which i want to clarify
on the basis of which i gave few suggestions. What if the STATUS value D and
V are closely located and there are a lot of records containing both ? Is
there any chances that the dynamic prefetch be kicked of on the indexes ? I
am not sure about this but at the same time i think it should be.This is the
way i told the synch. access on the index as well can be avoided if we have
more qualifying rows using the IN predicate. I did not assume it to be the
clustering index as i saw INDEX B2 which is unique but not primary.
The reason i asked about the cardinality is related to my worry of scanning
the complete index.

My opologies for this miss of looking the INDEX B2 instead of INDEX B1 for
the 2nd query.

Thanks and Regards
Sanjeev

> -----Original Message-----
> From: Terry Purcell [SMTP:[login to unmask email]
> Sent: Tuesday, December 19, 2000 8:43 PM
> To: [login to unmask email]
> Subject: Re: SQL Access Path
>
> Richard,
>
> If you are still having problems convincing the design team, then you may
> be
> able to sway them with old school logic. If I was to read a sequential
> file
> into a batch program, would I apply all criteria against the data in one
> pass, or apply some to the first pass and do a second pass to apply the
> remainder? Simple answer : 1 pass. The SQL is the same, just not as
> extreme.
>
> However, there is one minor optimizer issue we have neglected. The second
> (simpler) SQL has now chosen INDEXB1 (matchcols 2), rather than INDEXB2
> (matchcols 3). The optimizer (V5 did) decides that a single probe of the
> unique 2 column index is better than multiple IN list probes of the unique
> 3
> column index. Why do multiple when it can do one? Except when the 3rd
> matchcol may discard rows at index retrieval time rather than data
> retrieval.
>
> This will only be an issue if these values do significant filtering or
> DISTRICT and SUMM_ID have a low cardinality (or DISTRICT low, SUMM_ID
> high).
> In which case you can try and force the use of INDEXB2 by ordering by all
> of
> it's columns (In this case the single SELECT can avoid a sort, whereas the
> UNION ALL will always sort).
>
> Sanjeev,
>
> As both queries stand, there is no index screening being done; so that
> comment is not applicable. Also, index screening has been around since
> (approx) V2.3. V6 (and V5 APAR) introduced index screening on list
> prefetch;
> but there is no list prefetch occuring.
>
> Why would I scan the entire index? Unless the leading column has a
> cardinality of 1. And how will a range predicate minimise random I/O? What
> random I/O? Access is via the primary key index, which I assume is
> clustered.
>
> Regards
> Terry Purcell
> Yevich Lawson & Associates
> [login to unmask email]
> WWW.YLASSOC.COM
> WWW.DB2-SYMPOSIUM.COM
>
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message. Any unauthorised review, use, disclosure,
dissemination, forwarding, printing or copying of this email or any action taken in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------



[login to unmask email]

Re: SQL Access Path
(in response to Sanjeev (CTS) S)
Sanjeev,

I didn't get a chance to to analyze your query thourougly, but at a
glance, based
on you requirements I thought this might be a candidate for a possible
rewrite using
UNION ALL but as Terry mentioned this would bounce the query into
"Stage-2'land"
and perform the nasty sort. Another solution (if your shop is at least V5)
is to use
the CASE command. This would significantly improve performance because you
would only
make one pass through the predicate forest and remain in "Stage-1'land".
This means
you would probably invoke the wonderfull Matching Index scan and NOT
invoke the SORT
Grinch.

Good Luck,
Don Alden

-----Original Message-----
From: S, Sanjeev (CTS) [mailto:[login to unmask email]
Sent: Tuesday, December 19, 2000 10:14 PM
To: [login to unmask email]
Subject: Re: SQL Access Path


Hi Terry,
Thanks for pointing out some wrong messages delivered by me. I overlooked
few things like INDEX B1 is used for the 2nd query, i thought INDEX B2 is
used with matchcols of 2. However INDEX B1 is more logical.
Considering INDEX B2 is used, there is one thing which i want to clarify
on the basis of which i gave few suggestions. What if the STATUS value D and
V are closely located and there are a lot of records containing both ? Is
there any chances that the dynamic prefetch be kicked of on the indexes ? I
am not sure about this but at the same time i think it should be.This is the
way i told the synch. access on the index as well can be avoided if we have
more qualifying rows using the IN predicate. I did not assume it to be the
clustering index as i saw INDEX B2 which is unique but not primary.
The reason i asked about the cardinality is related to my worry of scanning
the complete index.

My opologies for this miss of looking the INDEX B2 instead of INDEX B1 for
the 2nd query.

Thanks and Regards
Sanjeev

> -----Original Message-----
> From: Terry Purcell [SMTP:[login to unmask email]
> Sent: Tuesday, December 19, 2000 8:43 PM
> To: [login to unmask email]
> Subject: Re: SQL Access Path
>
> Richard,
>
> If you are still having problems convincing the design team, then you may
> be
> able to sway them with old school logic. If I was to read a sequential
> file
> into a batch program, would I apply all criteria against the data in one
> pass, or apply some to the first pass and do a second pass to apply the
> remainder? Simple answer : 1 pass. The SQL is the same, just not as
> extreme.
>
> However, there is one minor optimizer issue we have neglected. The second
> (simpler) SQL has now chosen INDEXB1 (matchcols 2), rather than INDEXB2
> (matchcols 3). The optimizer (V5 did) decides that a single probe of the
> unique 2 column index is better than multiple IN list probes of the unique
> 3
> column index. Why do multiple when it can do one? Except when the 3rd
> matchcol may discard rows at index retrieval time rather than data
> retrieval.
>
> This will only be an issue if these values do significant filtering or
> DISTRICT and SUMM_ID have a low cardinality (or DISTRICT low, SUMM_ID
> high).
> In which case you can try and force the use of INDEXB2 by ordering by all
> of
> it's columns (In this case the single SELECT can avoid a sort, whereas the
> UNION ALL will always sort).
>
> Sanjeev,
>
> As both queries stand, there is no index screening being done; so that
> comment is not applicable. Also, index screening has been around since
> (approx) V2.3. V6 (and V5 APAR) introduced index screening on list
> prefetch;
> but there is no list prefetch occuring.
>
> Why would I scan the entire index? Unless the leading column has a
> cardinality of 1. And how will a range predicate minimise random I/O? What
> random I/O? Access is via the primary key index, which I assume is
> clustered.
>
> Regards
> Terry Purcell
> Yevich Lawson & Associates
> [login to unmask email]
> WWW.YLASSOC.COM
> WWW.DB2-SYMPOSIUM.COM
>
----------------------------------------------------------------------------
-------------------------------------------------------------
----------------------------------------------------------------------------
-------------------------------------------------------------
This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged
information.
If you are not the intended recipient, please contact the sender by reply
e-mail and
destroy all copies of the original message. Any unauthorised review, use,
disclosure,
dissemination, forwarding, printing or copying of this email or any action
taken in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------
------------------------------------------------------------
----------------------------------------------------------------------------
------------------------------------------------------------