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

 

一、 可扩展多级编目的定义

可扩展多级编目有下述特点：

• 编目分级，上下级编目形成父子关系；
• 任意节点可以有零到多个子节点，任意子节点有且只有一个父节点；
• 节点深度在理论上可以无限扩充；

由上述特点可知，可扩展多级编目实际上是树。

 

二、 可扩展多级编目的例子

Windows 操作系统中的文件路径是一个典型的多级编目结构。多级编目结构的例子还有很多：人的家谱、文献索引、制造业的 BOM 、财务的科目编码、公司组织结构等。

 

三、 可扩展多级编目在 Oracle 中的设计

概念设计：

XXX_CLASS_TAB
PK	ID
FK	PARENTID NAME DESCRIBE

 

物理设计：

表名	XXX_CLASS_TAB
注释	多级编目表
数据量的估计	行		存储空间的估计			兆
表空间			索引表空间
索引	ID+PARENTID PARENTID+ID
别名	列名	数据类型	空值	缺省	规则		注释
ID	ID	NUBMER	N		PK		自增序列
父类ID	PARENTID	NUBMER	Y		FK		FK( XXX_CLASS_TAB.ID )
名称	NAME	VARCHAR(128)	N
描述	DESCRIBE	VARCHAR(1024)	Y

 

       多级编目表的设计思路跟“树”的设计思路一样。 ID 是行的唯一标识，用 Oracle 的序列和触发器（可参见有关文章）实现自增长。 PARENTID 是指向本表的外键，如果该行有父节点则引用本表中父节点行的 ID ，如果该行是根节点则为空。

 

四、 用 connect by 遍历多级编目表

用 Oracle 提供的 connect by 命令可以正序或逆序遍历多级编目表。正序遍历的例子：

 

select ID , PARENTID , NAME , DESCRIBE ,level from XXX_CLASS_TAB

connect by PARENTID = prior ID

start with PARENTID is null

 

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 子句为：

 

connect by PARENTID = prior ID

 

这时可以为该表建立两个索引，第一个索引在 PARENTID 和 ID 上建立，第二个索引在 ID 和 PARENTID 上建立。这样无论正序还是逆序遍历该表，都会用索引访问代替全表扫描，从而提高查询效率。

 

六、 排序会打乱多级编目表的遍历顺序

    connect by 按照深度优先原则进行遍历，所以尽管使用各种排序的办法， connect by 仍然会将搜索深度最大的行排在前面。也就是说，在 connect by 中使用 order by 将得不到正确的遍历顺序。