Hi,

I am using this query:

select * from untersuchungen
where patientenid in (select patientenid from informa.patienten where
name like 'becker%' and vorname like 'f%')

There are indexes on PatientenID both in "untersuchungen" and in
"patienten". Also indexes on "name" and "vorname".

Here the execution plan:

The following filter condition was applied to the untersuchungen table:

"patientenid" IN ( SELECT ALL "patientenid" AS "patientenid" FROM
"informa"."patienten" WHERE "Name" = 'becker' [Partial length match on 6
char(s)] AND
"Vorname" = 'f' [Partial length match on 1 char(s)] [Row scan (): 0
rows, 0 bytes
estimated cost] ORDER BY "patientenid") [Row scan (Untersuchungen):
74862 rows,
33538176 bytes estimated cost]

================================================================================
3 row(s) returned in 1,453 secs
================================================================================

When executing the subquery alone it is done in 0 seconds (3 records
returned). Executing the main query with the values of the subquery like
this:

select * from untersuchungen
where patientenid in (8179,17157,34435)

is done in 0 seconds as well.

Did I miss anything to optimize the query?

Regards Uli

Uli


What about rewriting it as a join?

Roy Lambert

Uli,

<< Did I miss anything to optimize the query? >>

It doesn't appear so.   Can you send me the database catalog ?

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

> What about rewriting it as a join?

Forgot to mention that I tried that. It even takes more time.

Uli Becker

Tim,

> It doesn't appear so.   Can you send me the database catalog ?

I sent the two catalogs by mail.

Regards Uli

Roy,

> What about rewriting it as a join?

Your are right. I tried a join, but it was the wrong one.

This code works very fast:

select * from untersuchungen u
 join informa.patienten p
on u.patientenID = p.PatientenID where
p.name like 'Becker%' and p.vorname like 'F%';

Thank you.

Uli