471,897 Members | 2,054 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

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

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

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.