EXISTS and Index processing

[login to unmask email]

EXISTS and Index processing
Hello,

I have a question about stage 2 predicates and index access paths....

My undestanding is that use of EXIST and NOT EXISTS with correlated subquery
continues to be a stage 2 predicate in DB2 V 5 and that as a consequence cannot
be supported by any kind of index based access path (matching or non matching).
However I have observed some SQL in our production subsystem with a BIND date of
1997-10-10 14:43:39.07 (at which time we were version 4) which appears to me to
be stage 2 but with an index in the access path. Is this subquery stage 2 and is
the rest of my thinking correct? We are currently running DB2 OS/390 Ver 5.0


DECLARE CURSOR1 CURSOR FOR
SELECT DISTINCT A . FINANCIAL_PROD_ID
FROM Table A A
WHERE SERVICE_PROD_ID LIKE 'S%'
AND FINANCIAL_PROD_ID LIKE 'A%'
AND START_DATE <= : TCG-NEXT-BUS-DATE
AND CANCEL_DATE > : TCG-NEXT-BUS-DATE
AND NOT EXISTS (
SELECT DISTINCT B . FINANCIAL_PROD_ID
FROM Table B B
WHERE B . FINANCIAL_PROD_ID = A . FINANCIAL_PROD_ID )

Access table Table A
using tablespace scan
Intent share; Sequential prefetch;
Composite table is sorted for DISTINCT/UNIQUE clause
Access table Table B
using index Table B.XB01 (0 COLS)
Intent share;
index only
Composite table is sorted for DISTINCT/UNIQUE clause

QUALIFIER: TABLE=Table B
CmIndex Name Column Seq Num O Lth DatTyp
----v----1----v----2----v----3----v----4----v----5----v----6----v----7----v---
Table B SERVICE_PROD_ID 1 1 A 11 CHAR
Table B FINANCIAL_PROD_ID 2 2 A 11 CHAR

My interpretation of the output is that the index is not being used to support
sorting either since a sort is reported as being invoked.
Is the distinct in the subquery redundant?


Visit Bank of Scotland's web site at http://www.bankofscotland.co.uk to find out
about our range of products and services, and for the latest Bank of Scotland
news and information.


The information contained in this message is confidential and is intended for
the addressee only. If you have received this message in error or there are any
problems please notify the originator immediately. The unauthorised use,
disclosure, copying or alteration of this message is strictly forbidden. This
mail and any attachments have been scanned for viruses prior to leaving Bank of
Scotland Group network. Bank of Scotland Group will not be liable for direct,
special, indirect or consequential damages arising from alteration of the
contents of this message by a third party or as a result of any virus being
passed on.



Venkat (PCA) Pillay

Re: EXISTS and Index processing
(in response to BoSDBA@BANKOFSCOTLAND.CO.UK)
Index can be used for subquery because it is different query block but it is
stage 2 predicate for the outer query.

Distinct is not necessary inside this subquery.

> -----Original Message-----
> From: [login to unmask email] [SMTP:[login to unmask email]
> Sent: Wednesday, January 05, 2000 12:02 PM
> To: [login to unmask email]
> Subject: EXISTS and Index processing
>
> Hello,
>
> I have a question about stage 2 predicates and index access paths....
>
> My undestanding is that use of EXIST and NOT EXISTS with correlated
> subquery
> continues to be a stage 2 predicate in DB2 V 5 and that as a consequence
> cannot
> be supported by any kind of index based access path (matching or non
> matching).
> However I have observed some SQL in our production subsystem with a BIND
> date of
> 1997-10-10 14:43:39.07 (at which time we were version 4) which appears to
> me to
> be stage 2 but with an index in the access path. Is this subquery stage 2
> and is
> the rest of my thinking correct? We are currently running DB2 OS/390 Ver
> 5.0
>
>
> DECLARE CURSOR1 CURSOR FOR
> SELECT DISTINCT A . FINANCIAL_PROD_ID
> FROM Table A A
> WHERE SERVICE_PROD_ID LIKE 'S%'
> AND FINANCIAL_PROD_ID LIKE 'A%'
> AND START_DATE <= : TCG-NEXT-BUS-DATE
> AND CANCEL_DATE > : TCG-NEXT-BUS-DATE
> AND NOT EXISTS (
> SELECT DISTINCT B . FINANCIAL_PROD_ID
> FROM Table B B
> WHERE B . FINANCIAL_PROD_ID = A . FINANCIAL_PROD_ID )
>
> Access table Table A
> using tablespace scan
> Intent share; Sequential prefetch;
> Composite table is sorted for DISTINCT/UNIQUE clause
> Access table Table B
> using index Table B.XB01 (0 COLS)
> Intent share;
> index only
> Composite table is sorted for DISTINCT/UNIQUE clause
>
> QUALIFIER: TABLE=Table B
> CmIndex Name Column Seq Num O Lth
> DatTyp
> ----v----1----v----2----v----3----v----4----v----5----v----6----v----7----
> v---
> Table B SERVICE_PROD_ID 1 1 A 11 CHAR
> Table B FINANCIAL_PROD_ID 2 2 A 11 CHAR
>
> My interpretation of the output is that the index is not being used to
> support
> sorting either since a sort is reported as being invoked.
> Is the distinct in the subquery redundant?
>
>
> Visit Bank of Scotland's web site at http://www.bankofscotland.co.uk to
> find out
> about our range of products and services, and for the latest Bank of
> Scotland
> news and information.
>
>
> The information contained in this message is confidential and is intended
> for
> the addressee only. If you have received this message in error or there
> are any
> problems please notify the originator immediately. The unauthorised use,
> disclosure, copying or alteration of this message is strictly forbidden.
> This
> mail and any attachments have been scanned for viruses prior to leaving
> Bank of
> Scotland Group network. Bank of Scotland Group will not be liable for
> direct,
> special, indirect or consequential damages arising from alteration of the
> contents of this message by a third party or as a result of any virus
> being
> passed on.
>
>
>
>
>



Michael Hannan

Re: EXISTS and Index processing
(in response to Venkat (PCA) Pillay)
Just to expand slightly on what Venkat correctly replied:

The NOT EXISTS subquery is executed when rows of Table A have been filtered
by the earlier stage 1 predicates and passed to stage 2 processing in the RDS.

Subquery may not be executed if 1 page memory cache determines result
already known for same value of correlation column A . FINANCIAL_PROD_ID.

Within subquery processing of Table B, index matching can occur, since stage
2 behaviour of NOT EXISTS is now not relevant.

I hate to see a lot of use of DISTINCTs in SQLs.
Make sure you really need them, and DB2 Sort avoided, unless you want to
force a DB2 Sort for some reason.

DB2 Sort Usually only avoided if Selected columns known to be unique already.

From: Michael Hannan
Just my personal opinions.

>From: "Pillay, Venkat (PCA)" <[login to unmask email]>
>Subject: Re: EXISTS and Index processing
>To: [login to unmask email]
>
>Index can be used for subquery because it is different query block but it is
>stage 2 predicate for the outer query.
>
>Distinct is not necessary inside this subquery.
>
>> -----Original Message-----
>> From: [login to unmask email] [SMTP:[login to unmask email]
>> Sent: Wednesday, January 05, 2000 12:02 PM
>> To: [login to unmask email]
>> Subject: EXISTS and Index processing
>>
>> Hello,
>>
>> I have a question about stage 2 predicates and index access paths....
>>
>> My undestanding is that use of EXIST and NOT EXISTS with correlated
>> subquery
>> continues to be a stage 2 predicate in DB2 V 5 and that as a consequence
>> cannot
>> be supported by any kind of index based access path (matching or non
>> matching).
>> However I have observed some SQL in our production subsystem with a BIND
>> date of
>> 1997-10-10 14:43:39.07 (at which time we were version 4) which appears to
>> me to
>> be stage 2 but with an index in the access path. Is this subquery stage 2
>> and is
>> the rest of my thinking correct? We are currently running DB2 OS/390 Ver
>> 5.0
>>
>>
>> DECLARE CURSOR1 CURSOR FOR
>> SELECT DISTINCT A . FINANCIAL_PROD_ID
>> FROM Table A A
>> WHERE SERVICE_PROD_ID LIKE 'S%'
>> AND FINANCIAL_PROD_ID LIKE 'A%'
>> AND START_DATE <= : TCG-NEXT-BUS-DATE
>> AND CANCEL_DATE > : TCG-NEXT-BUS-DATE
>> AND NOT EXISTS (
>> SELECT DISTINCT B . FINANCIAL_PROD_ID
>> FROM Table B B
>> WHERE B . FINANCIAL_PROD_ID = A . FINANCIAL_PROD_ID )
>>
>> Access table Table A
>> using tablespace scan
>> Intent share; Sequential prefetch;
>> Composite table is sorted for DISTINCT/UNIQUE clause
>> Access table Table B
>> using index Table B.XB01 (0 COLS)
>> Intent share;
>> index only
>> Composite table is sorted for DISTINCT/UNIQUE clause
>>
>> QUALIFIER: TABLE=Table B
>> CmIndex Name Column Seq Num O Lth
>> DatTyp
>> ----v----1----v----2----v----3----v----4----v----5----v----6----v----7----
>> v---
>> Table B SERVICE_PROD_ID 1 1 A 11 CHAR
>> Table B FINANCIAL_PROD_ID 2 2 A 11 CHAR
>>
>> My interpretation of the output is that the index is not being used to
>> support
>> sorting either since a sort is reported as being invoked.
>> Is the distinct in the subquery redundant?
>>
>>
>> Visit Bank of Scotland's web site at http://www.bankofscotland.co.uk to
>> find out
>> about our range of products and services, and for the latest Bank of
>> Scotland
>> news and information.
>>
>>
>> The information contained in this message is confidential and is intended
>> for
>> the addressee only. If you have received this message in error or there
>> are any
>> problems please notify the originator immediately. The unauthorised use,
>> disclosure, copying or alteration of this message is strictly forbidden.
>> This
>> mail and any attachments have been scanned for viruses prior to leaving
>> Bank of
>> Scotland Group network. Bank of Scotland Group will not be liable for
>> direct,
>> special, indirect or consequential damages arising from alteration of the
>> contents of this message by a third party or as a result of any virus
>> being
>> passed on.
>>
>>
>>
>>
>>
>
>
>


>