Slow Query

[ponderena]

ave a select statement as follows, which runs very slow. I think it is the count or the subselect, but
I do not see how I could optimize it. I tried using hints, Ordered and All Rows, but it did not do speed up
the retrieval. The tables are indexed.

SELECT CASE
WHEN (a.type = 'ResultType' AND a.type_num in (0,1,2,3,4,5,6,12,13) ) THEN 'RES1_' || UPPER(a.value)
WHEN (a.type = 'ResultType' AND a.type_num = 7) THEN 'RES2 ' || UPPER(a.value)
WHEN (a.type = 'ResultType' AND a.type_num in (7,8,9,10,11) ) THEN 'RES3. ' || UPPER(a.value)
ELSE UPPER(b.value) END AS Type,
COUNT(CASE WHEN EXISTS (SELECT 1 FROM SITUATIONS S1, SUBSITUATIONS SV
WHERE m.sit_id = S1.sit_id
AND (m.sit_id <> S1.sit_id or s.subsit_id <> S2.subsit_id)
AND m.sit_id <> S1.sit_id)
THEN NULL ELSE 1 END) singles,
COUNT(CASE WHEN EXISTS (SELECT 1 FROM SITUATIONS S1, SUBSITUATIONS SV
WHERE m.sit_id = S1.sit_id
AND (m.sit_id <> S1.sit_id or s.subsit_id <> S2.subsit_id)
AND S1.type_num = 0)
THEN 1 ELSE NULL END) doubles,
FROM (SELECT * FROM RESULTYPES WHERE ENUM_TYPE = 'ResultType1') b,
(SELECT * FROM RESULTYPES WHERE ENUM_TYPE = 'ResultType') a,
SITUATIONS m,
SUBSITUATIONS s
WHERE m.sit_id = s.subsit_id(+)
AND m.typ_num = b.type_num(+)
AND s.type_num = a.type_num(+)
GROUP BY CASE
WHEN (a.type = 'ResultType' AND a.type_num in (0,1,2,3,4,5,6,12,13) ) THEN 'RES1_' || UPPER(a.value)
WHEN (a.type = 'ResultType' AND a.type_num = 7) THEN 'RES2 ' || UPPER(a.value)
WHEN (a.type = 'ResultType' AND a.type_num in (7,8,9,10,11) ) THEN 'RES3. ' || UPPER(a.value)
ELSE UPPER(b.value)
ORDER BY Type;

Ponderena

 

[SantaMufasa]

Ponderena,

First of all, the code you posted should not "run very slow"...It should not run at all <smile>:[ul][li]There is an extraneous comma just prior to your initial "FROM".[/li][li]The table aliases "SV" and "S2" seem to be confused with one another (i.e., there is no "S2" alias creation, and you do not reference alias "SV".[/li][li]"m.typ_num" should be spelled "m.type_num".[/li][li]You are missing an "END" to terminate your "GROUP BY CASE..." statement.[/li][/ul]

We could provide specific tuning help if you post (minimal, yet functional) "CREATE TABLE..." statements and sample/contrived "INSERT INTO..." statements for tables, "RESULTTYPES", "SITUATIONS", and "SUBSITUATIONS".

When I need quick, rated tuning alternatives for a SQL statement, I find that TOAD's Quest SQL Optimizer/Tuning Lab (a component of TOAD) to be very useful.

Please provide the above, and also let us know if you have access to Quest SQL Optimizer.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”