469,625 Members | 1,914 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,625 developers. It's quick & easy.

Optimizer's use of check Constraints ... ?

Hi,

(DB2 LUW 8.2)

Is the DB2 optimiser able to use check constraints ?

e.g
table1
(
name varchar(30)
constraint c_upper check (name = UCASE(name))
)

CREATE INDEX table1_ix1 ON table1 (name ASC) ALLOW REVERSE SCANS;

would a query like

select * from table1 where ucase(name) = 'PAUL'

recognise that the index table1_ix1 was valid to be considered to be
used?

nb. at the moment we use indexed generated columns to achieve this.

Nov 7 '06 #1
1 1479
PaulR wrote:
Hi,

(DB2 LUW 8.2)

Is the DB2 optimiser able to use check constraints ?

e.g
table1
(
name varchar(30)
constraint c_upper check (name = UCASE(name))
)

CREATE INDEX table1_ix1 ON table1 (name ASC) ALLOW REVERSE SCANS;

would a query like

select * from table1 where ucase(name) = 'PAUL'

recognise that the index table1_ix1 was valid to be considered to be
used?

nb. at the moment we use indexed generated columns to achieve this.
The optimizer can use check constraints in some circumstances, such as
a UNION ALL view, but I don't think it would work in your example.

Nov 8 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.