Genealogy Query in Oracle

I'm trying to fetch a genealogy tree of animals from my Oracle database.

Here's the table:

Animal
------------------------
Animal_ID
Parent_Male_ID
Parent_Female_ID
....
....
------------------------

If I specify an animal, I can get all of its descendants (on the male side) using something like this:

SELECT *
FROM animal
START WITH animal_id = 123
CONNECT BY PRIOR animal_id = parent_male_id

I'm trying to find a way to extend this in such a way that if I specify an animal, it will fetch both parents and then will fetch all of their descendants.

Any thoughts? (this is Oracle 9.2)

From stackoverflow

• SELECT  *
  FROM    animal
  START WITH
          animal_id IN
          (
          SELECT  parent_male_id
          FROM    animal
          WHERE   animal_id = 123
          UNION ALL 
          SELECT  parent_female_id
          FROM    animal
          WHERE   animal_id = 123
          )
  CONNECT BY
          PRIOR animal_id IN (parent_male_id, parent_female_id)
  

  This query, however, will be quite slow.

  Better to use this one:

  SELECT  DISTINCT(animal_id) AS animal_id
  FROM    (
          SELECT  0 AS gender, animal_id, father AS parent
          FROM    animal
          UNION ALL
          SELECT  1, animal_id, mother
          FROM    animal
          )
  START WITH
          animal_id IN
          (
          SELECT  father
          FROM    animal
          WHERE   animal_id = 9500
          UNION ALL 
          SELECT  mother
          FROM    animal
          WHERE   animal_id = 9500
          )
  CONNECT BY
          parent = PRIOR animal_id
  ORDER BY
          animal_id
  

  , which will use HASH JOIN and is much faster.

  See this entry in my blog for performance details:

  • Genealogy query on both parents

• here is some further reading:

  http://dev.mysql.com/tech-resources/articles/hierarchical-data.html