Performance problem of recursive join

From: Peizhong Wu <wupz_at_umich.edu>
Date: 2000/05/09
Message-ID: <zL3S4.6$dp4.15425@srvr1.engin.umich.edu>#1/1

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 Tue May 09 2000 - 00:00:00 CDT