Very Computer

PLease can anybody help in finding the better of doing this. I have
tried three approaches. May be there is still better I don't know :(.

desc SCV
ID    NUMBER PK (ID + SCRID)
SCRID NUMBER FK(SC)

ID    SCRID
-----------
1     1
2     1
3     1
4     2
5     2
6     3
7     4
8     4

desc PROJECTS
ID   NUMBER PK
NAME VARCHAR2(100)

ID   NAME
-------------
1    PROJECT1
2    PROJECT2
3    PROJECT3
4    PROJECT4

desc PJS

ID     NUMBER
PROID  NUMBER FK (PROJECTS)
SCRID  NUMBER FK (SCV(SCRID + SCVID)
SCVID  NUMBER

ID   PROID SCRID  SCVID
-----------------------
1      1     1      1
2      1     1      2
3      1     2      5
4      1     3      6
5      1     4      7
6      2     1      3
7      2     2      4
8      2     2      5
9      2     4      7

There are over 1 million records in PJS.

desc TBP
SCRID   NUMBER
SCVID   NUMBER

SCRID   SCVID
1       1
1       2
1       3
2       4
2       5
3       6
4       7
4       8

The requirement is to select projects that have matching SCRID, SCVID
from TBP such that
all distinct SCRID should match and within that and any SCVID match
will do. (A "AND" between each SCRID and an "OR" for each SCVID in
that SCRID like 'SCRID = 1 AND (SCVID = 1 OR SCVID = 2 OR SCVID = 3)
AND SCRID = 2 AND (SCVID =....)

So, for the sample data it should return us PROID = 1

I have few queries written for this:

SELECT PROID FROM PJS,TBP WHERE TBP.SCVID = PJS.SCVID AND TBP.SCRID =
1
INTERSECT
SELECT PROID FROM PJS,TBP WHERE TBP.SCVID = PJS.SCVID AND TBP.SCRID =
2
INTERSECT
SELECT PROID FROM PJS,TBP WHERE TBP.SCVID = PJS.SCVID AND TBP.SCRID =
3
INTERSECT
SELECT PROID FROM PJS,TBP WHERE TBP.SCVID = PJS.SCVID AND TBP.SCRID =
4

This query performs well but the cost is very high, hardcoding,
sorting.

The 2nd option is to:

SELECT pjs.PROID                      proid
FROM   TBP                            tbp,
       PJS                            pjs
WHERE  pjs.SCVID         = tbp.SCVID
AND    pjs.SCRID         = tbp.SCRID
GROUP BY pjs.PROID
HAVING COUNT(DISTINCT pjs.SCRID) = (SELECT COUNT(DISTINCT SCRID ) FROM
TBP)

This has a low cost but runs slowly.

One more way I tried was with the IN operator like

SELECT DISTINCT PROID FROM PJS A,TBP T WHERE T.SCRID = 1 AND T.SCVID =
A.SCVID
AND PROID IN (SELECT PROID FROM PJS A,TBP T WHERE T.SCRID = 2 AND
T.SCVID = A.SCVID
    AND PROID IN (...SO ON with each DISTINCT SCRID.

Again this involves too much of sorting.

Thanks,

Ashish