第四章 ORACLE表管理

第四章ORACLE数据库对象-------表４.1ORACLE表和数据类型第四讲Oracle数据表４.2DUAL表４.3常规表(堆表)(NormalTable)４.5索引组织表(IndexOrganizedTableIOT)４.6对象表(ObjectTable)４.7嵌套表(NestedTable)４.8其他表４.4分区表(PartitionsTable)重点掌握各种类型表的创建方法了解常规表、分区表、索引组织表和对象表的物理组织形式及其特点了解各种表的使用场合４.1ORACLE表和数据类型表是数据库数据存储的基本单元,它对应于显示世界中的对象(部门和雇员等)。当进行数据库设计时,需要构造E-R图,在将E-R图转变为数据库对象时,实体最终要转换为数据库表。４.1.1ORACLE表简介BOSTONOPERATIONS40CHICAGOSALES30DALLASRESEARCH20NEWYORKACCOUNTING10LOCDNAMEDEPTNO表中存储的数据的逻辑结构是一张由行列组成的二维表。表中的一行又叫一条记录，或一个元组。一条记录描述一个实体;一列描述实体的属性，如部门有部门代码、部门名称、位置等属性，每个列还具有列名、数据类型、长度、约束条件、默认值等等。ROWID是表的伪列，ROWID给出了表行的物理位置,用来唯一的标识表行。是定位表行最快的方式，它与其他列一样可以直接查询。ROWID在数据文件中其数据是掩码格式存放，所以一般用户不能直接读懂它的内容。而必须用包DBMS_ROWID进行转换。４.1.2ORACLE数据行的物理标识rowid４.1ORACLE表和数据类型selectdeptno,dname,rowidfromscott.dept;selectdeptno,dname,rowid,dbms_rowid.rowid_relative_fno(rowid)file#,dbms_rowid.rowid_block_number(rowid)block#,dbms_rowid.rowid_row_number(rowid)row#fromscott.dept;为DEPT表的DEPTNO建立索引,分析数据库是如何存储索引信息的?如何利用索引来加快查找速度的?用户表空间DEPTROWIDDEPTNO其他列rowid110CHICAGOG1rowid230NEWYORKG1rowid340DALLASG2rowid420BOSTONG2形成索引列表rowid340rowid230rowid420rowid110INDEX_DEPTNO索引表空间Select*fromdeptWheredeptno=20最大长度4000chars根据字符集而定的可变长度字符串(以字符为单位)VARCHAR2(nchar)最大长度2000chars根据字符集而定的固定长度字符串(以字符为单位)CHAR(nchar)可做索引的最大长度749最大长度4000bytes可变长度的字符串(以字节为单位)VARCHAR2(n)最大长度2000bytes固定长度字符串(以字节为单位)CHAR(n)其它说明限制条件中文说明字段类型字符型４.1.3ORACLE基本数据类型(PAGE40)４.1ORACLE表和数据类型注解：因为char类型总是会用空格填充使之达到一个固定宽度，无论在是表段中还是索引段中，它都会占用很大的存储空间。NUMBER(63)，精度更高实数类型REALNUMBER(38)，双精度浮点数类型FLOAT小的整数NUMBER(38)整数类型INTEGER/INTP为整数位，S为小数位数字类型DECIMAL(P,S)/NUMERIC(P,S)精度13位；范围-10308.25--10308.25在内存中占9个字节双精度浮点数类型BINARY_DOUBLE精度6位；范围-1038.53--1038.53;在内存中占5个字节单精度浮点数类型BINARY_FLOATP为总位数，S为小数位；精度P=38；长度0~22字节，范围10e-130~10e126数字类型NUMBER(P,S)数值型４.1.3ORACLE基本数据类型为数值型分配存储空间的公式:Round((length(number)+s)/2)+1当number=0s=0;当number0s=1４.1ORACLE表和数据类型注解：number可以很精确的存储数值;如果对科学数据执行数据挖掘或进行复杂的数值分析，精度损失往往是可以接受的，使用binary_float、binary_double可能会得到非常显著的性能提升。字段类型中文说明限制条件DATE(1)固定日期/时间格式;(2)7字节宽度描述世纪、年、月、日、时、分、秒7个属性;(3)使用DUMP函数，可查看实际存储。默认格式DD-MM-YY（HH-MI-SS），可以通过修改nls_language和nls_date_format两个参数来更改日期的显示。TIMESTAMP（n）(1)与DATE类似;存储方式与DATE相同;(2)支持小数秒N的取值为0~9INTERVAL时间量４.1ORACLE表和数据类型日期时间型４.1.3ORACLE基本数据类型注解:date类型世纪和年份采用一种“加100”表示法来存储。月和日采用自然的存储方式。时、分、秒采用“加1”表示法存储。４.1ORACLE表和数据类型其他数据型４.1.3ORACLE基本数据类型最大长度4000bytes二进制数据表中记录的唯一行号NROWID10bytes********.****.****格式，*为0或1数据表中记录的唯一行号ROWID最大长度4G存放在数据库外的二进制数据BFILE最大长度4G根据字符集而定的字符数据NCLOB最大长度4G字符数据CLOB最大长度4G二进制数据BLOB最大长度2G可变长度的二进制数据LONGRAW最大长度2000bytes固定长度的二进制数据RAW最大长度2G（231-1）超长字符串LONG４.2DUAL表DUAL表属于SYS模式,它在数据字典创建时自动建立。DUAL表只有一列和一行，列名为dummy。由于ORACLE中大部分操作都必须在表中，如对一个代数表达式求值，可以在该表上使用SELECT命令计算一个常量表达式。DUAL表在此充当了那些表达式的包罗万象的容器。SQLServerClientSomeQueryResultsdummydual４.3常规表(NormalTable)创建常规表的方法和过程3创建常规表前准备工作2常规表及其特征1DDLTABLESPACE数据定义命令usersTABLE1表结构的查看与维护4常规表及其特征1４.3常规表(NormalTable)常规表又叫做堆组织表（heap）,它是一个无序行集合，并不是以某种特定的顺序来存储数据。堆是计算机领域中深入研究的一种经典的数据结构，它的特点是会将数据放在最合适的地方，而不是按照某种特定顺序来放置。Example:建立一个测试表t，数据库中的每个数据块刚好能放一个整行（数据库块是8KB),向表中插入多条记录,验证记录存储的无序性。根据数据需求分析设计数据库表的模式,应该使用范式来规划每个表。定义表列时，应选择合适的数据类型和长度。确定表中所需要的完整性约束。规划表的位置，将表部署在特定的表空间上(USERS表空间)。预计和规划表的大小。在oracle10G中，通过OEMDatabasecontrol估算表大小。用户与权限问题。(CREATEANYTABLE、CREATETABLE、UNLIMITEDTABLESPACE)４.3常规表(NormalTable)创建常规表前准备工作2４.3常规表(NormalTable)report_card学生成绩管理数据库deptcoursestudent实例：学生成绩管理项目数据库设计dept字段名注释类型长度备注dept_no部门编号varchar28PKdept_name部门名称varchar240dept_mng部门经理varchar28address地址varchar280cont部门人数numberstudent字段名注释类型长度备注student_no学号varchar212PKstudent_name姓名varchar210dept_no部门编号varchar28FKsex性别varchar21CKtype学生类型varchar21CKhiredate入学日期dateprovince籍贯varchar220empid身份证号varchar225address详细地址varchar260borndate出生日期datespeciality所学专业varchar220nation民族varchar220htelno电话Varchar220money缴费总计Number(10,2)remarks备注Varchar280course字段名注释类型长度备注course_no课程号varchar28PKcourse_name课程名称varchar240grade开设年级varchar28period学时数numberreport_card字段名注释类型长度备注student_no学号varchar212FKcourse_no课程号varchar28FKterm学期varchar28result成绩number（5，2）remark备注varchar280４.3常规表(NormalTable)创建常规表的方法和过程3使用CreateTable脚本在SQLPlus环境中实现。使用OEM控制台通过图形界面创建表结构。在原来已有表的基础上建立新表。４.3常规表(NormalTable)使用CreateTable脚本创建常规表Createtableschema.table_name(列名1类型（长度）列约束,列名2类型（长度）列约束,……,表约束)PctfreenPctusednInitransnMaxtransnTablespacetablespace_nameStorage(InitialnNextnPctincreasenMinextentsnMaxextentsn)指定表的每一个数据块为Update操作所保留的空间百分比,默认10指定在数据块上可以重新插入数据的已用空间最低百分比。指定可同时对表所有块进行更新操作的最小和最大的事务数将所创建的表部署到规定的表空间中指定表第一个区的大小。指定初始扩展区的大小.指定扩展区递增的百分比。NEXT=(1+n/100）*Old_next指定为表段分配的区数的最小值和最大值。Createtabletest(anumber)storage(initial100Knext100Kminextents2maxextents100pctincrease100);４.3常规表(NormalTable)示例:以新用户zhang登录,在方案zhang下创建学生成绩管理项目四个表。zhangCREATETABLEZHANG.dept(DEPT_NOvarchar2(8),DEPT_NAMEvarchar2(40),DEPT_MNGvarchar2(8),ADDRESSvarchar2(80),CONTnumber,PRIMARYKEY(DEPT_NO))pctfree20pctused50tablespaceusersstorage(initial100knext100kminextents2maxextents100pctincrease0);使用OEM创建常规表４.3常规表(NormalTable)示例:创建学生基本信息表student,包含student_no等15个字段。其中student_no为主键；student_name非空；dept_no参照与表dept中的dept_no的取值;type取值仅为1、2、3；sex取值只能为0或1。该表的表空间为users，保留用于更新的空闲空间的百分比为10%，并发事务个数初始值为2个，最多并发255个事务。为表分配