Can you help optimize your Oracle query?
I am trying to get better performance from this Oracle query (which is very slow). I am an Oracle newbie, so maybe someone can point out a better way to approach this query.
I have a table with information about different families. I want to extract different relationships based on 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
);
a source to share
Do you understand that 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 really is that simple:
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 have thought that the best thing to do is to have an index on relationship_type_id
, family_number
, person_id
in your table of people.
a source to share
To help you set up, you can post the following information:
1) For respective tables, their table definitions and index definitions in tables are defined. 2) Basic run of the approximate number of rows in each table 3) Query explain plan. to get the explain plan: a) run in sqlplus explain the plan SET STATEMENT_ID = for <insert your queries>; b) run in sqlplus select * from the table (dbms_xplan.display ('PLAN_TABLE', ''));
With an explain plan and table / index information, we can help you customize your query better. Only with a request, we really don't have much. If you can provide a tkprof to fulfill the request, that will be of further help as well.
a source to share