Help optimize an Oracle query?

Question

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.

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...

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
);

Answer 1

To help you with tuning can you post the following information:

1) For the tables involved, their table definition and index definitions on the tables. 2) A basic run down of the approximate number of rows in each of the table 3) An explain plan of the query. to get an explain plan: a) run in sqlplus explain plan SET STATEMENT_ID= for < insert your query>; b) run in sqlplus select * from table(dbms_xplan.display('PLAN_TABLE',''));

With an explain plan and table/index info we can help you tune the query better. With just the query we really don't have much to go by. If you can provide a tkprof of the query execution, that would be of additional help as well.

Answer 2

You realize this is equivalent to this?:

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,
    target_person related_person
  where
    target_person.person_id != related_person.person_id
    and target_person.family_number = related_person.family_number
);

So it's really as simple as this:

SELECT *
FROM people AS l
INNER JOIN people AS r
ON l.family_number = r.family_number
    AND l.relationship_type_id = 1
    AND r.relationship_type_id = 1
    AND l.person_id <> r.person_id

I would think the way to get this to go fastest is to have an index on relationship_type_id, family_number, person_id on your people table.