Tham khảo tài liệu 'oracle sql internals handbook phần 4', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả | Trees in SQL CHAPTER 5 Trees in SQL Nested Sets and Materialized Path Relational databases are universally conceived of as an advance over their predecessors network and hierarchical models. Superior in every querying respect they turned out to be surprisingly incomplete when modeling transitive dependencies. Almost every couple of months a question about how to model a tree in the database pops up at the newsgroup. In this article I ll investigate two out of four well known approaches to accomplishing this and show a connection between them. We ll discover a new method that could be considered as a mix-in between materialized path and nested sets. Adjacency List Tree structure is a special case of Directed Acyclic Graph DAG . One way to represent DAG structure is create table emp ename varchar2 100 mgrname varchar2 100 Each record of the emp table identified by ename is referring to its parent mgrname. For example if JONES reports to KING then the emp table contains ename JONES mgrname KING record. Suppose the emp table also includes ename SCOTT mgrname JONES . Then if the emp table doesn t contain the ename SCOTT mgrname KING record and the same is true for every pair 44 Oracle SQL Internals Handbook of adjoined records then it is called adjacency list. If the opposite is true then the emp table is a transitively closed relation. A typical hierarchical query would ask if SCOTT indirectly reports to KING. Since we don t know the number of levels between the two we can t tell how many times to selfjoin emp so that the task can t be solved in traditional SQL. If transitive closure tcemp of the emp table is known then the query is trivial select TRUE from tcemp where ename SCOTT and mgrname KING The ease of querying comes at the expense of transitive closure maintenance. Alternatively hierarchical queries can be answered with SQL extensions either SQL3 DB2 recursive query with tcemp as select ename mgrname from tcemp union select .
