Re: Performance of Recursive Join

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) Received on Wed May 17 2000 - 00:00:00 CDT