Select Constraints ??

[ubstek]

Hello,

Two table: sales_2002 and sales_2003,with column Period in Sales_2002 having value 2002, and column Period in sales_2003, having value 2003. (among other coulmns)

Constraint is defined on the columns.

An union all is created called Sales of these two tables.

When a query is run with period=2003, the plan shows that it reads both the tables.

Is it possible to constraint the optimiser from quering both the tables ???

Thank you very much in advance.

 

[sathyarams]

Using a UNION ALL view alone will not mean anything to the optimizer ..

Define yours as :

create view sales as
select * from sales_2002 where period=2002
union all
select * from sales_2003 where period=2003

This gives the hint to the optimizer to read only sales_2003 if you want to read 2003's data ..

To eliminate human errors, you will have to define the base table with proper constraint so that you will not endup inserting 2002's data in sales_2003

http://www7b.software.ibm.com/dmdd/library/techarticle/

0202zuzarte/0202zuzarte.pdf

Don't miss to read Serge's post in :

http://dbforums.com/arch/39/2002/8/473729

HTH

Sathyaram

More DB2 questions answered at

http://www.dbforums.com/f8

 

[ubstek]

We did try definng the view as you mentioned, but on explain plan, both the tables show up.

We have UDB 8.1

Thanks for your reply.

 

[gregsimpson]

Ubstek,

as the optimiser isn't doing what it's documented to do, maybe you need a fix applying.

Just a thought, but could you consider partitioning your data in a similar way to the constraints. As of Version 4 you are supposed to be able to limit a tablespace scan to accessing a subset of the partitions if the predicates of the where clause can be used to limit the key ranges that need to be scanned.

Might do what you want.

Cheers
Greg