are views obsolete ???? optimizer error ????
Path: emory!swrinde!cs.utexas.edu!uunet!mcsun!unido!sbsvax!ukh840!becker
From: becker@ukh840.med-in.uni-sb.de
Newsgroups: comp.databases.informix
Message-ID: <15865@sbsvax.cs.uni-sb.de>
Date: 29 Jan 92 06:29:34 GMT
Sender: news@sbsvax.cs.uni-sb.de
Sirs
We have a table with a field izahl - the patients identification - and a
field hktnr - number of an examination.
dbschema:
# create table "becker".hkt_nr ##### env. 29000 entries in this table ####
# (
# izahl char(10),
# datum date,
# hktnr integer,
# klinik char(1),
# station char(4),
# ueberw smallint,
# dilnr integer,
# uhrzeit smallint,
# lasnr integer,
# asknr integer
# );
# create index "becker".hkt_nrd on "becker".hkt_nr (dilnr);
# create index "becker".hktnriz on "becker".hkt_nr (izahl);
# create unique index "becker".hkt_nr on "becker".hkt_nr (hktnr);
# create index "becker".hkt_nr_dat on "becker".hkt_nr (datum);
Every patient could be examined more than once.
Because of: select izahl, max(hktnr) ... works very fast,
I wanted to create a view, which gives the maximum hktnr to a patient.
# create view hkt_nr_max ( izahl, hktnr)
# as select x0.izahl, max(x0.hktnr) from hkt_nr x0
# { group by 1 ## this line has no influence to the behaviour}
When I now write: select * from hkt_nr_max where izahl = "GUI290321F",
I must wait long time. It seems, Informix builds the view for all entries
in the table into a temporary table and then selects the izahl. This
could be derived from the sqexplain-file. I think, this is an error
in Informix V4.0 - perhaps the optimizer works wrong.
Any Ideas?
Dieter
--
Dr. med. dipl.-math Dieter Becker Tel.: (0 / +49) 6841 - 16 3046
Medizinische Universitaets- und Poliklinik Fax.: (0 / +49) 6841 - 16 3369
Innere Medizin III
D - 6650 Homburg / Saar Email: becker@med-in.uni-sb.de