Expert OneonOne Oracle阅读笔记6.docx
《Expert OneonOne Oracle阅读笔记6.docx》由会员分享，可在线阅读，更多相关《Expert OneonOne Oracle阅读笔记6.docx（76页珍藏版）》请在冰豆网上搜索。
ExpertOneonOneOracle阅读笔记6
ExpertOne-on-OneOracle阅读笔记
第6章数据库表
6.1表的类型
1. 堆组织表
2. 索引组织表
3. 聚簇表
4. 散列聚簇表
5. 嵌套表
6. 临时表
7. 对象表
一张表最多有1000列；表的行数理论上没有限制；表上索引个数可以是列的全排列数，而且一次性能够使用32个；表的数量没有限制。
6.2术语
高水位标记HighWaterMark
曾经包含数据的最右边的块。
在全表扫描时，Oracle将扫描高水标记一下的所有块，即使它们不含数据。
TRUNCATE将重新设置高水标记。
自由列表 Freelist
在Oracle中用来跟踪高水标记以下有空闲空间的块对象。
保留在高水标记以上的块，只有Freelist为空时才能被用到。
并行更新数据时，配置多个Freelist能提高整体性能，代价是增加了存储空间。
PCTFREE和PCTUSED
INITIAL,NEXT和PCTINCREASE
建议使用LocalManaged表空间并设置Extents大小相等。
而在没有使用LocalManaged表空间的情况下，建议总是设置INITIAL=NEXT和PCTINCREASE=0，以模拟LocalManaged表空间的使用。
MINEXTENTS和MAXEXTENTS
LOGGING和NOLOGGING
INITRANS和MAXTRANS
堆组织表
6.3索引组织表
数据在IOT中根据主键存储和排序。
IOT特别适用于IR（信息检索）、空间和OLAP应用程序。
IOT名义上是表，但它们的段实际上是索引段。
要显示空间使用等就要先把IOT表的名字转换成潜在的索引名。
默认值是SYS_IOT_TOP_，object_id是为表分配的内部对象ID。
推荐在建表时指定索引名。
主要应用
对只包含主键列的表：
使用堆组织表将有100%多的额外开销；
1．构建自己的索引结构：
例如自己实现一个提供大小写不敏感查询的类似函数索引
CREATETABLEempASSELECT*FORMscott.emp;
CREATETABLEupper_name
（x$ename,x$rid,
PRIMARYKEY（x$ename,x$rid）
）
ORGANIZATIONINDEX
AS
SELECTUPPER（ename）,ROWIDFROMemp;
CREATEORREPLACETRIGGERupper_ename
AFTERINSERTORUPDATEORDELETEONemp
FOREACHROW
BEGIN
IF（UPDATINGAND（:
OLD.ename||'x'<>:
NEW.ename||'x'））
THEN
DELETEFROMupper_name
WHEREx$ename=UPPER（:
OLD.ename）
ANDx$rid=:
OLD.rowid;
INSERTINTOupper_ename（x$ename,x$rid）VALUES（UPPER（:
NEW.ename）,:
NEW.rowid）;
ELSIF（INSERTING）
THEN
INSERTINTOupper_ename（x$ename,x$rid）VALUES（UPPER（:
NEW.ename）,:
NEW.rowid）;
ELSIF（DELETING）
THEN
DELETEFROMupper_name
WHEREx$ename=UPPER（:
OLD.ename）
ANDx$rid=:
OLD.rowid;
ENDIF;
END;
2．需要加强数据的共同定位或希望数据按特定的顺序物理存储时
对应Sybase和SQLServer用户，这种情况会采用聚簇索引，而这可能达到110%的额外开销，而IOT没有。
经常用BETWEEN对主键或者唯一键进行查询，则会降低I/O数量。
主要选项
NOCOMPRESS/COMPRESSN
压缩N列，即对其中前N列相同的值进行压缩。
从而能够允许更多数据进入BufferCache，代价是略多的CPU能量。
OVERFLOWPCTTHRESHOLDN/INCLUDINGcolumn_name
索引段的存储要密集于普通数据段（每块的行数要多），一般PCTUSED是没有意义的。
而OVERFLOW子句允许设置另一个段以允许IOT中的行数据太大时溢出的这个段中。
它再次引入PCTUSED，这样PCTUSED和PCTFREE对OVERFLOW段有对于堆组织表中相同的含义。
而使用方法是如下中的一种：
PCTTHRESHOLD——当行中数据超出此百分比，该行尾部的列溢出到溢出块；
INCLUDING——指定列之前的列均存入索引块，之后的列存入溢出块。
二次索引
只要主键是IOT，可以在索引中拥有索引。
但不像其他一般索引，它不包含真正rowid（物理地址），而是基于主键IOT的逻辑rowid，作用稍小。
对于IOT的二次索引访问实际有两个扫描执行（一般表只需一个扫描索引结构），一个在二次结构中，一个在IOT本身中。
6.4索引聚簇表
Oracle中聚簇是存储一组表的方法，而不是如同SQLServer、Sybase中那样（那是Oracle中的IOT）。
概念上是通过聚簇码列将几张表“预连接”，尽可能将聚簇码列相同的几张表的行放入同一个块中。
CREATECLUSTERemp_dept_cluster
（deptnoNUMBER
（2））
SIZE1024;
CREATEINDEXemp_dept_cluster_idx
ONCLUSTERemp_dept_cluster;
CREATETABLEdept
（deptnoNUMBER
（2）PRIMARYKEY,
dnameVARCHAR2（14）,
locVARCHAR2（3）
）
CLUSTERemp_dept_cluster（deptno）;
CREATETABLEemp
（empnoNUMBERPRIMARYKEY,
enameVARCHAR2（10）,
...
deptnoNUMBER
（2）REFERENCESdept（deptno）
）
CLUSTERemp_dept_cluster（deptno）;
BEGIN
FORxIN（SELECT*FROMscott.dept）
LOOP
INSERTINTOdeptVALUES（x.deptno,x.dname,x.loc）;
INSERTINTOemp
SELECT*FROMscott.emp
WHEREdeptno=x.deptno;
ENDLOOP;
END;
注意这里的插入方法，这将尽可能保证每个块中放置尽可能多的聚簇码值，并让可以“预连接”的两个表中的值尽可能在同一个块中。
DBMS_ROWID.ROWID_BLOCK_NUMBER（rowid）可用于检查rowid所属块。
很容易发现dept和emp有重复的rowid，表和rowid可以唯一确定行，rowid伪列只有在一张表中才是唯一的！
不使用聚簇的情况：
1．聚簇可能消极影响DML性能；
2．全扫描表的性能会受到影响——不仅仅扫描一个表，而是对多个表全扫描；
3．聚簇中的表不能TRUNCATE。
6.5散列聚簇表
概念类似索引聚簇表，但用散列函数代替了聚簇码索引。
Oracle采用行的码值，使用内部函数或者自定义的函数进行散列运算，从而指定数据的存放位置。
这样没有在表中增加传统的索引，因此不能RangeScan散列聚簇中的表，而只能全表扫描（除非单独建立索引）。
CREATECLUSTERhash_cluster
（hash_keyNUMBER）
HASHKEYS1000
SIZE8192;
索引聚簇需要空间时是动态分配，而散列聚簇表在创建时确定了散列码数（HASHKEY）。
Oracle采用第一个不小于HASHKEY的质数作为散列码数，将散列码数*SIZE就得到分配的空间（字节），可容纳HASHKEYS/TRUNC（BLOCKSIZE/SIZE）字节的数据。
性能上，散列聚簇表消耗较少I/O，较多CPU，所需执行时间较少，大体取决于CPU时间（当然可能要等待I/O，取决于配置）。
下列情况下使用散列聚簇表较为合适：
1.在一定程度上精确知道整个过程中表中记录行数或者合理的上限，以确定散列码数；
2. 不大量执行DML，尤其是插入。
更新不会产生显著的额外开销，除非更新HASHKEY，这样会导致行迁移；
3. 总是通过HASHKEY值访问数据。
6.6嵌套表
两种使用嵌套表的方法：
1．PL/SQL代码中作为扩展PL/SQL语言；
2．作为物理存储机制，以持久地存储集合。
嵌套表语法
创建嵌套表类型：
CREATETABLEdept
（deptno NUMBER
（2）PRIMARYKEY,
dname VARCHAR2（14）,
loc VARCHAR2（13）
）;
CREATETABLEemp
（empno NUMBER（4）PRIMARYKEY,
ename VARCHAR2（10）,
job VARCHAR2（9）,
mgr NUMBER（4）REFERENCESemp,
hiredate DATE,
sal NUMBER（7,2）,
comm NUMBER（7,2）,
deptno NUMBER
（2）REFERENCESdept
）;
INSERTINTOdeptSELECT*FROMscott.dept;
INSERTINTOempSELECT*FROMscott.emp;
CREATEORREPLACETYPEemp_type
ASOBJECT
（empno NUMBER（4）,
ename VARCHAR2（10）,
job VARCHAR2（9）,
mgr NUMBER（4）,
hiredate DATE,
sal NUMBER（7,2）,
comm NUMBER（7,2）
）;
CREATEORREPLACETYPEemp_tab_type
ASTABLEOFemp_type;
使用嵌套表：
CREATETABLEdept_and_emp
（deptno NUMBER
（2）PRIMARYKEY,
dname VARCHAR2（14）,
loc VARCHAR2（13）,
emps emp_tab_type
）
NESTEDTABLEempsSTOREASemps_nt;
可以在嵌套表上增加约束：
ALTERTABLEemps_ntADDCONSTRAINTemps_empno_unique
UNIQUE（empno） ;
嵌套表不支持参照完整性约束，不能参考任何其他表甚至自己：
ALTERTABLEemps_ntADDCONSTRAINTmgr_fk
FOREIGNKEY（mgr）REFERENCESemps_nt（empno）;
会产生错误ORA-30730。
INSERTINTOdept_and_emp
SELECTdept.*,
CAST（MULTISET（SELECTempno,ename,job,mgr,hiredate,sal,comm
FROMemp
WHEREemp.deptno=dept.deptno）ASemp_tab_type）
FROMdept;
MULTISET用来告诉Oracle子查询返回不止一行，CAST用来告诉Oracle将返回设置为一个集合类型。
查询时，嵌套表中的数据将在同一列中：
SELECTdeptno,dname,loc,d.empsASemployees
FROMdept_and_empd
WHEREdeptno=10;
Oracle同样提供方法去掉集合的嵌套，像关系型表一样处理（能够将EMPS列当作一个表，并自然连接且不需要连接条件）：
SELECTd.deptno,d.dname,emp.*
FROMdept_and_empD,TABLE（d.emps）emp;
按照“每行实际是一张表”的思想来更新：
UPDATE
TABLE（SELECTemps
FROMdept_and_emp
WHEREdeptno=10
）
SETcomm=100;
但如果返回SELECTempsFROMdept_and_empWHEREdeptno=10少于一行，更新将失败（普通情况下更新0行是许可的），并返回ORA-22908错误——如同更新语句没有写表名一样；如果返回多于一行，更新也会失败，返回ORA-01427错误。
这说明Oracle在使用了嵌套表后认为每一行指向另一个表，而不是如同关系型模型那样认为是另一个行集。
插入与删除的语法：
INSERTINTOTABLE
（SELECTempsFROMdept_and_empsWHEREdeptno=10）
VALUES
（1234,'NewEmp','Clerk',7782,SYSDATE,1200,NULL）;
DELETEFROMTABLE
（SELECTempsFROMdept_and_empsWHEREdeptno=20）
WHEREename='SCOTT';
一般而言，必须总是连接，而不能单独查询嵌套表（如EMPS）中的数据，但是如果确实需要，是可以的。
提示NESTED_TABLE_GET_REFS被用于EXP和IMP处理嵌套表。
SELECT/*+NESTED_TABLE_GET_REFS+*/
NESTED_TABLE_ID,SYS_NC_ROWINFO$
FROM"TKYTE"."EMPS_NT";
而我们察看EMPS_NT的表结构是看不到NESTED_TABLE_ID,SYS_NC_ROWINFO$两列的。
对父表DEPT_AND_EMP来说NESTED_TABLE_ID是一个外键。
使用这个提示就可以直接操作嵌套表了：
UPDATE /*+NESTED_TABLE_GET_REFS+*/emps_nt
SETename=INITCAP（ename）;
嵌套表存储
上例中，现实产生了两张表：
DEPT_AND_EMP
deptno
NUMBER
（2）
dname
VARCHAR2（14）
loc
VARCHAR2（13）
SYS_NC0000400005$
RAW（16）
EMPS_NT
SYS_NC_ROWINFO$
NESTED_TABLE_ID
RAW（16）
empno
NUMBER（4）
ename
VARCHAR2（10）
job
VARCHAR2（9）
mgr
NUMBER（4）
hiredate
DATE
sal
NUMBER（7,2）
comm
NUMBER（7,2）
默认情况下，每个嵌套表列都产生一个额外的RAW（16）隐藏列，并在其上创建了唯一约束，用以指向嵌套表。
而嵌套表中有两个隐藏列：
SYS_NC_ROWINFO$是作为一个对象返回所有标量元素的一个伪列；另一个NESTED_TABLE_ID的外键回指向父表。
可以看到真实代码：
CREATETABLETKYTE.DEPT_AND_EMP
（DEPTNO NUMBER（2,0）,
DNAME VARCHAR2（14）,
LOC VARCHAR2（13）,
EMPS EMP_TAB_TYPE）
PCTFREE10PCTUSED40INITRANS1MAXTRANS255LOGGING
STORAGE（INITIAL131072NEXT131072
MINEXTENTS1MAXEXTENTS4096
PCTINCREASE0FREELISTS1FREELISTGROUP1
BUFFER_POOLDEFAULT）
TABLESPACEUSER
NESTEDTABLEEMPS
STOREASEMPS_NT
RETURNBYVALUE;
RETURNBYVALUE用来描述嵌套表如何返回到客户应用程序中。
NESTED_TABLE_ID列必须是索引的，那么较好的解决办法就是使用IOT存储嵌套表。
CREATETABLETKYTE.DEPT_AND_EMP
（DEPTNO NUMBER（2,0）,
DNAME VARCHAR2（14）,
LOC VARCHAR2（13）,
EMPS EMP_TAB_TYPE）
PCTFREE10PCTUSED40INITRANS1MAXTRANS255LOGGING
STORAGE（INITIAL131072NEXT131072
MINEXTENTS1MAXEXTENTS4096
PCTINCREASE0FREELISTS1FREELISTGROUP1
BUFFER_POOLDEFAULT）
TABLESPACEUSER
NESTEDTABLEEMPS
STOREASEMPS_NT
（（empnoNOTNULL,
UNIQUE（empno）,
PRIMARYKEY（nested_table_id,empno））
ORGANIZATIONINDEXCOMPRESS1）
RETURNBYVALUE;
这样与最初默认的嵌套表相比，使用了较少的存储空间并有最需要的索引。
不使用嵌套表作为永久存储机制的原因
1．增加了RAW（16）列的额外开销，父表和子表都将增加这个额外的列；
2．当通常已经有唯一约束时，父表上的唯一约束是额外开销；
3．没有使用不支持的结构（NESTED_TABLE_GET_REFS），嵌套表不容易使用。
一般推荐在编程结构和视图中使用嵌套表。
如果要使用嵌套表作为存储机制，确保嵌套表是IOT，以避免NESTED_TABLE_ID和嵌套表本身中索引的额外开销。
6.7临时表
Oracle的临时表与其他数据库中的不同，其定义是“静态”的。
以事务（ONCOMMITDELETEROWS）或者会话（ONCOMMITPRESERVEROWS）为基础，只是说明数据的生命期，而在数据库中创建临时表一次，其结构总是有效的，被作为对象存在数据字典中了，这样也就允许对临时表建立视图、存储过程中用静态SQL引用临时表等等。
在实际开发中，考虑到DDL是消耗较大的操作，应该避免在运行时操作，而是将应用程序需要的临时表在程序安装时就创建，而只是在存储过程中简单的INSERT、SELECT。
临时表不支持的永久表的特性有：
1．不能用参照完整性约束，也不能被参照完整性约束所引用；
2．不能有VARRAY或者NESTEDTABLE类型的列；
3．不能是IOT；
4．不能是索引或者散列聚簇；
5．不能分区；
6．通过ANALYZE命令不能产生统计信息，也即是说优化器在临时表上没有真正的统计功能。
由于缺少统计功能，那么CBO（基于成本的优化器）的性能将受到极大的影响，因此应当尽可能使用INLINEVIEW。
要让临时表拥有正确的统计信息，CBO产生正确的决策，可以先建立一张结构与临时表完全相同的普通表：
CREATETABLEtemp_all_objects
AS
SELECT*FROMall_objectsWHERE1=0;
CREATEINDEXtemp_all_objects_idxONtemp_all_objects（object_id）;
选择插入代表性数据后进行分析：
...
ANALYZETABLEtemp_all_objectsCOMPUTESTATISTICSFORALLINDEX;
BEGIN
DBMS_STATS.CREATE_STAT_TABLE（ownname=>USER,
stattab=>'STATS'）;
DBMS_STATS.EXPORT_TABLE_STATS（ownname=>USER,
tabname=>'TEMP_ALL_OBJECTS',
stattab=>'STATS'）;
DBMS_STATS.EXPORT_INDEX_STATS（ownname=>USER,
tabname=>'TEMP_ALL_OBJECTS_IDX',
stattab=>'STATS'）;
END;
建立临时表：
DROPTABLEtemp_all_objects;
CREATEGLOBALTEMPORARYTABLEtemp_all_objects
AS
SELECT*FROMall_objectsWHERE1=0;
导入正确的信息后CBO将使用这些信息决定执行模式
升级会员 