Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Performance problem of recursive join

Re: Performance problem of recursive join

From: Sohn Matthias <sohn_at_jobpilot.de>
Date: 2000/05/10
Message-ID: <391907B6.335EA398@jobpilot.de>#1/1

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US