Re: Performance of Recursive Join

Haven't seen the original msg but I guess something like this should be quite fast :

select t1.id from person_skill t1
where skill='Oracle'
and exists (select 'x' from person_skill t2 where (t1.id=t2.id) and (t2.skill='Sybase'))
and exists (select 'x' from person_skill t3 where (t1.id=t3.id) and (t3.skill='MSSQL7'))
and exists (select 'x' from person_skill t4 where (t1.id=t4.id) and (t4.skill='VC++'));

Of course, you should have the ID and SKILL fields indexed (or it must take ages!) :

create index ix_person_skill_id on person_skill(id); create index ix_person_skill_skill on person_skill(skill);

--
Jean-Christophe Boggio
cat_at_elma.fr
Independant consultant and developper
Linux, Delphi, Oracle, Perl


Dirk Sudheimer <sudheimer_at_gmx.de> a écrit dans le message :
3922ac1e.0_at_juno.wiesbaden.netsurf.de...


> On Tue, 9 May 2000 09:19:03 -0400, "Peter Wu" <pwu7_at_ford.com> wrote:

>

> <snip>

>

> >The query I built 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 = 'Oracle'

> >    AND    t2.skill = 'Sybase'

> >    AND    t3.skill = 'MSSQL7'

> >    AND    t4.skill = 'VC++'

> >

> >The recursive join of table person_skill takes long time. Is anything I

 can


> >do to reduce the time? Such as new query, indexing?

>

> <snip>

>

> Hy Peter,

> something that worked for me is the following approach:

>

> SELECT id FROM

>    (select id,

>     SUM(DECODE(skill,'WORD',1, 'UNIX', 2, 'NETWORK', 4, 0)) bitmap

>    from person_skill

>   WHERE skill IN ('WORD', 'UNIX', 'NETWORK')

>   GROUP BY id)

> WHERE bitmap = 7

>

> the idea is to "bitmap" the entries you are interested in and

> mask out the relevant datasets.

> Another advantage: when you choose the mapping to represent

> your preference you are able to distinguish between optional and

> mandatory skills (e.g. "bitmap > 3 order by bitmap" makes WORD and

> UNIX mandatory and lists persons with additional skills first).

> You better have skill be uppercase to make full use of an index

> on this field.

>

> Dirk

> and leaves NETWORK as optional skill)