Optimizer's use of check Constraints ... ?

[PaulR]

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.

[Mark A]

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.