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

For a specific query it would be helpful if you could provide a link to somewhere to download a couple of table creates scripts and a little test data - that's the way I'd verify any solution I'd propose. Glad you got an answer. — Sten Vesterli
– Sten Vesterli, Commented May 26, 2009 at 20:00

Cade Roux · Accepted Answer · 2009-05-26 17:49:24Z

6

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.

answered May 26, 2009 at 17:49

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kevin Babcock Over a year ago

Yep, you're absolutely right. I was over-complicating the query. :) Thanks for the tip!

MichaelN · Accepted Answer · 2009-05-26 20:30:48Z

0

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.

answered May 26, 2009 at 20:30

MichaelN

1,74411 silver badges6 bronze badges

Collectives™ on Stack Overflow

Help optimize an Oracle query?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related