Hi,
I have a view defined as :
create view calling_view as
(
select d.*,c.patient_id as id_m,c.result as r_m from
(select a.*,b.patient_id as id_f,b.result as r_f from
( select substr(a.family_id,1,4) as fid,b.* from denver_person a,
luminex b
where a.id=b.patient_id and b.project='Denver' and
strpos(a.family_id,'C')>0) a
left join
( select substr(a.family_id,1,4) as fid,b.* from denver_person a,
luminex b
where a.id=b.patient_id and b.project='Denver' and
strpos(a.family_id,'F')>0) b
on a.fid=b.fid and a.marker=b.marker
) d
left join
(select substr(a.family_id,1,4) as fid,b.* from denver_person a, luminex b
where a.id=b.patient_id and b.project='Denver' and strpos(a.family_id,'M')>1
) c
on d.fid=c.fid and d.marker=c.marker
)
Looks ugly, but it works. The weird thing is on 8.0.1, when I execute
"select * from calling_view", it works very well, after a few seconds, I am
able to get results; but when I execute "select * from view_name WHERE
MARKER='blabla'", I never be able to get result, and CPU usage is near 100%.
In linux, I used "ctrl-c" to terminate it, but a process still running at
background, took a lot of cpu time. By the way, it works very well on
version 7.4.7.
I have tried Linux version and Windows version, same error occured.
My hardware is: CPU amd athlon 64 3000+ ,1G single channel memory , via
chipset, sata harddrive.
Linux version is 2.6.10 64bit, xfs filesystem
Windows version is windows xp service pack2.
I love 8.0 version, for my recent project, I got huge performance
improvement when I upgrade from 7 version to 8 version.
In my opinion, it might be SQL explainer or optimizer problem due to my
similar experience on DB2.
Thanks very much for your help.
Richard