need help with long query

From: Lynn Ranen <ranen_at_ix.netcom.com>
Date: Wed, 06 May 1998 20:05:09 +0100
Message-ID: <3550B465.3737D3AD@ix.netcom.com>

Hi --

The following SQL query is the basis for a PL/SQL procedure using DBMS_SQL that needs to be run several
times during a WWW-based product. The query takes so long that the user's

browsers are timing out!
When I run the following in SQL*Plus it takes 1.2 seconds (returns about 40

records)

These are the Table sizes:

chpa_test_product = 249 records
chpa_insurer = 38 records
chpa_county = 66 records

Does this seem reasonable to anyone? How can Oracle be the number 2 selling
software in the world with performance like this? I changed the ordering of
the WHERE CLAUSE to every possible combination and still only managed to

shave off about 2 seconds. The DB is Oracle Version 7, running on Sun

Solaris. I'm thinking it has to be a hardware issue, or that the DB is really messed up. Unfortunately, the DBA and UNIX Admin won't even look at

the situation. Is this query REALLY that bad?? Any thoughts appreciated.

Thanks -- Lynn

SELECT
A.full_product_number, A.ffn , A.plan_type, A.deductible,

A.benefit_level,A.tobacco, A.effdate, A.county_code, A.contribid, A.trendid,

A.copay, A.pcp, A.pos, B.insname
FROM chpa_test_product A,

     chpa_insurer B
WHERE B.ffn = A.ffn
AND A.county_id IN (select distinct F.id    from chpa_county F
   where lower(F.county) IN ('broward', 'dade')) AND A.plan_type IN ('HMO','PPO')
AND A.deductible IN (0,100,250,500,750,2000) AND LOWER(A.benefit_level) IN

                ('basic','standard','street','plusplan')

AND A.ffn IN (select distinct D.ffn

   from chpa_county D
   where lower(D.county) IN ('broward','dade')    AND D.ffn IN (select distinct C.ffn
     from chpa_insurer C)); Received on Wed May 06 1998 - 14:05:09 CDT