Re: effective SELECT from child tables

On Wed, 2005-09-28 at 12:13 +0400, Ilia Kantor wrote:
> >> Let table A be inherited by A1, A2, A3.
> >> How to select from A records where actual relations are A1, A2 ?
>
> >Why not just select directly from the child tables? I can't get excited
> >about optimizing the case you propose.
>
> Because "WHERE concrete_class IN (a,b,c)" is much more convenient and
> flexible way of forming select then manually split request into many unions.

> Also, this query runs on top of "abstract class", so inheritance really
> assists me here.
>

If you treat the sub-class Discriminator as a data item rather than some
additional syntax for class membership then you will find this works
very straightforwardly for you.

Include the Discriminator as a column in A and it will be inherited by
all A1, A2, A3.
e.g. concrete_class char(1) not null

then setup constraints on each table like so

ALTER TABLE A1 ADD CHECK (concrete_class = 'A')
ALTER TABLE A2 ADD CHECK (concrete_class = 'B')
ALTER TABLE A3 ADD CHECK (concrete_class = 'C')

then when you run a query like

SELECT * FROM A WHERE concrete_class IN ('A','B')

you will find that table A3, which corresponds to concrete_class C has
been excluded from your query.

Presumably A, B, C are all mutually exclusive, so the end result will be
the same as if you had used a UNION ALL set query.

This will add 1 byte per row in your superclass... and requires no
index. You can even add this as a DEFAULT value for each child table, so
the actual column concrete_class need not be mentioned in an INSERT
statement.

(I've got plans to add an ABSTRACT keyword to tables to follow along the
same set of OO terminology in describing this situation. In next
release, not 8.1)

Best Regards, Simon Riggs