Re: [Bizgres-general] A Guide to Constraint Exclusion

On Fri, 2005-07-15 at 00:24 +0300, Hannu Krosing wrote:
> Btw, not just UNION ALL, but also simple UNION, INTERSECT and EXCEPT
> could probably be taught to use CE at some stage.

It turns out that to solve this problem you very nearly have to solve
the "any table" problem. Thats an extra argument in favour of making
this work for any table.

> That's why I asked for GUC, not a default behaviour ;)

> but what about _static_ exlusion based on constraints ?
> 
> I mean if there is a left side table with say a single partition having
>    CHECK(id_order BETWEEN 1 AND 1000)
> (either originally or left after eliminating other by other constraints)
> 
> and 3 right side partition with
>    CHECK(key_order BETWEEN 1 AND 1000)
>    CHECK(key_order BETWEEN 1001 AND 2000)
>    CHECK(key_order BETWEEN 2001 AND 3000)
> 
> then the 3rd one could be eliminated statically from a join on
> id_order=key_order

Well, SQL allows you to express almost any query, but that doesn't mean
it is all 3 of: frequently occcuring, meaningful and interesting.

Have you ever seen such a construct?

I think we might be able to use equivalence to show that a restriction
on one table could be translated into a restriction on the Fact table.

e.g.

SELECT
FROM Fact, OtherTable
WHERE Fact.Key = OtherTable.Key
AND OtherTable.Key > 28000

But the harder and yet more common problem is where there is no direct
restriction on the equivalent join column. Thats the one I would
eventually seek to solve

e.g.

SELECT
FROM Fact, Dimension
WHERE Fact.Key = Dimension.Key
AND Dimension.DescriptiveField = 'Blah'

where there is a relationship between DescriptiveField and Key enshrined
within the Dimension table.

> and thanks for the good work so far!

Thank you,

Best Regards, Simon Riggs