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: <michael_bialik_at_my-deja.com>
Date: 2000/05/10
Message-ID: <8fbdm9$ksq$1@nnrp1.deja.com>#1/1

Hi.

 Try

 SELECT id, count(*) FROM person_skill
 WHERE
  skill IN ('VC++','ORACLE','SYBASE','BC++')  GROUP BY id
 HAVING count(*) = 4;

 If you are able to create bitmap index on skill - it may help.

 HTH. Michael.

In article <xL3S4.5$dp4.15410_at_srvr1.engin.umich.edu>,   "Peizhong Wu" <wupz_at_umich.edu> 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed May 10 2000 - 00:00:00 CDT

Original text of this message

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