Oracle Database 10g 中新的 CONNECT BY 特性

Jonathan Gennick自 2001 年以來一直是 OTN 會員

Oracle Database 10g 增強了對查詢層次或樹狀結構資料的支援

最近，我在 OTN 寫了一篇文章，名為《查詢階層：最出色的支援》，其中論述了 Oracle 對層次查詢的支援。在寫那篇文章時，我瞭解到 Oracle Database 10g 中一些激動人心的新特性，這些特性加強了 Oracle 已有的對查詢樹狀結構資料的穩健支援。 不幸的是，我不能提及那些新特性，因為它們仍在測試中。 其後，我一直迫不及待地等待著該產品發布，現在我終於可以暢所欲言了。

這篇文章詳細說明了 Oracle Database 10g 中三個新的 CONNECT BY 特性。這些樣本使用的表和資料與我之前的文章中所用的表和資料相同。您可能希望讀那篇文章來重溫一下 CONNECT BY 查詢的工作方式。

問題的起源

如果您閱讀了我先前的文章，則您已經瞭解了如何在層次查詢中使用 PRIOR 操作符來從一個父行返回列資料。能夠返回到根行有時是很方便的。Oracle Database 10g 中新增的 CONNECT_BY_ROOT 操作符使您能夠從一個階層中的任何位置引用根行的值。

CONNECT_BY_ROOT 的一個用途是用來識別所有包含了一個給定部分的產品。設想您為一個製造公司工作。您發現 1019 號組件有缺陷，消費品安全委員會命令您收回已售出的所有包含了該組件的產品。您的第一個任務是確定您擔心的是哪些產品。您可以通過發出以下查詢來開始，作為第一步工作：

 

SELECT assembly_id, assembly_nameFROM bill_of_materialsWHERE part_number = 1019START WITH parent_assembly IS NULLCONNECT BY parent_assembly = PRIOR assembly_id;ASSEMBLY_ID ASSEMBLY_NAME----------- -----------------------141 Lock144 Lock

 

這些結果看起來似乎是合理的，但它們存在一個問題。1019 號組件是一個鎖，但事實上它被用在兩個鎖組件中。然而，那些鎖組件隨後被用在左邊和右邊的門組件內，門組件隨後又被用在一個車身組件中，車身組件最後被用來製造一輛汽車，而我們售出的和要關心的是汽車。您不想要 1019 號組件的直接父組件；您想要 1019 號組件最終的父親。幸運的是，您的資料庫剛剛升級到 Oracle Database 10g，因此您可以利用新的 CONNECT_BY_ROOT 操作符：

 

SELECT DISTINCT CONNECT_BY_ROOT assembly_id,CONNECT_BY_ROOT assembly_nameFROM bill_of_materialsWHERE part_number = 1019START WITH parent_assembly IS NULLCONNECT BY parent_assembly = PRIOR assembly_id;CONNECT_BY_ROOTASSEMBLY_ID CONNECT_BY_ROOTASSEMBLY-------------------------- -----------------------100 Automobile

 

該查詢大部分和以前相同。只有兩點區別：使用了DISTINCT；在 SELECT 列表中的每一個列名前面出現了 CONNECT_BY_ROOT。CONNECT_BY_ROOT 操作符輸出我們關心的那個組件的最終父組件 ID 和名稱。DISTINCT 關鍵字防止一個產品在多個組件中包含了相同組件時被多次列出。因此，結果是您的汽車在它的右邊和左邊的門裡都包含了鎖。

尋根究底

層次資料常常是深層嵌套的。考慮這樣一個問題：為機械師提供一輛汽車中的組件和組件的一個嵌套列表。汽車包含大量的組件。機械師很少希望立刻瞭解所有組件和組件的詳情。那樣的一個列表不僅將使人不知所措，而且當使用者只需要該資料的一部分時，從資料庫檢索那樣一個組件和組件的完整列表以及跨網路傳遞那樣的資訊也是非常低效的。相反，您可能選擇一開始僅提供頂層的組件，然後讓使用者從那裡開始深入到階層內部。例如，您可能一開始提供給我們的使用者下列查詢的結果：

 

SELECT ASSEMBLY_ID,RPAD(' ', 2*(LEVEL-1)) || assembly_name assembly_name,quantityFROM bill_of_materialsWHERE LEVEL <= 2START WITH assembly_id = 100CONNECT BY parent_assembly = PRIOR assembly_id;ASSEMBLY_ID   ASSEMBLY_NAME     QUANTITY-----------   -------------     ----------100     Automobile110     Combustion Engine 1120     Body              1130     Interior          1

 

通過查看第一級的組件，我們的使用者現在能夠確定他們是否要進一步向下查看。當有更多的資料要查看時，可以通過將組件名實現為 Web 連結來實現向下查看，或者您可以像 Windows 應用中常見的那樣實現一個樹狀控制。且慢！您怎麼知道什麼時候一個組件會有更多的資料？什麼時候向下查看是可能的？當使用者試圖從該階層的底部向下查看時，您可以讓他們試著向下查看到任何組件中，然後給他們一條“沒有更多資料”的訊息，但這是一種生硬的解決辦法，無疑將使他們感到灰心。最好能夠提前瞭解向下查看是否可能。Oracle Database 10g 使我們能夠通過 CONNECT_BY_ISLEAF 虛擬列來達到這一目的。您可以使用以下查詢來開始：

 

SELECT ASSEMBLY_ID,RPAD(' ', 2*(LEVEL-1)) || assembly_name assembly_name,quantity, CONNECT_BY_ISLEAFFROM bill_of_materialsWHERE LEVEL <= 2START WITH assembly_id = 100CONNECT BY parent_assembly = PRIOR assembly_id;ASSEMBLY_ID ASSEMBLY_NAME             QUANTITY CONNECT_BY_ISLEAF----------- ----------------------- ---------- -----------------100 Automobile                                         0110   Combustion Engine              1                 0120   Body                           1                 0130   Interior                       1                 0

 

CONNECT_BY_ISLEAF 返回的零指示在該列表中顯示的組件中沒有一個是分葉節點。換句話說，向下查看到它們的任何一個中都是無效的。假定使用者向下查看到 Combustion Engine 中。那麼您可以發出以下查詢來擷取組成發動機的子組件：

 

SELECT ASSEMBLY_ID,RPAD(' ', 2*(LEVEL-1)) || assembly_name assembly_name,quantity, CONNECT_BY_ISLEAFFROM bill_of_materialsWHERE LEVEL = 2START WITH assembly_id = 110CONNECT BY parent_assembly = PRIOR assembly_id;ASSEMBLY_ID ASSEMBLY_NAME             QUANTITY CONNECT_BY_ISLEAF----------- ----------------------- ---------- -----------------111   Piston                         6                 1112   Air Filter                     1                 1113   Spark Plug                     6                 1114   Block                          1                 1115   Starter System                 1                 0

 

該查詢和以前幾乎是相同的。Combustion Engine 的 START WITH 組件 ID 值變為 110，該查詢特別請求 LEVEL = 2。在這個節點上，您不需要 LEVEL = 1，因為那將再次返回 Combustion Engine 的行，而您已經得到那一行了。

這次，您看到 CONNECT_BY_ISLEAF 有兩個值。Piston、Air Filter、Spark Plug 和 Block 的值為 1 指示那些組件是分葉節點，且其下沒有發現更多的組件。瞭解到這一點，您就可以調整我們的顯示內容，這樣使用者就知道不用徒勞地在那些組件上向下切入。另一方面，Starter System 的 CONNECT_BY_ISLEAF 值為 0，這指示仍然存在要檢索的子組件。

跳出迴圈

只要您利用層次資料進行工作，您就可能遇到一個迴圈的層次。比如說，有人可能將一輛汽車的父組件設為一個火花塞：

 

UPDATE bill_of_materialsSET parent_assembly = 113WHERE assembly_id=100;

嘗試在該組件樹中查詢 "Automobile" 現在就將失敗：

 

SELECT RPAD(' ', 2*(LEVEL-1)) || assembly_name assembly_name,quantityFROM bill_of_materialsSTART WITH assembly_id = 100CONNECT BY parent_assembly = PRIOR assembly_id;ERROR:ORA-01436:CONNECT BY loop in user data

當您獲得像這樣的一條錯誤訊息時，您可以使用 CONNECT_BY_ISCYCLE 虛擬列來確定引起問題的行的位置。要做到這一點，您還必須添加 NOCYCLE 關鍵字到 CONNECT BY 子句中，防止資料庫進入階層中的任何迴圈：

接下來的步驟 下載 Oracle Database： /global/cn/software/products/oracle9i/index.html 訪問 Oracle Database 10g 產品頁面： /global/cn/products/database/index.html

SELECT RPAD(' ', 2*(LEVEL-1)) || assembly_name assembly_name,quantity, CONNECT_BY_ISCYCLEFROM bill_of_materialsSTART WITH assembly_id = 100CONNECT BY NOCYCLE parent_assembly = PRIOR assembly_id;ASSEMBLY_NAME                    QUANTITY CONNECT_BY_ISCYCLE------------------------------ ---------- ------------------Automobile                                                 0Combustion Engine                     1                  0Piston                              6                  0Air Filter                          1                  0Spark Plug                          6                  1Block                               1                  0

注意，CONNECT_BY_ISCYCLE 為 "Spark Plug" 行返回一個 1。當您使用 NOCYCLE 時，資料庫通過層次跟蹤它的路徑，不斷檢查確保它不會進入迴圈。在完成從 "Automobile" 到 "Combustion Engine" 再到 "Spark Plug" 的遍曆後，資料庫發現 "Spark Plug" 的孩子是 "Automobile"，該行所處的路徑通向 "Spark Plug"。這樣的一行代表一個迴圈。NOCYCLE 防止資料庫進入迴圈，CONNECT_BY_ISCYCLE 返回一個 1 來指示出現迴圈的行。現在您已經知道了出現問題的位置，您可以解決它。

完成工作

Oracle Database 10g 中新的 CONNECT BY 特性 — CONNECT_BY_ROOT、CONNECT_BY_ISLEAF 和 CONNECT_BY_ISCYCLE — 是經過精心設計的對 Oracle 層次查詢支援的改善，受到大家的歡迎。這些特性解決了查詢層次資料所固有的常見和長期存在的問題，而這些問題用其它方式難以解決。利用這些特性，您可以更少地關注實施細節，而更多地專註於您需要實現的目標的全域，從而簡單且井然有序地完成您需要完成的工作。