Expert OneonOne Oracle阅读笔记6.docx
- 文档编号:9297338
- 上传时间:2023-02-04
- 格式:DOCX
- 页数:76
- 大小:53.61KB
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_
推荐在建表时指定索引名。
主要应用
对只包含主键列的表:
使用堆组织表将有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将使用这些信息决定执行模式
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Expert OneonOne Oracle阅读笔记6 Oracle 阅读 笔记
![提示](https://static.bdocx.com/images/bang_tan.gif)