Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Performance problem of recursive join
Hi,
try
SELECT t1.id FROM person_skill t1 where t1.skill = 'VC++'
INTERSECT
SELECT t1.id FROM person_skill t1 where t1.skill = 'ORACLE'
INTERSECT
SELECT t1.id FROM person_skill t1 where t1.skill = 'SYBASE'
....
bye, matthias
Peizhong Wu wrote:
> Hi, I am having problem with recursive join.
>
> The table:
> person_skill
> id number;
> skill VARCHAR2(20);
>
> Data:
> id skill
> 1 VC++
> 1 MSWord
> 1 ORACLE
> 1 SYBASE
> 1 JAVA
> 2 ASP
> 2 WEB
> 2 DHTML
> ....
>
> There are about 10,000 people and the table has about 80,000 rows.
>
> I am going to find any person with centain skill set:
> VC++, ORACLE, BC++, and SYBASE
>
> The query I build is:
>
> SELECT t1.id
> FROM person_skill t1,
> person_skill t2,
> person_skill t3,
> person_skill t4
> WHERE t1.id = t2.id
> AND t2.id = t3.id
> AND t3.id = t4.id
> AND t1.skill = 'VC++'
> AND t2.skill = 'ORACLE'
> AND t3.skill = 'SYBASE'
> AND t4.skill = 'BC++'
>
> This query works fine but takes too long. Is anything I can do
> to improve the performance? Such as other approach, indexing?
>
> Thanks
>
> Peter
Received on Wed May 10 2000 - 00:00:00 CDT