SQL & PL/SQL

I am on Oracle 10.2.0.3 on solaris.
I have a query that uses a MASTER table and CHILD table.
The STATUS_V column in CHILD table can have 3 distinct values :
PASS,FAIL, UNSUCCESSFUL.
Also the child table has about 160000 rows

The Child Table has data like this :
ID_V CSN_V STATUS_V
183 1795752 PASS
183 1795752 PASS
210 1701476 FAIL
210 1701476 PASS
210 1701476 PASS
212 1701478 FAIL
212 1701478 FAIL
210 1705535 UNSUCCESSFUL
214 1234566 UNSUCCESSFUL
214 1234566 PASS
217 1234569 FAIL
217 1234569 UNSUCCESSFUL

For a combination of (ID_V,CSN_V), if all records have pass, I want to display all records all records for that combination as PASS.
For a combination of (ID_V,CSN_V), if all records have fail, I want to display all records all records for that combination as FAIL.
For a combination of (ID_V,CSN_V), if all records have unsuccessful, I want to display all records all records for that combination as UNSUCCESSFUL.
For a combination of (ID_V,CSN_V), if there are atleast 2 distinct types(either a PASS, FAIL OR either a PASS, UNSUCCESSFUL or even FAIL, UNSUCESSFUL, or last but not least, if all three appear in the table, I want to display all records for that combination as FAIL.

In other words, my query should return :

183 1795752 PASS
183 1795752 PASS
210 1701476 FAIL
210 1701476 FAIL
210 1701476 FAIL
212 1701478 FAIL
212 1701478 FAIL
210 1705535 UNSUCCESSFUL
214 1234566 FAIL
214 1234566 FAIL
217 1234569 FAIL
217 1234569 FAIL


I wrote a query to get this and it works as far as accuracy of results are concerned!! Unfortunately it takes about an hour to execute. The explain plan looks okay with no warning signs(as viewed in toad) and uses the right indexes. I suspect that the sorting might be happening on disk as opposed to doing all in memory.

Here's my query :


SELECT B.ID_V,B.CSN_V,'FAIL'
FROM CHILD B,MASTER A
where B.AUTOID=A.AUTOID(+) AND
(b.ID_V,b.csn_v) not in
(SELECT ID_V,csn_v from CHILD where (STATUS_V='PASS' or STATUS_V='UNSUCCESSFUL')
)
and b.STATUS_V='FAIL'

UNION ALL
(
SELECT
B.ID_V,B.CSN_V,'UNSUCCESSFUL'
FROM CHILD B,MASTER A
where B.AUTOID=A.AUTOID(+) AND
(b.ID_V,b.csn_v) not in
(SELECT ID_V,csn_v from CHILD where (STATUS_V='FAIL' OR STATUS_V='PASS')
)
and b.STATUS_V='UNSUCCESSFUL'
)
UNION ALL
(
SELECT
B.ID_V,B.CSN_V,'PASS'
FROM CHILD B,MASTER A
where B.AUTOID=A.AUTOID(+) AND
(b.ID_V,b.csn_v) not in
(SELECT ID_V,csn_v from CHILD where (STATUS_V='FAIL' or STATUS_V='UNSUCCESSFUL')
)
and b.STATUS_V='PASS'
)
UNION ALL
(
SELECT
B.ID_V,B.CSN_V,'FAIL' FROM CHILD B,MASTER A
where B.AUTOID=A.AUTOID(+) AND
(b.ID_V,b.csn_v) in
(SELECT ID_V,csn_v from CHILD where STATUS_V in('PASS','UNSUCCESSFUL')
)
and b.STATUS_V='FAIL'
)
UNION ALL
(
SELECT
B.ID_V,B.CSN_V,'FAIL'
FROM CHILD B,MASTER A
where B.AUTOID=A.AUTOID(+) AND
(b.ID_V,b.csn_v) in
(SELECT ID_V,csn_v from CHILD where STATUS_V in('FAIL','UNSUCCESSFUL')
)
and b.STATUS_V='PASS'
)
UNION ALL
(
SELECT
B.ID_V,B.CSN_V,'FAIL'
FROM CHILD B,MASTER A
where B.AUTOID=A.AUTOID(+) AND
(b.ID_V,b.csn_v) in
(SELECT ID_V,csn_v from CHILD where STATUS_V in('FAIL','PASS')
)
and b.STATUS_V='UNSUCCESSFUL'
)



My question is :

Is there a different fashion or a less complicated query that I can write to achive this? Please suggest

Thanks