Hierarchical functions - Oracle Database Tutorial

- [Instructor] Sometimes you need to store and query hierarchical data. Oracle Database 19c is not a hierarchical database but instead a relational database, but you can still store and query hierarchical data in an Oracle Database. And I'll show you how that's done with some examples. What exactly is Connect By and why would you use it? Using the Connect By Clause helps you identify and visualize hierarchical relationships represented in a database table. In a hypothetical company represented in the HR schema, the diagram shows the reporting structure of the employees in the company. King is at the top and has no manager. Both De Haan and Kochhar report to King others report to them and so forth. In the employee's database table, those same relationships are stored in the table but it's a lot easier to infer the relationships in the diagram. Here's the syntax for select with all the clauses and the Hierarchical Clause goes right in the middle after the Where Clause, but before Group By. It has two conditions. And the first one usually references the column that defines the hierarchy. Within the Hierarchical Clause are several sub-clauses and a few pseudo-columns and functions you can use in the condition clauses. Connect By indicates that this query has a hierarchical relationship between columns and how the columns are related. Prior is a keyword before condition one that specifies which column is the parent and which is the child in the hierarchy. Nocycle means that the query should run even if an infinite loop is detected. For example, if a manager's employee is also the manager's manager whether one level away or many to still return results from the query and stop returning rows when a loop is detected. Without Nocycle the query will not run to completion. Start With specifies the condition to specify where the hierarchical query starts. For example, you might want to Start With one of the middle managers and display everyone that reports to them. Finally, Order Siblings By is like an Order By but when after Connect By defines how to order rows at the same level who have the same parent. There are many pseudo-columns and functions you'll often use in one of the conditions of Connect By. The Level pseudo-column returns an integer defining what level in the hierarchy the given row is at. If you use Nocycle to detect loops, Connect By Iscycle helps you figure out if this row is causing the loop by returning one, otherwise it returns zero. If a row is at the end of a branch in the hierarchy in other words, a leaf, it returns a one, otherwise a zero. Finally, if you want to make the output really compact and return a string returning a hierarchical branch you can use SYS Connect By Path. Here's a basic example of using a hierarchical query against the employee's table to make it more clear what the hierarchy is in the company. The indentation is facilitated by the level of pseudo-column. How far down the reporting structure you are is used to indent the line in the results. If you are not a manager, Connect By Isleaf is one and the case clause returns no. The output shows a tree view indenting more depending on how many managers there are before you get to the CEO. The Prior key word has an expression that defines how any particular row connects to its predecessor. The manager ID column is the employee ID column of the parent record. Here's a short demo. I can start with a query, put it in a report with a parameter and show the employees that report to the specified employee and call up this report in SQL developer. Here's the full query. I'll go ahead and run it. And I get the full hierarchy. But what if instead I double click on the report I defined, connect to the database, start with a value of one zero eight which is employee 108, click on apply. And now I show so all the employees that report directly or indirectly to Greenberg and Greenberg only. From the previous example, you have to scan many rows to find the full reporting hierarchy. The second example uses the SYS Connect By Path function to generate the full reporting structure in each line of the results. The parameters are the same as for the previous example. The management structure starts with King and each row is connected from the manager ID column to the employee ID column in the related row. Here is how those two different hierarchical functions compare using Connect By comparing complexity, control, and compactness. You can use an alternative to Connect By in the With Clause, a truly recursive subquery in the With Clause. It does not use the Connect By syntax. To write a query that traverses a hierarchy, you can use a query with the Connect By Clause to link a column in the current row to a different column in the parent row that defines the relationship between rows and comes between Where and Group By. You'll use the Level pseudo-column quite often to indicate at what level in the hierarchy the row is. Using the With Clause and subquery factoring, you can generate hierarchical output with recursive calls to the subquery containing a Union All. And avoids the more compact, but cryptic Connect By syntax. Use the Connect By whenever our row in a table references the same value in another column of the same table. That is what defines a hierarchical relationship.