title: Help optimize an Oracle query?
body: <p>I'm trying to get better performance out of this Oracle query (which is terribly slow). I'm an Oracle beginner so maybe someone can point out a better way to approach this query.</p>
  
  <p>I have a table with information on different families. I want to extract the different relationships based on a relationship type. The solution I came up with uses a hash join to query the database...</p>
  
  <pre><code>select *
  from (
    with target_person as (
      select 
        p.person_id,
        p.family_number,
        p.relationship_type_id
      from 
        people p
      where
        p.relationship_type_id = 1 -- parent
    )
    select
      target_person.person_id,
      related_person.related_person_id,
      related_person.relationship_type_id
    from
      target_person,
      people related_person
    where
      target_person.person_id != related_person.person_id
      and target_person.family_number = related_person.family_number
      and related_person.relationship_type_id = 1
  );
  </code></pre>