oracle 嵌套表学习

 

1   基础知识

嵌套表是表中之表。一个嵌套表是某些行的集合，它在主表中表示为其中的一列。对主表中的每一条记录，嵌套表可以包含多个行。在某种意义上，它是在一个表中存储一对多关系的一种方法。

嵌套表不需要执行联合操作，就可以通过相应的嵌套表信息列。这种不经联合而直接选择数据的能力使得用户对数据访问更加容易。甚至在并没有定义方法来访问嵌套表的情况下，也能够很清楚地把部门和项目中的数据联系在一起。在严格的关系模型中，父子两个表的联系需要通过外部关键字（外键）关系才能实现，即NESTED_TABLE_ID外键，默认情况下，每个嵌套表列都产生一个额外的RAW(16)隐藏列，并在其上创建了唯一约束，用以指向嵌套表。而嵌套表中有两隐藏列：SYS_NC_ROWINFO$是作为一个对象返回所有标量元素的一个伪列；另一个NESTED_TABLE_ID的外键回指向父表。

一般推荐在编程结构和视图中使用嵌套表。如果要使用嵌套表作为存储机制，确保嵌套表是IOT，以避免NESTED_TABLE_ID和嵌套表本身中索引的额外开销.

 

嵌套表中的子表不能单独查询，但是如果确实需要，是可以的。采用hint指令。

SELECT /*+NESTED_TABLE_GET_REFS+*/ NESTED_TABLE_ID, SYS_NC_ROWINFO$ FROM  嵌套子表;

 

2   应用

之前的函数只返回一个总分值，现在需要获得多个信息。选择方法有创建多个其他函数各种返回1个值，这些函数的流程几乎一摸一样。需要执行多次，后续若增加其他需求，就得创建更多函数了。执行多个函数后，性能肯定会下降。

而采用嵌套表可以解决这个问题，只执行一次获得很多信息，但是基于嵌套表的访问性能有所下降，因此需要平衡。

2.1    创建嵌套表类型

CREATE OR REPLACE TYPE OBJ_TYPE_XX AS OBJECT (

info1 VARCHAR2(15),

info2 VARCHAR2(15),

info3 NUMBER(10,0),

info4 NUMBER(10,0),

info5 NUMBER(10,0)

--后续增加其他字段

);

 

CREATE OR REPLACE TYPE TBL_TYPE_XX AS TABLE OF OBJ_TYPE_XX;

 

2.2    创建返回嵌套表类型的函数

 

CREATE OR REPLACE FUNCTION  MyFunc(

  v_args2 VARCHAR2,

  v_args1 VARCHAR2)

RETURN tbl_type_userscore3

AS

   Result tbl_type_XX;

   VAR11 NUMBER;

   VAR22 NUMBER;

BEGIN

  Result:=tbl_type_XX(obj_type_XX(v_args2 ,v_args1 VAR11 , VAR11 ,VAR22));

  return(Result);

END;

 

2.3    表的创建

2.3.1   版本1

CREATE TABLE T_XXX(

    COL1 VARCHAR2(15) NOT NULL,

    COL2 VARCHAR2(15) NOT NULL,

    ....

    COLN TBL_TYPE_XX 

)

NESTED TABLE COLN STORE AS EMBED_Table_XX tablespace orcl;

2.3.2   使用嵌套表

创建测试视图

CREATE OR REPLACE VIEW V_VIEW AS

select  T1.*,T2.* from  -- 不能用t1.*，有2个重复列

T_XXX t1,TABLE(t1.COLN ) t2;

select *from V_VIEW where 选择从句

查询需要2秒,查询计划如下：

 

为嵌套表增加索引

create unique index  IDX_1111 on EMBED_Table_XX (nested_table_id) TABLESPACE oacl

增加unique index 后， 选择行数下降很多，cost也下降很多。

select *from V_VIEW where 选择从句

查询需要0.1秒,查询计划如下：

 

2.3.3   Version2

上个版本增加索引会额外的空间开销，采用如下IOT方式避免。

CREATE TABLE T_XXX(

    COL1 VARCHAR2(15) NOT NULL,

    COL2 VARCHAR2(15) NOT NULL,

    ....

    COLN TBL_TYPE_XX 

) tablespace orcl

NESTED TABLE COLN  STORE AS EMBEDED_Table_3

　　((

PRIMARY KEY(nested_table_id)));