Z/OS DB2 V7:: SQL Tuning Basic Question

Vijayababu Sriramulu

Z/OS DB2 V7:: SQL Tuning Basic Question
Dear list users,

This is one of the basic beginner question I have.

When do we use the predicates 0=1 and 1=1 while tuning queries and what
are their implications ?

Any hints/ideas/links is greatly appreciated.

As always, thanks for your time.

Regards
Vijay



---------------------------------------------------------------------------------
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

Alexandros Papadopoulos

Re: Z/OS DB2 V7:: SQL Tuning Basic Question
(in response to Vijayababu Sriramulu)
Hello Vijay,



A quick try:

0=1 (always false) is sometimes ORed with another predicate in order to make
it not indexable.

1=1 (always true) can be used to force the materialization of a view



For more details search in Admin Guide for "0=1" and for "1=1"



hth

Alekos

----------------------------------------------------------------------------
----- 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], êáèþò êáé óôç óõíÝ÷åéá íá äéáãñÜøåôå ôï ìÞíõìá áðü ôï óýóôçìÜ óáò.
Ïé åðéêïéíùíßåò ìÝóù ôïõ Äéáäéêôýïõ äåí åßíáé áóöáëåßò êáé ãéá ôïí ëüãï áõôü ï ¼ìéëïò Åôáéñéþí ôçò ÅèíéêÞò ÔñÜðåæáò ôçò ÅëëÜäïò äåí áðïäÝ÷åôáé íïìéêÞ åõèýíç ãéá ôá ðåñéå÷üìåíá ôïõ ðáñüíôïò ìçíýìáôïò êáé ãéá ïðïéáäÞðïôå æçìéÜ ðñïêëçèåß áðü éïýò ðïõ åßíáé äõíáôüí íá åéóáãÜãåé. Ïé áðüøåéò ðïõ äéáôõðþíïíôáé áíÞêïõí áðïêëåéóôéêÜ óôïí áðïóôïëÝá ôïõ ìçíýìáôïò êáé äåí áíôéðñïóùðåýïõí áðáñáßôçôá ôéò áðüøåéò ôïõ Ïìßëïõ Åôáéñéþí ôçò ÅèíéêÞò ÔñÜðåæáò ôçò ÅëëÜäïò.
Óáò Åõ÷áñéóôïýìå,
¼ìéëïò Åôáéñéþí ÅèíéêÞò ÔñÜðåæáò ôçò ÅëëÜäïò

Email Disclaimer
The information in this email is confidential and is intended solely for the addressee(s). If you have received this transmission in error, and you are not an intended recipient, be aware that any disclosure, copying, distribution or use of this transmission or its contents is prohibited. Furthermore, you are kindly requested to send us back the original message at the address [login to unmask email], and delete the message from your system immediately.
Internet communications are not secure and therefore the National Bank of Greece Group does not accept legal responsibility for the contents of this message and for any damage whatsoever that is caused by viruses being passed. Any views or opinions presented are solely those of the author and do not necessarily represent those of National Bank of Greece Group.
Thank You,
National Bank of Greece Group
***************************************************************************************

---------------------------------------------------------------------------------
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: Z/OS DB2 V7:: SQL Tuning Basic Question
(in response to Alexandros Papadopoulos)
"0=1" has often been used to "trick" the optimiser into not choosing
to access data via an specific index. For example
AND (COL1 = :hv OR 0=1)
The optimiser would have to check every index entry on the off
chance that the "0=1" predicate were true - not just the few that
satisfy the other predicate. The extra getpages needed for this
should have been enough to discourage access via an index with
COL1 as its first column.

However since V7 the optimzer has the smarts to know that "0=1"
is never true, so it will only check for "COL1 = :hv" - fewer
getpages - more likely the index will be used.

In V7, and later, the officially documented way of doing this is to
code
AND COL1 = :hv + 0 (for numeric columns)
or
AND COL1 = :hv CONCAT '' (for character columns.)
http://publibz.boulder.ibm.com/cgi-
bin/bookmgr_OS390/BOOKS/DSNAGH14/5.8.3.2 Note 9 states
that using this technique the index is "not indexable", rather than
simple discouraged.

James Campbell

On 30 Nov 2005 at 16:29, Sriramulu, Vijayababu wrote:

>
> Dear list users,
> This is one of the basic beginner question I have.
> When do we use the predicates 0=1 and 1=1 while tuning queries and what are their implications
> ?
> Any hints/ideas/links is greatly appreciated.
> As always, thanks for your time.
> Regards
> Vijay
>
>
> --------------------------------------------------------------------------------- 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 DB2-L-
> [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