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))); |