在Oracle中实现可扩展的多级编目结构

2009-10-16 作者：zhangyuk 来源：章鱼的blog

一、可扩展多级编目的定义 　　可扩展多级编目有下述特点： 　　编目分级，上下级编目形成父子关系; 　　任意节点可以有零到多个子节点，任意子节点有且只有一个父节点; 　　节点深度在理论上可以无限扩充; 　　由上述特点可知，可扩展多级编目实际上是树。 　　二、可扩展多级编目的例子 　　Windows操作系统中的文件路径是一个典型的多级编目结构。多级编目结构的例子还有很多：人的家谱、文献索引、制造业的BOM、财务的科目编码、公司组织结构等。 　　三、可扩展多级编目在Oracle中的设计 　　概念设计： 　　物理设计： 　　多级编目表的设计思路跟“树”的设计思路一样。ID是行的唯一标识，用Oracle的序列和触发器(可参见有关文章)实现自增长。PARENTID是指向本表的外键，如果该行有父节点则引用本表中父节点行的ID，如果该行是根节点则为空。 　　四、用connect by遍历多级编目表 　　用Oracle提供的connect by命令可以正序或逆序遍历多级编目表。正序遍历的例子： 　　select ID,PARENTID,NAME,DESCRIBE,levelfromXXX_CLASS_TAB 　　connectby PARENTID =prior ID 　　startwith PARENTID isnull 　　connect by的标准语法为： 　　SELECT expression [,expression] … 　　FROM [user.] table 　　WHERE condition 　　CONNECT BY [PRIOR] expression = [ PRIOR ] expression 　　START WITH expression = expression 　　ORDER BY expression 　　Start with指定从哪个节点开始遍历; 　　Connect by 指定父子节点的连接方式，其中prior修饰遍历的初始节点; 　　Order by 指定排序关键字，但是使用order by 会破坏节点的遍历顺序; 　　Connect by 工具允许Level关键字，Level代表节点的深度; 　　用where可以过滤行，但不影响其关系节点; 　　用connect by可以过滤行以及该行的关系节点。 　　五、提高connect by的查询效率 　　如果多级编目表的数据较多或层次较多，为了提高查询效率，可以为多级编目表建立索引。索引的建立取决于connect by子句。上面例子的connect by子句为： 　　connectby PARENTID =prior ID 　　这时可以为该表建立两个索引，第一个索引在 PARENTID和ID上建立，第二个索引在ID和PARENTID上建立。这样无论正序还是逆序遍历该表，都会用索引访问代替全表扫描，从而提高查询效率。 　　六、排序会打乱多级编目表的遍历顺序 　　connect by按照深度优先原则进行遍历，所以尽管使用各种排序的办法，connect by仍然会将搜索深度最大的行排在前面。也就是说，在connect by 中使用order by 将得不到正确的遍历顺序。