"Zeugswetter Andreas SB SD" <[EMAIL PROTECTED]> writes: > e.g if you have a constraint "acol integer, check acol < 5" > and you have a query with a "where acol = 10" you could reduce that > to "where false".
I think part of the question is how much work do you put into checking this. Checking constant known values like above is probably not too expensive. Checking for ranges like "where acol between 5 and 10" is probably doable. And that might be enough for partitioned tables. I think that's about all Oracle bothers to check, for example. More complex where clauses and check expressions might be hard to prove are true or false. But then the work's still not done, you still have to add an optimization that prunes members of a UNION ALL (or equivalent if it's done using inherited tables or some other infrastructure) if they are known to provably produce zero rows. And then there are more subtle cases. Like if the query is "where acol = ?". Then you know it only has to read one partition, but you don't know which one at compile time. And it's important to handle that case because that might be the only clause. So knowing that you only need one partition might be the difference between a sequential scan of one partition, or an index scan of many thousands of records because they're only a small percentage of the entire table. -- greg ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
