如何優化分層查詢

我一直在嘗試運行我的查詢，但它似乎總是像永遠一樣運行。想知道我怎樣才能提高性能 必須按子句添加到組中，因為在查看結果時左連接似乎正在創建重復。

分層查詢代碼

with NextValue (equip_id, link_id, node_fr_id, node_to_id ) as
(
    SELECT lm.equipment_id, l.link_id, l.node_fr_id_num, l.node_to_id_num
    FROM road_details l
    LEFT JOIN Equipment_details e ON l.link_id = e.link_id
    Where 1=1
    START WITH l.link_id = 2
    CONNECT By NOCYCLE Prior l.node_to_id_num = l.node_fr_id_num  
)
Select * from NextValue 

-- Select Particular Equipment starting with 'DET' --
Where 1=1 and equip_id like 'DET%'

-- Remove Duplicate cause by Join clause --
Group by  equip_id, link_id, node_fr_id, node_to_id;

預期輸出：

電流輸出：

for odd reason it creating duplicates and hence had to add in the Group clause to counter this issue.. but with the group clause added in i've notice the longer querying duration occuring.

Updates for commenters (not enough space to explain): Hi @HimanshuKandpal, let me just explain the query and what it is suppose to do. There are 2 tables, one is road_details and the other is an equipment_details. PK for the road detailsis Link_id, where equipment_id is the PK in the equipment_details and link_id is FK.

Brief explanation road details for context of what this hieratical query is aiming to do. Picture a map of inter connecting roads, e.g. a single road is made up of multiple links (Blocks) each block has a fr_id (head) and to_id (tail) [link_id 1 -> link_id 2 -> link_id 3] where link_id1.to_id = link_id2.fr_id as they are linked that way. The query is supposed to find out what is the next connecting link based on the criteria.

2nd criteria, with each link can have multiple equipment on it or non at all. If a user were to select a certain equipment type, as shown in my where clause are they able to know what is the next or previous link ID of a particular equipment with support from hieratical query.

uj5u.com熱心網友回復：

您SELECT都來自road_details表的列（假設lm.equipment_id是一個錯字，因為沒有lm表別名，它應該是l.equipment_id），WHERE過濾器是表中的列，road_detailsandSTART WITH條件CONNECT BY都使用表中的列road_details。

您LEFT OUTER JOIN的Equipment_details表意味著匹配行是可選的，并且您永遠不要使用它的任何列來過濾輸入或生成輸出，因此該Equipment_details表似乎與輸出無關。充其量，SQL 引擎會優化查詢并移除連接；Equipment_details在最壞的情況下，表中的每一行都有多個匹配的行，road_details這將產生重復，然后隨著路徑深入到層次結構中，這個重復的數量將成倍地放大。

例如，如果您的路徑沒有設備：

• A -> B -> C

并將其與在每條道路上使用 2 臺設備的相同路徑進行比較：

• A (with equipment A1) -> B (with equipment B1) -> C (with equipment C1)
• A (with equipment A1) -> B (with equipment B1) -> C (with equipment C2)
• A (with equipment A1) -> B (with equipment B2) -> C (with equipment C1)
• A (with equipment A1) -> B (with equipment B2) -> C (with equipment C2)
• A (with equipment A2) -> B (with equipment B1) -> C (with equipment C1)
• A (with equipment A2) -> B (with equipment B1) -> C (with equipment C2)
• A (with equipment A2) -> B (with equipment B2) -> C (with equipment C1)
• A (with equipment A2) -> B (with equipment B2) -> C (with equipment C2)

該查詢將生成 8 個終止 a 的重復項C（并且隨著層次結構的加深或多個路徑到達同一個連接點，情況會變得更糟）。

您仍然需要過濾掉重復項，因為您可以沿著道路行駛A -> B -> C，并且可以通過兩條不同的路徑到達，A -> D -> C而C不會與設備重復，但包括設備會使重復項變得更加普遍。

由于您從不使用該Equipment_details表，因此請不要將其包含在查詢中而只使用該road_details表：

SELECT DISTINCT
       l.equipment_id,
       l.link_id,
       l.node_fr_id_num,
       l.node_to_id_num
FROM   road_details l
WHERE  l.equipment_id like 'DET%'
START WITH
       l.link_id = 2
CONNECT BY NOCYCLE
       PRIOR l.node_to_id_num = l.node_fr_id_num

(Note: using either GROUP BY with all the columns or DISTINCT will be doing the same job and performance should be similar for both and can be used interchangeably; you should profile each version and check. DISTINCT may be easier for future developers to comprehend what you are trying to achieve.)

If the equipment_id column is in the Equipment_details table then you can generate the road hierarchy and then later join the equipment:

SELECT e.equipment_id,
       l.*
FROM   ( SELECT DISTINCT
                link_id,
                node_fr_id_num,
                node_to_id_num
         FROM   road_details
         START WITH
                link_id = 2
         CONNECT BY NOCYCLE
                PRIOR node_to_id_num = node_fr_id_num
       ) l
       INNER JOIN Equipment_Details e
       ON (l.link_id = e.link_id)
WHERE  e.equipment_id like 'DET%'

or:

SELECT e.equipment_id,
       l.*
FROM   ( SELECT DISTINCT
                link_id,
                node_fr_id_num,
                node_to_id_num
         FROM   road_details
         START WITH
                link_id = 2
         CONNECT BY NOCYCLE
                PRIOR node_to_id_num = node_fr_id_num
       ) l
       LEFT OUTER JOIN Equipment_Details e
       ON (   l.link_id = e.link_id
          AND e.equipment_id like 'DET%')

標籤：sql oracle

上一篇：如何洗掉同一選項值中具有不同值的行

下一篇：如何從以一對多和多對多關系劃分的兩個表中獲取列？