Oracle 10g按优先级连接-性能问题 [英] Oracle 10g Connect By Prior - Performance Issues

问题描述

我有以下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(父亲=父亲，母亲=母亲).

This in in a table with about 1.6 Million animals. Each record has Animal_Id, Sire_Animal_Id, and Dam_Animal_Id (Sire = Father, Dam = Mother).

我使用此sql显示完整的动物谱系.结果将显示动物，2个父母，4个祖父母等.

I use this sql to display the full animal pedigree. Results Will show Animal, 2 Parent, 4 GrandParents, etc.

我的问题是，对于一只动物来说，这句话需要15秒.必须有一种优化此方法的方法.有什么想法吗?

My issue is that this statement takes 15 seconds, for one animal. There has got to be a way to optimize this. Any thoughts?

推荐答案

我没有很长时间对此进行测试，所以答案中有一些DYOR，但是会使用内联视图有帮助吗?

I haven't had a long time to test this so there is a bit of DYOR in the answer but would using an inline view help?

恐怕您还没有发布解释计划，我对此无能为力，在下面的解决方案中，您可能会发现WITH子句中的并集会导致性能问题，但可能会对您有所帮助解决方法.

As you haven't posted an explain plan I can't help too much i'm afraid and in the solution below, you may find that the union in the WITH clause causes you performance issues but it might help you on your way to a solution.

WITH ani
  AS (SELECT animal_id, 
             line_id, 
             sire_animal_id, 
             dam_animal_id, 
             sire_animal_id AS generic_id
        FROM animals
      UNION
      SELECT animal_id, 
             line_id, 
             sire_animal_id, 
             dam_animal_id, 
             dam_animal_id AS generic_id
        FROM animals)
SELECT CONNECT_BY_ROOT animal_id "ORIGINAL_ANIMAL",
       animal_id,
       line_id,
       sire_animal_id,
       dam_animal_id,
       LEVEL - 1 "LEVEL"
  FROM ani
 START WITH animal_id = '2360000002558'
 CONNECT BY (PRIOR generic_id = animal_id AND LEVEL < 5 )

这篇关于Oracle 10g按优先级连接-性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！