sql - Oracle 10g Connect By Prior - 性能问题

Question

我有以下 SQL 语句:

SELECT 
    CONNECT_BY_ROOT ANIMAL_ID "ORIGINAL_ANIMAL" ,
    ANIMAL_ID, LINE_ID, SIRE_ANIMAL_ID, DAM_ANIMAL_ID,
    LEVEL -1 "LEVEL" FROM ANIMALS 
START WITH ANIMAL_ID IN( '2360000002558' )
CONNECT BY
  ((PRIOR SIRE_ANIMAL_ID = ANIMAL_ID and LEVEL < 5) OR (PRIOR DAM_ANIMAL_ID = ANIMAL_ID AND LEVEL < 5))

这是一张包含大约 160 万只动物的表格。每条记录都有 Animal_Id、Sire_Animal_Id 和 Dam_Animal_Id(Sire = 父亲，Dam = 母亲)。

我使用这个 sql 来显示完整的动物谱系。结果将显示动物、2 个 parent 、4 个祖 parent 等。

我的问题是，对于一只动物，此语句需要 15 秒。必须有一种方法来优化它。有什么想法吗？

Answer 1

我尝试重新创建您的情况，但我无法让 Oracle 明智地使用索引。我相信有一些聪明的方法可以做到这一点。但是，如果这里没有其他人可以弄清楚，下面是愚蠢而丑陋的方式。

由于您只能获得一定数量的级别，因此您可以手动创建连接。获取第一级，将其合并到第二级(从第一个查询的副本中获取结果)，将其合并到第三级(从第二个查询的副本中获取结果)，等等。我只做了三个级别在这里，但您可以复制和粘贴以制作第四个。由于原始 id 重复了很多次，所以更难使用，但它 super 快(在我的机器上有 160 万条记录，0.005 秒。)

--Original animal
select '101' original_animal, animal_id, line_id, sire_animal_id, dam_animal_id, 0 "level" from animals where animal_id = '101'
union all
--Parents
select '101' original_animal, animal_id, line_id, sire_animal_id, dam_animal_id, 1 "level" from animals
where animal_id = (select sire_animal_id from animals where animal_id = '101')
union all
select '101' original_animal, animal_id, line_id, sire_animal_id, dam_animal_id, 1 "level" from animals
where animal_id = (select dam_animal_id from animals where animal_id = '101')
union all
--Grand parents
select '101' original_animal, animal_id, line_id, sire_animal_id, dam_animal_id, 2 "level" from animals
where animal_id =
(
  select sire_animal_id from animals
  where animal_id = (select sire_animal_id from animals where animal_id = '101')
)
union all
select '101' original_animal, animal_id, line_id, sire_animal_id, dam_animal_id, 2 "level" from animals
where animal_id =
(
  select dam_animal_id from animals
  where animal_id = (select sire_animal_id from animals where animal_id = '101')
)
union all
select '101' original_animal, animal_id, line_id, sire_animal_id, dam_animal_id, 2 "level" from animals
where animal_id =
(
  select sire_animal_id from animals
  where animal_id = (select dam_animal_id from animals where animal_id = '101')
)
union all
select '101' original_animal, animal_id, line_id, sire_animal_id, dam_animal_id, 2 "level" from animals
where animal_id =
(
  select dam_animal_id from animals
  where animal_id = (select dam_animal_id from animals where animal_id = '101')
);