
> > With the rule system and two underlying tables one could make it work by 
> > hand I think.
> 
> The rule system could be used to do this, but there was some discussion of
> using inherited tables to handle it. However neither handles the really hard
> part of detecting queries that use only a part of the table and taking that
> into account in generating the plan.

I think the consensus should be to add smarts to the planner to include 
static constraint information to reduce table access.

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". This would help in all sorts of situations not only 
partitioned/inherited tables. I am not sure what the runtime cost of 
such an inclusion would be, so maybe it needs smarts to only try in certain 
cases ?

Andreas

Fri, 11 Jun 2004 12:02:40 +0200
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: Tablespaces

"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> 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


11 Jun 2004 11:33:19 -0400
Greg Stark <gsstark@mit.edu>
Re: Tablespaces