473,386 Members | 1,721 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,386 software developers and data experts.

Why is this happening in `Enable Query Optimization`

Hi all,

db2 => create table emp(empno int not null primary key, \
db2 (cont.) => sex char(1) not null constraint s_check check \
db2 (cont.) => (sex in ('m','f')) \
db2 (cont.) => not enforced \
db2 (cont.) => enable query optimization)
DB20000I The SQL command completed successfully.

db2 => insert into emp values(1,'m')
DB20000I The SQL command completed successfully.
db2 => insert into emp values(2,'f')
DB20000I The SQL command completed successfully.
db2 => insert into emp values(2,'q')
db2 => insert into emp values(3,'q')
DB20000I The SQL command completed successfully.

db2 => select * from emp

EMPNO SEX
----------- ---
1 m
2 f
3 q

3 record(s) selected.

db2 => select * from emp where sex = 'q'

EMPNO SEX
----------- ---

0 record(s) selected.
db2 => select * from emp where sex <> 'm'

EMPNO SEX
----------- ---
2 f
3 x

2 record(s) selected.
I have add the enable query optimization clause.. But how come the
last query is showing records with sex x??
According to doc "The ENABLE QUERY OPTIMIZATION keyword also allowed
DB2 to use this constraint information when optimizing SQL
statements."

I am using db2 8.1

Thiru.
WantedToBeDBA.
Nov 12 '05 #1
12 6143
In article <e6**************************@posting.google.com >,
WantedToBeDBA (Wa***********@gmail.com) says...


db2 => select * from emp where sex <> 'm'

EMPNO SEX
----------- ---
2 f
3 x

2 record(s) selected.


Hi,

I can't reproduce this on DB2 8.2 with FP8, it shows the expected
results:

select * from emp

EMPNO SEX
----------- ---
1 m
2 f
3 q

3 record(s) selected.
select * from emp where sex = 'q'

EMPNO SEX
----------- ---

0 record(s) selected.
select * from emp where sex <> 'm'

EMPNO SEX
----------- ---
2 f
3 q

2 record(s) selected.
Nov 12 '05 #2
Gert van der Kooij wrote:
In article <e6**************************@posting.google.com >,
WantedToBeDBA (Wa***********@gmail.com) says...

db2 => select * from emp where sex <> 'm'

EMPNO SEX
----------- ---
2 f
3 x

2 record(s) selected.

Hi,

I can't reproduce this on DB2 8.2 with FP8, it shows the expected
results:

select * from emp

EMPNO SEX
----------- ---
1 m
2 f
3 q

3 record(s) selected.
select * from emp where sex = 'q'

EMPNO SEX
----------- ---

0 record(s) selected.
select * from emp where sex <> 'm'

EMPNO SEX
----------- ---
2 f
3 q

2 record(s) selected.

I also cannot possible come up with way for DB2 to invent 'x' on its
own. DB2 doesn't know constants that are not in the query, the data or
the DDL....

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3
Gert,

I guess that during compilation of
select * from emp where sex = 'q'
the optimizer recognized the check constraint sex in ('m','f') and
eliminated the select altogether. Should the constraint be enforced, no
rows would meet the search criteria sex = 'q'. The optimizer has acted
as if the constraint were enforced, which is exactly as it should.

Regarding
select * from emp where sex <> 'm'
I guess the execution plan was tablespace scan, matching rows against
the search criteria
sex <> 'm'

I don't see any problem here, all worked as it should.

What do you think?

Nov 12 '05 #4
In article <11*********************@l41g2000cwc.googlegroups. com>,
(ak************@yahoo.com) says...
Gert,

I guess that during compilation of
select * from emp where sex = 'q'
the optimizer recognized the check constraint sex in ('m','f') and
eliminated the select altogether. Should the constraint be enforced, no
rows would meet the search criteria sex = 'q'. The optimizer has acted
as if the constraint were enforced, which is exactly as it should.

Regarding
select * from emp where sex <> 'm'
I guess the execution plan was tablespace scan, matching rows against
the search criteria
sex <> 'm'

I don't see any problem here, all worked as it should.

What do you think?


You should read the first post :)
I didn't have a problem, it was Thiru (a.k. WantedToBeDBA) who had a
problem when running the last SQL, it returned an 'x':
db2 => select * from emp where sex <> 'm'

EMPNO SEX
----------- ---
2 f
3 x

2 record(s) selected.

Nov 12 '05 #5
Hi all,
Sorry for some mistakes in early post.
Here is the correct one.
db2 => create table emp(empno int not null primary key, \
db2 (cont.) => sex char(1) not null constraint s_check check \
db2 (cont.) => (sex in ('m','f')) \
db2 (cont.) => not enforced \
db2 (cont.) => enable query optimization)
DB20000I The SQL command completed successfully.

db2 => insert into emp values(1,'m')
DB20000I The SQL command completed successfully.
db2 => insert into emp values(2,'f')
DB20000I The SQL command completed successfully.
db2 => insert into emp values(3,'x')
DB20000I The SQL command completed successfully.

db2 => select * from emp

EMPNO SEX
----------- ---
1 m
2 f
3 x

3 record(s) selected.

db2 => select * from emp where sex = 'x'

EMPNO SEX
----------- ---

0 record(s) selected.
db2 => select * from emp where sex <> 'm'

EMPNO SEX
----------- ---
2 f
3 x

2 record(s) selected.
I have add the enable query optimization clause.. But how come the
last query is showing records with sex x??

My question is "If query optimization is enabled and the query has
been issued, the informational constraint having check values m and f
has to be considered." Am i Right??? Can any one justify for final
query response??

db2level
--------
DB21085I Instance "DB2" uses "32" bits and DB2 code release
"SQL08010" with
level identifier "01010106".
Informational tokens are "DB2 v8.1.0.36", "s021023", "", and FixPak
"0".
Product is installed at "C:\PROGRA~1\IBM\SQLLIB".

Thiru.
WantedToBeDBA.

Gert van der Kooij <ge**@invalid.nl> wrote in message news:<MP************************@news.xs4all.nl>.. .
In article <11*********************@l41g2000cwc.googlegroups. com>,
(ak************@yahoo.com) says...
Gert,

I guess that during compilation of
select * from emp where sex = 'q'
the optimizer recognized the check constraint sex in ('m','f') and
eliminated the select altogether. Should the constraint be enforced, no
rows would meet the search criteria sex = 'q'. The optimizer has acted
as if the constraint were enforced, which is exactly as it should.

Regarding
select * from emp where sex <> 'm'
I guess the execution plan was tablespace scan, matching rows against
the search criteria
sex <> 'm'

I don't see any problem here, all worked as it should.

What do you think?


You should read the first post :)
I didn't have a problem, it was Thiru (a.k. WantedToBeDBA) who had a
problem when running the last SQL, it returned an 'x':
db2 => select * from emp where sex <> 'm'

EMPNO SEX
----------- ---
2 f
3 x

2 record(s) selected.

Nov 12 '05 #6
DB2 today does not consider that if you exclude 'm' only 'f' and NULL
are left over.
Note that ENABLE is not equal to MUST ;-)
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #7
Hi Serge,
Does that mean that we can't trust the output from a table
containing informational contraints??

Thiru.
WantedToBeDBA.

Nov 12 '05 #8
Thiru wrote:
Hi Serge,
Does that mean that we can't trust the output from a table
containing informational contraints??

Thiru.
WantedToBeDBA.

If DB2 cannot trust the app to put correct information inside, of course
not.
Not that that this doe snot even require informational constraints.
You can use SET INTEGRITY IMMEDIATE UNCHECKED to aqccept badly loaded
data. IMMEDIATE UNCHECKED can also cause trouble when altering a
generated column, or worse: If you use an external UDF in a generetaed
column or a check constraint and you change the implementation of the UDF.

Check out the word "assumed" below:
ENABLE QUERY OPTIMIZATION or DISABLE QUERY OPTIMIZATION
Specifies whether the constraint or functional dependency can be
used for query optimization under appropriate circumstances. The default
is ENABLE QUERY OPTIMIZATION.

ENABLE QUERY OPTIMIZATION
The constraint is _assumed_to_be_true_ and can be used for
query optimization.
DISABLE QUERY OPTIMIZATION
The constraint cannot be used for query optimization.
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #9
Thanks Serge for making me see things in black and white.

Thiru.

Nov 12 '05 #10
Serge,

Any chance that we'll soon see informational constraint versions of
primary keys and unique constraints?

Ken

Nov 12 '05 #11
kenfar wrote:
Serge,

Any chance that we'll soon see informational constraint versions of
primary keys and unique constraints?

Ken

You mean like this?:
http://publib.boulder.ibm.com/infoce...n/r0002169.htm

I haven't heard about this requirement for tables thus far.
You would want informational uniqueness without the index to go with it?
(it's the index that's costly, having a non unique index and an
informational unique constraint IMHO makes little sense)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #12
Serge Rielau wrote:
kenfar wrote:
Serge,

Any chance that we'll soon see informational constraint versions of
primary keys and unique constraints? You mean like this?:
http://publib.boulder.ibm.com/infoce...n/r0002169.htm


yep, but for tables rather than nicknames
I haven't heard about this requirement for tables thus far.
You would want informational uniqueness without the index to go with it? (it's the index that's costly, having a non unique index and an
informational unique constraint IMHO makes little sense)


In warehousing environments there are sometimes performance reasons for
avoiding primary keys & unique constraints. According to documentation
regarding star joins, primary keys on dimensions are a requirement:
http://publib.boulder.ibm.com/infoce...onstraints.htm
Unfortunately, some dimensions (customer, household, etc) can easily
grow to tens of millions of rows.

I've automated data quality testing on our warehouse - by generating
tests out of the constraints defined in the catalog. Even though we're
careful about managing data quality, this speeds up testing and catches
the occasional problem. I've currently got unique constraints defined
on a number of seldom-used columns - and would prefer to rely on our
etl application & test harness than db2.

ken

Nov 12 '05 #13

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: MBS | last post by:
I know there are already a million threads on php vs asp.net. My prior experience is with asp but I am now doing a website for a company that has given me free reign over which technology I want to...
6
by: Juho Saarikko | last post by:
The program attached to this message makes the Python interpreter segfault randomly. I have tried both Python 2.2 which came with Debian Stable, and self-compiled Python 2.3.3 (newest I could find...
5
by: AC Slater | last post by:
Whats the simplest way to change a single stored procedures query optimization level? In UDB8 that is. /F
11
by: 73blazer | last post by:
We are migrating a customer from Version 7.1 FP3, to Version 8.2 (8.1 FP8). For the most part, things are faster, but there is one query that is much much slower, and it is a query that is used all...
3
by: Vjay77 | last post by:
As a result from who is I am getting this: > > NOTICE AND TERMS OF USE: You are not authorized to access or query our WHOIS database through the use of high-volume, automated, electronic...
5
by: mathieu.page | last post by:
Hi, I often have recursives queries in my applications, like in this simplified example : req1 : SELECT EmployeNo, EmployeName, EmployePhone FROM Employe; req2 :
1
by: sendtosanju | last post by:
HI, How can I optimize my delete query that currently takes less than 1 min . I want to improve my query such that it take only few 2-3 secs. My query is DELETE FROM table1 R WHERE ROWID...
38
by: Mark Dickinson | last post by:
I get the following behaviour on Python 2.5 (OS X 10.4.8 on PowerPC, in case it's relevant.) (0.0, 0.0) (-0.0, -0.0) I would have expected y to be -0.0 in the first case, and 0.0 in the...
1
by: madankarmukta | last post by:
Hi, One I get the query plan for a particular query.. Can anybody provide me the guidelines for how to proceed for optimization ? Thanks!
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.