I have several schemes, each containing the same two tables.
The two tables have the following structure:
create table dav_samplesetattribute
(
key int8,
poresulttype int4 not null,
calculationerrorcode int4,
causedbypo int4,
numberofsamples int4,
sampletype int4,
samplesize int4,
physicalunit int4,
physicalvalue int4,
constraint dav_samplesetattribute_pk primary key (key)
);
create table dav_sample
(
key int8,
samplenumber int4,
samplestate int4 not null,
sampleerrorcode int4 not null,
limitlevel int4,
variablesamplesize int4,
integersample int8,
realsample double precision,
charsample varchar(10485780),
constraint sample_pk primary key(key, samplenumber),
constraint sample_fk foreign key (key) references dav_samplesetattribute(key)
);
Some of the fields are meant to get a not unique index (for example the field
integersample).
Each of the tables in each scheme will store a lot rows. That is also why I
decided to split the tables over the schemes.
Now (for queries) I want to unite all the tables of all schemes via one view.
Therefore I first declared one view uniting all 'dav_samplesetattribute' tables
of all schemes and a second view uniting all 'dav_sample' tables.
The view definitions use the UNION-clause to unite the equal tables from all
schemes. One field is added to the view representing the name of the according
scheme. My first question is, if there exists a possibility to add an index on
the views new field, representing the name of the scheme?
After having declared these two views, I declare a third view, uniting the
former two views via OUTER JOIN.
Now, if I execute queries on this view, none of my indexes seem to be used.
Queries on the other two views, use my indexes though. Are indexes not used on
view queries, if a view is based on other views?
I'd also welcome any common tips on views-index usage (known problems for
example).
Thanks Andreas.
-------------------------------------------------
This mail sent through IMP: http://horde.org/imp/