New connect by feature in Oracle Database 10g

Jonathan Gennick has been an OTN member since 2001.

Oracle Database 10GEnhanced support for hierarchical or tree-like Data Query

Recently, I wrote an article in OTN titled query hierarchy: the best support, which discusses Oracle's support for hierarchical queries.When I wrote that article, I learned about Oracle Database 10GSome exciting new features that enhance Oracle's robust support for querying tree-structured data. Unfortunately, I cannot mention those new features because they are still being tested. After that, I have been waiting for the release of this product, and now I can finally speak freely.

This article details Oracle Database 10GThree newCONNECTFeatures. The tables and data used in these examples are the same as the tables and data used in my previous articles. You may want to read the article to review it.CONNECTThe query method.

Origin

If you have read my previous articles, you have learned how to usePRIORReturns column data from a parent row. It is sometimes convenient to return to the root row. Oracle Database 10GAddedCONNECT_BY_ROOTThe operator enables you to reference the value of the root row from any position in a hierarchy.

CONNECT_BY_ROOTIs used to identify all products that contain a given part. Imagine you are working for a manufacturing company. You have found that part 1019 is defective, and the Consumer Goods Safety Board ordered you to reclaim all sold products that contain the part. Your first task is to determine which products you are worried about. You can start by issuing the following query as the first step:

 

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

 

These results seem reasonable, but they have a problem. Part 1019 is a lock, but in fact it is used in two lock components. However, the lock components are subsequently used in the left and right door components, and the door components are subsequently used in a body component, and the body components are finally used to create a car, what we sell and care about is cars. You do not want the direct parent component of part 1019; you want the final parent component of part 1019. Fortunately, your Database has just been upgraded to Oracle Database 10GTherefore, you can use the newCONNECT_BY_ROOTOPERATOR:

 

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

 

Most of the queries are the same as before. There are only two differences:DISTINCTInSELECTBefore each column name in the listCONNECT_BY_ROOT.CONNECT_BY_ROOTThe operator outputs the final parent component ID and name of the component we care about.DISTINCTKeyword to prevent a product from being listed multiple times when multiple components contain the same part. Therefore, the result is that your car contains a lock in both the right and left doors.

Root Cause

Hierarchical Data is often nested in depth. Consider the problem of providing a mechanic with a nested list of components and components in a car. A car contains a large number of parts. Mechanics seldom want to immediately learn details about all components and components. Such a list will not only make people feel overwhelmed, but also when users only need a part of the data, retrieving a complete list of such components and components from a database and transferring such information across networks is also very inefficient. Instead, you may choose to provide only the top-level components at the beginning, and then let the user go deep into the hierarchy from there. For example, you may first provide the following query results to our users:

 

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

 

By viewing the first-level components, our users can now determine whether they want to further look down. When you want to view more data, you can use the component name as a Web link to view the data. Alternatively, you can implement a tree-like control as common in Windows applications. Slow! How do you know when a component will have more data? When is it possible to look down? When a user tries to view down from the bottom of the hierarchy, you can ask them to try to view down any component and then give them a message with "no more data, but this is a tough solution that will undoubtedly make them feel discouraged. It is best to know in advance to see if it is possible. Oracle Database 10GAllows usCONNECT_BY_ISLEAFVirtual columns to achieve this purpose. You can start with the following query:

 

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_ISLEAFThe returned zero indicates that none of the components displayed in the list are leaf nodes. In other words, all of them are invalid. Assume that the user can view the Combustion Engine. Then you can issue the following query to obtain the sub-components that constitute the 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

 

The query is almost the same as before. Combustion EngineSTARTThe component ID is changed to 110, which is a special request for this query.LEVEL =2. On this node, you do not needLEVEL =1, because it will return the line of the Combustion Engine again, and you have obtained that line.

This time, you can seeCONNECT_BY_ISLEAFThere are two values. The values of Piston, Air Filter, Spark Plug, and Block are 1, indicating that those components are leaf nodes and no more components are found under them. With this in mind, you can adjust our display content so that you don't have to drill down on those components in vain. On the other handCONNECT_BY_ISLEAFThe value is 0, indicating that the child component to be retrieved still exists.

Skip Loop

As long as you use hierarchical data for work, you may encounter a cyclical hierarchy. For example, someone may set the parent component of a car as a spark plug:

 

UPDATE bill_of_materialsSET parent_assembly = 113WHERE assembly_id=100;

An attempt to query "Automobile" in the component tree will fail now:

 

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

You can useCONNECT_BY_ISCYCLEVirtual column to determine the location of the row that causes the problem. To do this, you must addNOCYCLEKeywordCONNECTClause to prevent the database from entering any loop in the hierarchy:

Next step Download Oracle Database: /Global/cn/software/products/oracle9i/index.html Accessing Oracle Database 10GProduct Page: /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

Note,CONNECT_BY_ISCYCLEReturns 1 for the Spark Plug row. When you useNOCYCLEThe database traces its path through layers and constantly checks to ensure that it does not enter a loop. After traversing from "Automobile" to "Combustion Engine" to "Spark Plug", the database found that "Spark Plug" is "Automobile ", the path of this row leads to "Spark Plug ". Such a row represents a loop.NOCYCLEPrevent the database from entering the loop,CONNECT_BY_ISCYCLEReturns 1 to indicate the row with a loop. Now you know where the problem occurs. You can solve it.

Complete the work

Oracle Database 10GNewCONNECTFeature-CONNECT_BY_ROOT,CONNECT_BY_ISLEAFAndCONNECT_BY_ISCYCLE-It was well-designed to improve the support for Oracle hierarchical queries and was welcomed by everyone. These features solve common and long-term problems inherent in querying hierarchical data, which are difficult to solve in other ways. With these features, you can focus less on implementation details, and focus more on the global target you need to achieve, so that you can complete the work you need in a simple and orderly manner.