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.

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.