01- Oracle 存储与关键表设计

1、Oracle RDBMS存储与表设计赵元杰Zyj56812007年8月9/2/20221Oracle 数据库优化设计内容提要Oracle 表空间与块结构与段对象创建表结构准备创建表结构语法与参数解释创建带有存储控制的样例表9i/10g存储参数与块参数的变化表数据存储量大小估计表的修改及其它处理参考资料9/2/20222Oracle 数据库优化设计Oracle 数据库-表空间-对象TablespaceTables, indexes, etc.DatafilesSegmentExtentBlocks9/2/20223Oracle 数据库优化设计Oracle 表空间(Tablespaces)表空间类

2、型System/NonsystemOnline/OfflineRead-Write/Read-OnlyTransportable表空间种类永久表空间(存储表、索引)临时表空间(存储排序数据)撤消表空间(存储DML数据)9/2/20224Oracle 数据库优化设计表空间(tablespace)与表(Tables)表存储在表空间( Tablespaces)表由行与列构成表包含数据(表占很多块空间，称“段”)TablespaceTables, indexes, etc.DatafilesSegmentExtentBlocks9/2/20225Oracle 数据库优化设计Oracle 表空间与数据文

3、件表空间(Tablespace)：表空间由多个数据文件组成，如SYSTEM表空间由system01.dbf构成；表的数据存储表空间对应的数据文件里；表和索引等可分别存储在不同的表空间；数据文件结构：数据文件由多个Oracle块组成；F1.oraF2.oraF3.oraF4.oraF5.oraTablespace 1Tablespace 2Table 1Table 2Table 3CREATE TABLESPACE user_data DATAFILE udata.ora SIZE 10M EXTENT MANAGEMENT LOCALCREATE TABLE my_emp ( name var

4、char2(20),TABLEASPACE user_data pctfree 5;9/2/20226Oracle 数据库优化设计Oracle 块结构与段对象数据文件都是块构成；内存分配也是以块为单位；I/O也以块为单位；块大小在安装时选择（基本块大小）；不同的Oracle版本允许块大小不一样；目前块大小可以2k32K；Oracle9i/10g/11g在表空间中块大小可变；块大小要根据OS决定；Oracle块太大是OS块大小的倍数(2的幂次方)；Oracle块大小影响到性能；9/2/20227Oracle 数据库优化设计Oracle 块结构与段对象-块内部结构Oracle 数据段(Data s

5、egment) 存放在块里比我们想象要复杂的多：9/2/20228Oracle 数据库优化设计表空间对应一个或多个数据文件；数据文件由多个Oracle块构成；Oracle块可存储下面的段：数据段(Data segment)-tables,triggers等索引段(Index segment)-indexes临时段(Temporary segment):-排序和临时表回滚段(Rollback segment)-undo data 信息LOB段(LOB segment)-与表的基本数据存储或分开的LOB数据Oracle 块结构与段对象9/2/20229Oracle 数据库优化设计内容提要Oracl

6、e 表空间与块结构与段对象创建表结构准备创建表结构语法与参数解释创建带有存储控制的样例表存储参数在9i/10g的变化表数据存储量大小估计表的修改及其它处理参考资料9/2/202210Oracle 数据库优化设计创建表结构准备对象命名：名字130个字符号； 名字只能是az、AZ， 也可在字符后加09；名字可加$、#、_；不许带空字符；最好不能为保留字（可从v$reserved_words中查）与保留字相同，可用双引号； 列名的统一问题：同一种含义的列命名与类型在应用系统中统一 ；命名不要采用拼音或拼音缩写-重复率高、不好猜；命名采用英语或英语缩写；列名太长不好；类型能采用字符就不要采用数字类型。

7、9/2/202211Oracle 数据库优化设计创建表结构准备数据类型使用VARCHAR2 (size)变长- 1 4000 字符CHAR (size)定长 1 2000字符NUMBER (p)整数NUMBER (p, s)定点数十进制 NUMBER浮点数十进制DATE日期类型LONG变长大对象( 4 Gigabyte)NCHAR类似 CHAR, 使用 2-字节 编码BLOB二进制数据，4GBCLOB单字节字符数据，可达4GBNCLOB类似CLOB, 支持2-字节 编码BFILE引用的外部的二进制文件LONG RAW原二进制数据Oracle 8/8i/9i/10g 版本常用数据类型：9/2/2

8、02212Oracle 数据库优化设计创建表结构准备Oracle 10g 一般的数据类型：数据类型说明Char(size)定长字符类型,size=2000字符Varchar(size)可变字符类型，size=4000个字符Varchar2(size)可变字符类型，size=4000个字符(Oracle 建议用)Nchar(size)定长本地语言字符类型,size=2000字符Nvarchar(size)可变本地语言字符类型，size=4000个字符Nvarchar(size)可变本地语言字符类型，size=4000个字符9/2/202213Oracle 数据库优化设计创建表结构准备Oracle

9、 10g 一般的数据类型：数据类型说明date定长的日期类型，占7字节；可包含年月日时分秒timestape时间邮戳类型，包含可包含年月日时分秒；但是秒数的精度为1/100秒Interval year以年月存储的时间区的值，可以是09，(默认为2)Interval day 以天、小时、分、秒存储的时间值，可以是09，默认为2Number(p,s)数字类型，可存储等任何类型BINARY_FLOAT32 Bit 的单精度数据类型(仅10g/11g)BINARY_DOUBLE 64 Bit 的单精度数据类型(仅10g/11g)9/2/202214Oracle 数据库优化设计创建表结构准备Oracle

10、 10g 一般的数据类型：数据类型说明long可变字符类型，可存储=2GB；Long raw可变二进制类型，可存储=2GB；raw可变二进制类型，可存储=4000；BLOB二进制大对象，可存储图像等类型数据；每数据行可存储=8128TBCLOB字符大对象，可存储任何大文本类型数据；每数据行可存储=8128TBNCLOB本地语言字符大对象，类似CLOB类型；BFILE包含指向OS路径的大对象定位器9/2/202215Oracle 数据库优化设计创建表结构准备10g 新BINARY_FLOAT数据类型：32 Bit 的单精度数据类型BINARY_FLOAT； BINARY_FLOAT 以 5个字节

11、存储，含一个长度字节；10g BINARY_DOUBLE ：64 Bit 的双精度数据类型BINARY_DOUBLE ； BINARY_DOUBLE以 9个字节存储，含一个长度字节； BINARY-FLOAT BINARY-DOUBLE MAXIMUM FINITE VALUE 3.40282+38F 1.79769313486231E+308MINIMUM FINITE VALUE 1.17549E-38F 2.22507485850720E-30810g9/2/202216Oracle 数据库优化设计Oracle 对象创建准备-数据类型数据类型选择要点：不要使用long或long raw类

12、型； 一般不要NCHAR等类型；Number(p,s)类型要满足应用系统发展需要；Number(p,s)类型在整个应用系统中要保持一致性！应用系统设计与数据仓库考虑：列名与类型一致性问题； 将来的数据整合问题；统一使用Number(p,s)类型；数字类型整数与精度要一致。9/2/202217Oracle 数据库优化设计内容提要Oracle 表空间与块结构与段对象Oracle 块结构与段对象创建表结构准备创建表结构语法与参数解释创建带有存储控制的样例表9i/10g存储参数与块参数的变化表的修改及其它处理参考资料9/2/202218Oracle 数据库优化设计创建表结构-语法建议采用命令方式：认真

13、研究Oracle的CREATE TABLE子句； 关键的项不要采用默认值，语法如下：CREATE TABLE USER. table_name( COLUMN1 DATATYPE DEFAULT EXPN COLUMN_CONSTRAINT |TABLE_CONSTRAINT , COLUMN1 DATATYPEDEFAULT EXPN COLUMN_CONSTRAINT |TABLE_CONSTRAINT . )CLUSTER CLUSTER (COLUMN1 ,COLUMN2 .) PCTFREE N PCTUSED NINITRANS N MAXTRANS NSTORAGE NTABLE

14、SPACE tablespace_name ENABLE | DISABLE AS QUERY9/2/202219Oracle 数据库优化设计创建表结构语法-子句解释子句说明Table_name表名，字母数字 =0），默认50UNLIMITEDMINEXTENTS参数，用于限制空间的增长9/2/202221Oracle 数据库优化设计创建表结构语法-子句解释子句说明DEFAULT当前列的默认值（常数）INITRANS数据块中用于更新（UPDATE）的空间百分比（1-99），0表示在插入时完全填满数据块，默认为10MAXTRANS每个数据块分配的事务如口数(1-255)，默认1PCTFREE数据

15、块的用于更新的百分比(1-99)，0表示在插入是填满整个数据库块PCTUSED数据块保留的可用空间的最小百分比。取值1-99，默认为40。实际上 PCTUSED是控制自由空间的可用性。9/2/202222Oracle 数据库优化设计创建表结构语法-块变化建议采用命令方式：认真研究Oracle的CREATE TABLE子句； 关键的项不要采用默认值，语法如下：9/2/202223Oracle 数据库优化设计内容提要Oracle 块结构与段对象创建表结构准备创建表结构语法与参数解释创建带有存储控制的样例表9i/10g存储参数与块参数的变化表的修改及其它处理参考资料9/2/202224Oracle

16、数据库优化设计创建表结构样例1-块控制参数create table user_dflow( user_name varchar2(30), /* 用 户 名 */ bj_date date, /* 日期及时间 */. . . day_fee_in number(16,6), /* 日入流量费用 */ day_fee_out number(16,6) /* 日出流量费用 */ )TABLESPACE users PCTFREE 5PCTUSED 80/创建网络计费数据流量明细表user_dflow：根据上网记录的信息很少修改特点，设置PCTFREE 为5；而希望尽快释放删除的块空间而将PCTUS

17、ED设 85； 该表一开始先分配100MB，用完再分配50MB；9/2/202225Oracle 数据库优化设计创建表结构样例2-存储分配参数create table user_dflow( user_name varchar2(30), /* 用 户 名 */ bj_date date, /* 日期及时间 */. . . day_fee_in number(16,6), /* 日入流量费用 */ day_fee_out number(16,6) /* 日出流量费用 */ )TABLESPACE users PCTFREE 5 PCTUSED 80STORAGE ( INITIAL 120M

18、NEXT 60M MINEXTENTS 1PCTINCREASE 0 MAXEXTENTS 9999 )创建网络计费数据流量明细表user_dflow：对user_dflow表的年数据量进行估计：假设年数据量100MB，则可分配120MB； 当分配120MB用完后，由再分配60MB,9/2/202226Oracle 数据库优化设计内容提要Oracle 块结构与段对象创建表结构准备创建表结构语法与参数解释创建带有存储控制的样例表9i/10g存储参数与块参数的变化表的修改及其它处理参考资料9/2/202227Oracle 数据库优化设计存储参数在9i/10g的变化为什么9i/10g取消段的空间扩展

19、设置：表数据的增长与空间的不断减少是矛盾的关键；NEXT 表示必须分配一组连序的Oracle块； 如果表空间无NEXT所要求的连续块，则分配失败；9i/10g 为表扩展空间时不参考NEXT，而是根据表的增长与实际表空间剩余的空间来确定。存储参数设置：字典类型表空间-可设置STORAGE的所有项；本地管理表空间-仅STORAGE的INITIAL有效；9/2/202228Oracle 数据库优化设计存储参数在9i/10g的变化块控制参数设置：9i/10g 本地管理表空间ASSM(AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO，Oracle根据dba_table

20、s.avg_row_len来调整PCTUSED的大小。ASSM(AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL，Oracle根据你的PCTUSED设置来调整PCTUSED的大小；SYSTEM表空间ASSM为MANUAL，USERS表空间ASSM为AUTO(见后面脚本)；9/2/202229Oracle 数据库优化设计存储参数在9i/10g的变化创建表结构前：根据表数据变化特点，创建相应的表空间；表空间的对应数据文件大小要合理；在创建表时指定到创建好的表空间上，并根据表空间是否是字典/本地来确定是否指定NEXT等参数：Set long 9999SELEC

21、T dbms_metadata.get_ddl(TABLESPACE,SYSTEM) from dual ;CREATE TABLESPACE SYSTEM DATAFILE E:ORACLEPRODUCT10.2.0ORADATAORA10SYSTEM01.DBF SIZE 314572800 AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL

22、9/2/202230Oracle 数据库优化设计存储参数在9i/10g的变化ASSM下创建表结构：观察下面脚本运行的结果：DROP TABLE customer;CREATE TABLE customer ( customerID INT NOT NULL PRIMARY KEY, firstName VARCHAR2(30) NOT NULL, lastName VARCHAR2(30) NOT NULL, phone VARCHAR2(20) NULL, address1 VARCHAR2(30) NULL, address2 VARCHAR2(30) NULL, city VARCHAR

23、2(30) NULL, state CHAR(2) NULL, zip CHAR(10) NULL ) PCTFREE 5 PCTUSED 85TABLESPACE SYSTEM STORAGE ( INITIAL 4M NEXT 1M PCTINCREASE 0);-DROP TABLE product; CREATE TABLE product ( productID INT NOT NULL PRIMARY KEY, productName VARCHAR2(30) NOT NULL, productVendor VARCHAR2(50) NULL, cost FLOAT NOT NUL

24、L CONSTRAINT chk_cost CHECK (cost 0), price FLOAT NOT NULL CONSTRAINT chk_price CHECK (price 0) ) PCTFREE 5 PCTUSED 85TABLESPACE USERS STORAGE ( INITIAL 4M NEXT 2M );SQL select tablespace_name,table_name,initial_extent,next_extent,pct_free,pct_used 2 from dba_tables where table_name in (PRODUCT,CUST

25、OMER) 3 /TABLESPACE_NAM TABLE_NAME INITIAL_EXTENT NEXT_EXTENT PCT_FREE PCT_USED- - - - - -SYSTEM CUSTOMER 4194304 5 85USERS PRODUCT 4194304 5-好好观察上面的结果-9/2/202231Oracle 数据库优化设计存储参数在9i/10g的变化LMT的extent_management_clause：AUTOALLOCATE(默认值)区的大小由系统来管理(忽略NEXT等参数)；块小于16K则分配64K；大于16K，则分配1M；然后再根据表增长情况分配区，大小是

26、64K,1M,8MLMT为UNIFORM情况：UNIFORM规定每次扩展都一样的大小；UNIFORM默认为5个Oracle 块；segment_management_clause：SEGMENT SPACE MANAGEMENT MANUAL/AUTO；如果SSM=AUTO，则Oracle忽略对像的PCTUSED, FREELIST和FREELIST GROUPS参数(见前面样例)；9/2/202232Oracle 数据库优化设计存储参数在9i/10g的变化LMT为UNIFORM情况：创建的对象的EXTENT都统一用此尺寸去划分大小；为表指定NEXT，PCTINCREASE 时，这些参数只在创

27、建时有效，之后的扩展无效；9/2/202233Oracle 数据库优化设计存储参数在9i/10g的变化LMT指定UNIFORM 例子：1.创建一表空间，UNIFORM SIZE 为 3M：2.在上面表空间上创建一个表：CREATE TABLESPACE ASSM DATAFILE D:oracleoradataencoreASSM01.DBF SIZE 50M AUTOEXTEND OFFEXTENT MANAGEMENT LOCAL UNIFORM SIZE 3M SEGMENT SPACE MANAGEMENT AUTO;CREATE TABLE ASSMTAB (COL1 NUMBER(

28、3) STORAGE (INITIAL 2M NEXT 5M MINEXTENTS 3 PCTINCREASE 100)TABLESPACE ASSM ;9/2/202234Oracle 数据库优化设计存储参数在9i/10g的变化LMT指定UNIFORM 例子：3.查看该表的扩展信息：根据 INITIAL 2M NEXT 5M MINEXTENTS 3 PCTINCREASE 100，系统分配方法为：INITIAL_EXTENT=(2)+(5)+ (5+(5*100%) = 17 MNext、pctincrease参数创建时是有效的，但以后ORACLE使用统一的尺寸（3M）来为表分配EXTEN

29、T；由于 UNIFORM SIZE = 3M，所以必须 6 个 EXTENT(5次只有5x3=15M)SELECT SEGMENT_TYPE, TABLESPACE_NAME, EXTENT_ID, BYTES, BLOCKS FROM USER_EXTENTSWHERE SEGMENT_NAME = ASSMTAB;SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID BYTES BLOCKS- - - - -TABLE ASSM 0 3145728 384TABLE ASSM 1 3145728 384TABLE ASSM 2 3145728 384TABLE AS

30、SM 3 3145728 384TABLE ASSM 4 3145728 384TABLE ASSM 5 3145728 384SQL select table_name,INITIAL_EXTENT,NEXT_EXTENT,PCT_INCREASE from user_tables where table_name = ASSMTAB;TABLE_NAME INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE- - - -ASSMTAB3 17825792 3145728 09/2/202235Oracle 数据库优化设计表数据存储量大小估计-方法 1 从DDL中了

31、解数据与行大小，如：Table emp (name varchar2(30), salary number (8,2), join_date date);Step 1 : 每行大小 -20B + 6B + 7B = 43BStep 2 : 估计总行数 - 10000(rows)Step 3 : 表的小 - 43(B) * 10000(rows) = 0.5 MBStep 4 : 每年增长率 - 43 * 2000(rows) = 100 KB 一般习惯 : 总是保守的多9/2/202236Oracle 数据库优化设计表数据存储量大小估计-方法 2 从存在的数据得到平均行大小Step 1 :In

32、itial 40,Next 50,Extents 4,PctIncrease 50Step 2 :Size : (90 + 50*1.5 + 50*1.5*1.5)= 287.5Step 3 :MaxExtents (高版本) 不要设置低的值： S = 项目总数 = NEXT(RN -1)/(R-1)这里 R = (100+PctIncrease)/100; N= (MAXEXTENTS - 1) = 总大小 = ( S + INITIAL)一般习惯 : 要合理设置pctincrease是关键!9/2/202237Oracle 数据库优化设计表数据存储量大小估计-方法 3从存在的表中分析以得到

33、统计信息：Step 1 :ANALYZE TABLE EMP ESTIMATECOMPUTE STATISTICS;Step 2 :SELECT (NUM_ROWS*AVG_ROW_LEN) FROM USER_TABLES WHERE TABLE_NAME = EMP;9/2/202238Oracle 数据库优化设计表数据存储量大小估计-检查表增长检查段的扩展次数是否接近 MAXEXTENTSSelect substr(owner | .| segment_name,1,25) “object”,MaxextentsFrom sys.Dba_extents Where maxextents

34、-extents ALTER TABLE emp DROP COLUMN comm;表已更改。9/2/202243Oracle 数据库优化设计修改表结构标识表结构的某个列不可用：标识某个列不可用的语法：ALTER TABLE . . . SET UNUSED COLUMN标识某个列不可用及删除的例子：标识某个列不用后就不能再恢复为可用：SQL Alter table emp 2 set UNUSED COLUMN comm;表已更改。SQL ALTER TABLE emp 2 DROP UNUSED COLUMNS CHECKPOINT 20;表已更改。SQL9/2/202244Oracle

35、数据库优化设计删除表结构删除表结构：就是从Oracle系统中删除该表的数据与结构；语法：删除表结构将影响：指向本表的外部键；表中的索引；本表的触发器；本表中的分区；本表的快照；本表的角色和用户权限；加在本表的所有限制。DROP TABLE user.table_nameCASCADE CONSTRAINTS9/2/202245Oracle 数据库优化设计删除表结构删除表结构注意：删除某个表前要查询该是否其它表存在关系；不要轻易带CASCADE子句：删除表结构建议做：查询某个表的子表：DROP TABLE DEPT CASCADE CONSTRAINTS ;表已删除./* 找出模式下的某个表的子

36、表 */SELECT table_name “child tables” FROM user_constraintsWHERE r_constraint_name IN(SELECT constraint_name FROM user_constraints WHERE table_name = UPPER(&parent_table)AND constraint_type =P ) ;9/2/202246Oracle 数据库优化设计释放不用的空间记录有最高水位 “High water mark” 可释放不使用的扩展Free space after deleteUnused blockHigh water markBeforedeallocationUsed blockALTER TABLE employeesDEALLOCATE UNUSED;Afterdeallocation9/2/202247Oracle 数据库优化设计10g段收缩-表的存储空间收缩9/2/202248Oracle 数据库优化设计10g段收缩-表的存储空间收缩Oracle 9i 提供:删除表,再重建,重加载