How to retrieve records from the same table in a better way

From: Ashish <ashishdhoke_at_hotmail.com>
Date: 11 Nov 2002 01:45:15 -0800
Message-ID: <9d32614.0211110145.2ccfcb91@posting.google.com>

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 Received on Mon Nov 11 2002 - 03:45:15 CST