Re: PAGING CURSOR

Michael Hannan

Re: PAGING CURSOR
Terry was correct in saving that AND NOT logic has special benefit in
preventing Multi-index access although NOT logic is difficult to understand.
There is another alternative.

Suppose our initial SQL is as follows (no matching cols without Multi-index):

SELECT C1, C2, C3, C4 FROM tables
WHERE ((C1 >:h1)
OR (C1 =:h1 AND C2 >:h2)
OR (C1 =:h1 AND C2 =:h2 AND C3 >:h3)
OR (C1 =:h1 AND C2 =:h2 AND C3 =:h3 AND C4 >:h4) )
ORDER BY C1, C2, C3;

First transformation to get at least one matching column on C1 by adding a
redundant predicate:

SELECT C1, C2, C3, C4 FROM tables
WHERE C1>=:h1
AND ((C1 >:h1)
OR (C1 =:h1 AND C2 >:h2)
OR (C1 =:h1 AND C2 =:h2 AND C3 >:h3)
OR (C1 =:h1 AND C2 =:h2 AND C3 =:h3 AND C4 >:h4) )
ORDER BY C1, C2, C3
OPTIMIZE FOR n ROWS;

Note that undesirable multi-index processing is still possible and all
predicates are stage 1, especially if DB2 cannot avoid sorting.
Second transformation is to the 'AND' line only:

SELECT C1, C2, C3, C4 FROM tables
WHERE C1>=:h1
AND ((C1<>:h1)
OR (C1 =:h1 AND C2 >:h2)
OR (C1 =:h1 AND C2 =:h2 AND C3 >:h3)
OR (C1 =:h1 AND C2 =:h2 AND C3 =:h3 AND C4 >:h4) )
ORDER BY C1, C2, C3
OPTIMIZE FOR n ROWS;

Since C1>=:h1 already specified, C1<>:h1 implies C1 >:h1.
This should be explained in a comment to aid maintainability of code.
Therefore there is no logical difference. However this single adjustment is
enough to prevent multi-index index processing on the whole OR structure
because <> is never indexable. Note that stage 1 processing of all
predicates remains so index screening is possible.

The 'AND NOT' technique has been around for a long time, is one less
predicate, and is also logically equivalent by De Morgan's laws, but is
probably a lot harder to understand with so much reverse logic, which is why
I have come up with the above alternative, first published in my last IDUG
presentation.

There is extremely little difference in performance of the 2 approaches so
it is then a matter of personal choice.

From: Michael Hannan

>Subject: PAGING CURSOR
>
> The reason we use AND NOT logic is that it will match on an extra
>column.
>The following is an example of the SQL:
>
> CREATE TABLESPACE AHS00005 IN AHDB0001
> USING STOGROUP G461524
> PRIQTY 40 SECQTY 40 ERASE NO
> FREEPAGE 0 PCTFREE 0
> BUFFERPOOL BP0 LOCKSIZE ANY
> SEGSIZE 4
> CLOSE YES;
> COMMIT;
>
> CREATE TABLE DSDBA24.AHTA0500
> (C1 CHAR (1) NOT NULL ,
> C2 CHAR (1) NOT NULL ,
> C3 CHAR (1) NOT NULL ,
> C4 CHAR (1) NOT NULL ,
> C5 CHAR (1) NOT NULL )
> IN AHDB0001.AHS00005;
>
> CREATE UNIQUE INDEX DSDBA24.AHXA0501
> ON DSDBA24.AHTA0500
> (C1 ASC ,
> C2 ASC ,
> C3 ASC ,
> C4 ASC ,
> C5 ASC )
> USING STOGROUP G461524
> PRIQTY 40 SECQTY 40 ERASE NO
> FREEPAGE 0 PCTFREE 0
> CLUSTER
> BUFFERPOOL BP0
> CLOSE YES;
> COMMIT;
>
>--TEST DATA:
> INSERT INTO AHTA0500 VALUES ('A','A','A','A','A');
> INSERT INTO AHTA0500 VALUES ('A','A','A','A','B');
> INSERT INTO AHTA0500 VALUES ('A','A','A','A','C');
> INSERT INTO AHTA0500 VALUES ('A','A','A','A','D');
> .
> . (insert values here)
> .
> INSERT INTO AHTA0500 VALUES ('C','D','C','B','H');
> INSERT INTO AHTA0500 VALUES ('C','D','C','B','I');
> INSERT INTO AHTA0500 VALUES ('C','D','C','B','J');
>
>-- OLD FORMAT :
> SELECT C1, C2, C3, C4, C5 FROM AHTA0500
> WHERE C1>'C' OR
> (C1='C' AND C2>'B') OR
> (C1='C' AND C2='B' AND C3>'A') OR
> (C1='C' AND C2='B' AND C3='A' AND C4>'A') OR
> (C1='C' AND C2='B' AND C3='A' AND C4='A' AND C5 >'E');
>
>-- NEW FORMAT:
> SELECT C1, C2, C3, C4, C5 FROM AHTA0500
> WHERE C1 >= 'C'
> AND NOT (C1='C' AND C2<'B')
> AND NOT (C1='C' AND C2='B' AND C3<'A')
> AND NOT (C1='C' AND C2='B' AND C3='A' AND C4<'A')
> AND NOT (C1='C' AND C2='B' AND C3='A' AND C4='A' AND C5<='E');
>
> You will find that the second query matches on the first column
>whereas the first does not. You can extend the logic so that if you
>have an '=' on the first column it will match on the second etc. This is
>important to us. We have many indexes on multi million row tables where
>the first column is 'bank branch' with only a few distinct values, and the
>second is 'account number', with thousands of values. Many searches are done
>at branch level and the match on that column saves a lot of processing.
>
> I don't have access to post responses on the list server, so if you
>
>Andrew Hine
>DBA
>Lloyds TSB group PLC
>
>-----Original Message-----
>Sent: 17 December 1999 05:17
>Subject: Paging Cursors
>
>
>We need to build many programs with paging cursor logic.
>
>We are informed that using AND NOT logic is better than OR logic. Though we
>would prefer to use OR logic for ease of understanding.
>Is there performance or any other benefits in using AND NOT logic instead of
>OR logic?
>
>
>---------------------------------------------------------------------------
>This Email is intended for the exclusive use of the addressee only.
>If you are not the intended recipient, you should not use the
>contents nor disclose them to any other person and you should
>immediately notify the sender and delete the Email.
>
>Lloyds TSB Bank plc is registered in England and Wales no. 2065.
>Registered office: 71 Lombard Street, London, EC3P 3BS.
>
>Lloyds TSB Scotland plc is registered in Scotland no. 95237.
>Registered office: Henry Duncan House, 120 George Street,
>Edinburgh, EH2 4LH.
>---------------------------------------------------------------------------
>
>
>

>

Paging Cursors
We need to build many programs with paging cursor logic.

We are informed that using AND NOT logic is better than OR logic. Though we
would prefer to use OR logic for ease of understanding.
Is there performance or any other benefits in using AND NOT logic instead of
OR logic?

Terry Purcell

Re: Paging Cursors

Apart from being "visually" less complex, the performance benefit can be
that the AND NOT logic will use a single access path (if available) because
the AND NOT predicates are not indexable. The OR logic can make use of a
multiple index access path.

Note I said "can be", because you will need to test it for yourself as to
which performs better for your situation: either the single index access
path, whereby you have decreased your indexable predicates (now stage 1 or
2), or the multi-index access, involving an additional index access and RID
sort (if > 32 pages returned from 1st index).

Another thought (which I haven't tested), an ORDER BY sort which may be
avoided in a single index access path, will probably be required in a
multi-index situation due to the RID sort. Thoughts anyone?? (Thanks)

Regards
Terry Purcell
CPT Consulting

> -----Original Message-----
> Sent: Friday, 17 December 1999 4:17 pm
> Subject: Paging Cursors
>
> We need to build many programs with paging cursor logic.
>
> We are informed that using AND NOT logic is better than OR logic. Though
> we
> would prefer to use OR logic for ease of understanding.
> Is there performance or any other benefits in using AND NOT logic instead
> of
> OR logic?
>
>
>
>
>
>

PAGING CURSOR
(in response to Terry Purcell)
The reason we use AND NOT logic is that it will match on an extra
column.
The following is an example of the SQL:

CREATE TABLESPACE AHS00005 IN AHDB0001
USING STOGROUP G461524
PRIQTY 40 SECQTY 40 ERASE NO
FREEPAGE 0 PCTFREE 0
BUFFERPOOL BP0 LOCKSIZE ANY
SEGSIZE 4
CLOSE YES;
COMMIT;

CREATE TABLE DSDBA24.AHTA0500
(C1 CHAR (1) NOT NULL ,
C2 CHAR (1) NOT NULL ,
C3 CHAR (1) NOT NULL ,
C4 CHAR (1) NOT NULL ,
C5 CHAR (1) NOT NULL )
IN AHDB0001.AHS00005;

CREATE UNIQUE INDEX DSDBA24.AHXA0501
ON DSDBA24.AHTA0500
(C1 ASC ,
C2 ASC ,
C3 ASC ,
C4 ASC ,
C5 ASC )
USING STOGROUP G461524
PRIQTY 40 SECQTY 40 ERASE NO
FREEPAGE 0 PCTFREE 0
CLUSTER
BUFFERPOOL BP0
CLOSE YES;
COMMIT;

--TEST DATA:
INSERT INTO AHTA0500 VALUES ('A','A','A','A','A');
INSERT INTO AHTA0500 VALUES ('A','A','A','A','B');
INSERT INTO AHTA0500 VALUES ('A','A','A','A','C');
INSERT INTO AHTA0500 VALUES ('A','A','A','A','D');
.
. (insert values here)
.
INSERT INTO AHTA0500 VALUES ('C','D','C','B','H');
INSERT INTO AHTA0500 VALUES ('C','D','C','B','I');
INSERT INTO AHTA0500 VALUES ('C','D','C','B','J');

-- OLD FORMAT :
SELECT C1, C2, C3, C4, C5 FROM AHTA0500
WHERE C1>'C' OR
(C1='C' AND C2>'B') OR
(C1='C' AND C2='B' AND C3>'A') OR
(C1='C' AND C2='B' AND C3='A' AND C4>'A') OR
(C1='C' AND C2='B' AND C3='A' AND C4='A' AND C5 >'E');

-- NEW FORMAT:
SELECT C1, C2, C3, C4, C5 FROM AHTA0500
WHERE C1 >= 'C'
AND NOT (C1='C' AND C2<'B')
AND NOT (C1='C' AND C2='B' AND C3<'A')
AND NOT (C1='C' AND C2='B' AND C3='A' AND C4<'A')
AND NOT (C1='C' AND C2='B' AND C3='A' AND C4='A' AND C5<='E');

You will find that the second query matches on the first column
whereas the first does not. You can extend the logic so that if you
have an '=' on the first column it will match on the second etc. This is
important to us. We have many indexes on multi million row tables where
the first column is 'bank branch' with only a few distinct values, and the
second is 'account number', with thousands of values. Many searches are done
at branch level and the match on that column saves a lot of processing.

I don't have access to post responses on the list server, so if you

Andrew Hine
DBA
Lloyds TSB group PLC

-----Original Message-----
Sent: 17 December 1999 05:17
Subject: Paging Cursors

We need to build many programs with paging cursor logic.

We are informed that using AND NOT logic is better than OR logic. Though we
would prefer to use OR logic for ease of understanding.
Is there performance or any other benefits in using AND NOT logic instead of
OR logic?

---------------------------------------------------------------------------
This Email is intended for the exclusive use of the addressee only.
If you are not the intended recipient, you should not use the
contents nor disclose them to any other person and you should
immediately notify the sender and delete the Email.

Lloyds TSB Bank plc is registered in England and Wales no. 2065.
Registered office: 71 Lombard Street, London, EC3P 3BS.

Lloyds TSB Scotland plc is registered in Scotland no. 95237.
Registered office: Henry Duncan House, 120 George Street,
Edinburgh, EH2 4LH.
---------------------------------------------------------------------------