Any disadvantages by using 0 = 1 predicate

Pradeep Gunjala

Any disadvantages by using 0 = 1 predicate
Hello List,

We know that optimizer is not chosing the right index for one of our SQL
so we have suggested to use 0 = 1 predicate to influence the access path
so that it would consider the right index. But are there any
disadavantages with this approach down the road. Does IBM has stated in
any manual to use this as a lost resort. I was asked by our management and
I need your inputs.

Thanks in advance.

Wish you all a Very Happy New Year 2006 !!

- Pradeep

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

James Campbell

Re: Any disadvantages by using 0 = 1 predicate
(in response to Pradeep Gunjala)
I recall reading somewhere that the DB2 zOS V8 optimizer has the smarts to
discard the "or 0=1" predicate as it can never be true. You can draw your own
conclusions.

The officially documented method is to use "+ 0" for numeric items or "concat ''"
(that's a zero length string) for character items to disable indexability. (Admin Guide,
predicate indexabilty section, one of the notes after the chart.)

The other alternative is an optimizer hint.

James Campbell

On 30 Dec 2005 at 15:49, Pradeep Gunjala wrote:

> Hello List,
>
> We know that optimizer is not chosing the right index for one of our SQL
> so we have suggested to use 0 = 1 predicate to influence the access path
> so that it would consider the right index. But are there any
> disadavantages with this approach down the road. Does IBM has stated in
> any manual to use this as a lost resort. I was asked by our management and
> I need your inputs.
>
> Thanks in advance.
>
> Wish you all a Very Happy New Year 2006 !!
>
> - Pradeep
>
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

[login to unmask email]

Re: Any disadvantages by using 0 = 1 predicate
(in response to James Campbell)
If my memory serves me corretly ,this type of statement would be flagged
as 'Pruned' ,and never be executed actually, and NOT impact your access
path, perhaps begins from V7?
So, it depends on where you stand. You can make it with V6 or lower
versions.

Wish all a happy new year!!!

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Terry Purcell

Re: Any disadvantages by using 0 = 1 predicate
(in response to hhuang@DCCSH.ICBC.COM.CN)
Pradeep,

The optimizer doesn't prune non-boolean term "OR 0=1", but from V7 (with
APAR PQ77454), optimizer does prune some boolean term predicates that can
never be true, such as WHERE 0=1.

To answer your original question, any type of trick is absolutely a last
resort in my opinion.

The preferred approach is instead to determine what the root cause is and
resolve that. So that other queries don't suffer the same issue. You don't
want to have to use tricks on every similar SQL if the problem can be
resolved at the table or index level.

So why is the optimizer not choosing the "correct" index? Unknown data
skew or correlation? Predicates that are difficult for the optimizer to
cost correctly?

The risks are that you don't resolve the real problem (as I mention
above), maintainability of the statement, CPU overhead of the trick etc.

Regards
Terry Purcell

On Sat, 31 Dec 2005 13:59:11 +1100, James Campbell
<[login to unmask email]> wrote:

>I recall reading somewhere that the DB2 zOS V8 optimizer has the smarts to
>discard the "or 0=1" predicate as it can never be true. You can draw
your own
>conclusions.
>
>The officially documented method is to use "+ 0" for numeric items
or "concat ''"
>(that's a zero length string) for character items to disable
indexability. (Admin Guide,
>predicate indexabilty section, one of the notes after the chart.)
>
>The other alternative is an optimizer hint.
>
>James Campbell
>
>On 30 Dec 2005 at 15:49, Pradeep Gunjala wrote:
>
>> Hello List,
>>
>> We know that optimizer is not chosing the right index for one of our SQL
>> so we have suggested to use 0 = 1 predicate to influence the access path
>> so that it would consider the right index. But are there any
>> disadavantages with this approach down the road. Does IBM has stated in
>> any manual to use this as a lost resort. I was asked by our management
and
>> I need your inputs.
>>
>> Thanks in advance.
>>
>> Wish you all a Very Happy New Year 2006 !!
>>
>> - Pradeep

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Pradeep Gunjala

Re: Any disadvantages by using 0 = 1 predicate
(in response to Terry Purcell)
Terry,

Thanks a lot for your reply. I agree. Let me explain you the scenario.

IX1 is defined on COLA
IX2 is defined on COLB

SQL:

Select .........FROM TAB
WHERE COLA BETWEEN :HV1 AND :HV2
AND COLB = :HV3

But HV1 and HV2 variable are optional that means in all the cases these
values may not be known to application, so the developers are populating
with Low values in HV1 and High Values in HV2 respectively in those cases.
Even though access path shows index access (IX1) it has to scan the entire
index space there by table space because of Low values and High values in
HV1 and HV2.
But the application will always provide the HV3 value, so we decided to
force the access path to consider the second index IX2, so that we don't
end up looking the entire index space of IX1.

Do you see any harm in long term using this approach.

Thanks,
Pradeep





Terry Purcell <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/31/2005 01:38 PM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] Any disadvantages by using 0 = 1 predicate






Pradeep,

The optimizer doesn't prune non-boolean term "OR 0=1", but from V7 (with
APAR PQ77454), optimizer does prune some boolean term predicates that can
never be true, such as WHERE 0=1.

To answer your original question, any type of trick is absolutely a last
resort in my opinion.

The preferred approach is instead to determine what the root cause is and
resolve that. So that other queries don't suffer the same issue. You don't
want to have to use tricks on every similar SQL if the problem can be
resolved at the table or index level.

So why is the optimizer not choosing the "correct" index? Unknown data
skew or correlation? Predicates that are difficult for the optimizer to
cost correctly?

The risks are that you don't resolve the real problem (as I mention
above), maintainability of the statement, CPU overhead of the trick etc.

Regards
Terry Purcell

On Sat, 31 Dec 2005 13:59:11 +1100, James Campbell
<[login to unmask email]> wrote:

>I recall reading somewhere that the DB2 zOS V8 optimizer has the smarts
to
>discard the "or 0=1" predicate as it can never be true. You can draw
your own
>conclusions.
>
>The officially documented method is to use "+ 0" for numeric items
or "concat ''"
>(that's a zero length string) for character items to disable
indexability. (Admin Guide,
>predicate indexabilty section, one of the notes after the chart.)
>
>The other alternative is an optimizer hint.
>
>James Campbell
>
>On 30 Dec 2005 at 15:49, Pradeep Gunjala wrote:
>
>> Hello List,
>>
>> We know that optimizer is not chosing the right index for one of our
SQL
>> so we have suggested to use 0 = 1 predicate to influence the access
path
>> so that it would consider the right index. But are there any
>> disadavantages with this approach down the road. Does IBM has stated in
>> any manual to use this as a lost resort. I was asked by our management
and
>> I need your inputs.
>>
>> Thanks in advance.
>>
>> Wish you all a Very Happy New Year 2006 !!
>>
>> - Pradeep

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Terry Purcell

Re: Any disadvantages by using 0 = 1 predicate
(in response to Pradeep Gunjala)
Pradeep,

The problem I have with using "OR 0=1" on the BETWEEN predicate is that
sometimes it will provide filtering, but the optimizer will never be able
to exploit this. What's worse is that you make this a stage 2 predicate.

As you are probably aware, REOPT(VARS) is designed exactly for this
situation. Another approach is to have 2 separate SQLs and let the
application decide which to use.

A 3rd (and potentially better) approach is to add COLA to IX2, such that
it contains COLB, COLA. If the optimizer chooses this index, then you will
always have matchcols = 2, and when there is filtering you get the benefit
of both predicates, and when there is no filtering on COLA, then there was
little harm done.

Regards
Terry

On Sat, 31 Dec 2005 14:14:46 -0500, Pradeep Gunjala
<[login to unmask email]> wrote:

>Terry,
>
>Thanks a lot for your reply. I agree. Let me explain you the scenario.
>
>IX1 is defined on COLA
>IX2 is defined on COLB
>
>SQL:
>
>Select .........FROM TAB
>WHERE COLA BETWEEN :HV1 AND :HV2
> AND COLB = :HV3
>
>But HV1 and HV2 variable are optional that means in all the cases these
>values may not be known to application, so the developers are populating
>with Low values in HV1 and High Values in HV2 respectively in those cases.
>Even though access path shows index access (IX1) it has to scan the entire
>index space there by table space because of Low values and High values in
>HV1 and HV2.
>But the application will always provide the HV3 value, so we decided to
>force the access path to consider the second index IX2, so that we don't
>end up looking the entire index space of IX1.
>
>Do you see any harm in long term using this approach.
>
>Thanks,
>Pradeep
>
>
>
>
>
>Terry Purcell <[login to unmask email]>
>Sent by: DB2 Data Base Discussion List <[login to unmask email]>
>12/31/2005 01:38 PM
>Please respond to
>DB2 Database Discussion list at IDUG <[login to unmask email]>
>
>
>To
>[login to unmask email]
>cc
>
>Subject
>Re: [DB2-L] Any disadvantages by using 0 = 1 predicate
>
>
>
>
>
>
>Pradeep,
>
>The optimizer doesn't prune non-boolean term "OR 0=1", but from V7 (with
>APAR PQ77454), optimizer does prune some boolean term predicates that can
>never be true, such as WHERE 0=1.
>
>To answer your original question, any type of trick is absolutely a last
>resort in my opinion.
>
>The preferred approach is instead to determine what the root cause is and
>resolve that. So that other queries don't suffer the same issue. You don't
>want to have to use tricks on every similar SQL if the problem can be
>resolved at the table or index level.
>
>So why is the optimizer not choosing the "correct" index? Unknown data
>skew or correlation? Predicates that are difficult for the optimizer to
>cost correctly?
>
>The risks are that you don't resolve the real problem (as I mention
>above), maintainability of the statement, CPU overhead of the trick etc.
>
>Regards
>Terry Purcell
>
>On Sat, 31 Dec 2005 13:59:11 +1100, James Campbell
><[login to unmask email]> wrote:
>
>>I recall reading somewhere that the DB2 zOS V8 optimizer has the smarts
>to
>>discard the "or 0=1" predicate as it can never be true. You can draw
>your own
>>conclusions.
>>
>>The officially documented method is to use "+ 0" for numeric items
>or "concat ''"
>>(that's a zero length string) for character items to disable
>indexability. (Admin Guide,
>>predicate indexabilty section, one of the notes after the chart.)
>>
>>The other alternative is an optimizer hint.
>>
>>James Campbell
>>
>>On 30 Dec 2005 at 15:49, Pradeep Gunjala wrote:
>>
>>> Hello List,
>>>
>>> We know that optimizer is not chosing the right index for one of our
>SQL
>>> so we have suggested to use 0 = 1 predicate to influence the access
>path
>>> so that it would consider the right index. But are there any
>>> disadavantages with this approach down the road. Does IBM has stated in
>>> any manual to use this as a lost resort. I was asked by our management
>and
>>> I need your inputs.
>>>
>>> Thanks in advance.
>>>
>>> Wish you all a Very Happy New Year 2006 !!
>>>
>>> - Pradeep

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Pradeep Gunjala

Re: Any disadvantages by using 0 = 1 predicate
(in response to Terry Purcell)
Most probably I am going to suggest to split the sql into 2 separate SQLs.
Thanks for your help. Have a Great New Year ahead !!




Terry Purcell <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/31/2005 02:24 PM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] Any disadvantages by using 0 = 1 predicate






Pradeep,

The problem I have with using "OR 0=1" on the BETWEEN predicate is that
sometimes it will provide filtering, but the optimizer will never be able
to exploit this. What's worse is that you make this a stage 2 predicate.

As you are probably aware, REOPT(VARS) is designed exactly for this
situation. Another approach is to have 2 separate SQLs and let the
application decide which to use.

A 3rd (and potentially better) approach is to add COLA to IX2, such that
it contains COLB, COLA. If the optimizer chooses this index, then you will
always have matchcols = 2, and when there is filtering you get the benefit
of both predicates, and when there is no filtering on COLA, then there was
little harm done.

Regards
Terry

On Sat, 31 Dec 2005 14:14:46 -0500, Pradeep Gunjala
<[login to unmask email]> wrote:

>Terry,
>
>Thanks a lot for your reply. I agree. Let me explain you the scenario.
>
>IX1 is defined on COLA
>IX2 is defined on COLB
>
>SQL:
>
>Select .........FROM TAB
>WHERE COLA BETWEEN :HV1 AND :HV2
> AND COLB = :HV3
>
>But HV1 and HV2 variable are optional that means in all the cases these
>values may not be known to application, so the developers are populating
>with Low values in HV1 and High Values in HV2 respectively in those
cases.
>Even though access path shows index access (IX1) it has to scan the
entire
>index space there by table space because of Low values and High values
in
>HV1 and HV2.
>But the application will always provide the HV3 value, so we decided to
>force the access path to consider the second index IX2, so that we don't
>end up looking the entire index space of IX1.
>
>Do you see any harm in long term using this approach.
>
>Thanks,
>Pradeep
>
>
>
>
>
>Terry Purcell <[login to unmask email]>
>Sent by: DB2 Data Base Discussion List <[login to unmask email]>
>12/31/2005 01:38 PM
>Please respond to
>DB2 Database Discussion list at IDUG <[login to unmask email]>
>
>
>To
>[login to unmask email]
>cc
>
>Subject
>Re: [DB2-L] Any disadvantages by using 0 = 1 predicate
>
>
>
>
>
>
>Pradeep,
>
>The optimizer doesn't prune non-boolean term "OR 0=1", but from V7 (with
>APAR PQ77454), optimizer does prune some boolean term predicates that can
>never be true, such as WHERE 0=1.
>
>To answer your original question, any type of trick is absolutely a last
>resort in my opinion.
>
>The preferred approach is instead to determine what the root cause is and
>resolve that. So that other queries don't suffer the same issue. You
don't
>want to have to use tricks on every similar SQL if the problem can be
>resolved at the table or index level.
>
>So why is the optimizer not choosing the "correct" index? Unknown data
>skew or correlation? Predicates that are difficult for the optimizer to
>cost correctly?
>
>The risks are that you don't resolve the real problem (as I mention
>above), maintainability of the statement, CPU overhead of the trick etc.
>
>Regards
>Terry Purcell
>
>On Sat, 31 Dec 2005 13:59:11 +1100, James Campbell
><[login to unmask email]> wrote:
>
>>I recall reading somewhere that the DB2 zOS V8 optimizer has the smarts
>to
>>discard the "or 0=1" predicate as it can never be true. You can draw
>your own
>>conclusions.
>>
>>The officially documented method is to use "+ 0" for numeric items
>or "concat ''"
>>(that's a zero length string) for character items to disable
>indexability. (Admin Guide,
>>predicate indexabilty section, one of the notes after the chart.)
>>
>>The other alternative is an optimizer hint.
>>
>>James Campbell
>>
>>On 30 Dec 2005 at 15:49, Pradeep Gunjala wrote:
>>
>>> Hello List,
>>>
>>> We know that optimizer is not chosing the right index for one of our
>SQL
>>> so we have suggested to use 0 = 1 predicate to influence the access
>path
>>> so that it would consider the right index. But are there any
>>> disadavantages with this approach down the road. Does IBM has stated
in
>>> any manual to use this as a lost resort. I was asked by our management
>and
>>> I need your inputs.
>>>
>>> Thanks in advance.
>>>
>>> Wish you all a Very Happy New Year 2006 !!
>>>
>>> - Pradeep

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm