第四章ORACLE表管理

第四章ORACLE数据库对象-表,.1ORACLE表和数据类型,第四讲Oracle数据表,.2DUAL表,.3常规表(堆表)(NormalTable),.5索引组织表(IndexOrganizedTableIOT),.6对象表(ObjectTable),.7嵌套表(NestedTable),.8其他表,.4分区表(PartitionsTable),重点掌握各种类型表的创建方法了解常规表、分区表、索引组织表和对象表的物理组织形式及其特点了解各种表的使用场合,.1ORACLE表和数据类型,表是数据库数据存储的基本单元,它对应于显示世界中的对象(部门和雇员等)。当进行数据库设计时,需要构造E-R图,在将E-R图转变为数据库对象时,实体最终要转换为数据库表。,.1.1ORACLE表简介,表中存储的数据的逻辑结构是一张由行列组成的二维表。表中的一行又叫一条记录，或一个元组。一条记录描述一个实体;一列描述实体的属性，如部门有部门代码、部门名称、位置等属性，每个列还具有列名、数据类型、长度、约束条件、默认值等等。,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建立索引,分析数据库是如何存储索引信息的?如何利用索引来加快查找速度的?,形成索引列表,Select*fromdeptWheredeptno=20,字符型,.1.3ORACLE基本数据类型(PAGE40),.1ORACLE表和数据类型,注解：因为char类型总是会用空格填充使之达到一个固定宽度，无论在是表段中还是索引段中，它都会占用很大的存储空间。,数值型,.1.3ORACLE基本数据类型,为数值型分配存储空间的公式:Round(length(number)+s)/2)+1当number=0s=0;当numberCOLsegment_nameformata10;SQLSELECTtablespace_name,segment_name,partition_nameFROMuser_segmentsWHEREsegment_name=SALES;,（2）当在分区表上执行DML操作时，ORACLE会根据分区列值的范围在相应的分区上执行操作(T10.SQL)SQLinsertintosalesvalues(1,28500,25-JAN-2003);SQLselect*fromsales-全表扫描wheresales_date=to_date(25-JAN-2003,dd-mon-yyyy);SQLselect*fromsalespartition(p1)-分区扫描wheresales_date=to_date(25-JAN-2003,dd-mon-yyyy);,范围分区（RangePartitioning）,散列(哈希)分区（HashPartitioning）,散列分区是指按照ORACLE所提供的散列（HASH）函数,计算列值数据，并最终按照函数结果将数据均匀的部署在不同的分区中。适用场合：不符合时间范围分区且需要均匀分布数据的场合。,4.4分区表(PartitionsTable),散列分区示例：创建一张产品编码表PRODUCT(product_id产品编号,description产品名称)，将产品编码的信息均匀的部署在两个不同的逻辑分区上，插入示例数据进行验证。操作：1）散列分区表的创建：OEM实现、SQL脚本实现。2）查看散列分区表的分区情况3）使用散列分区表,散列(哈希)分区（HashPartitioning）,创建散列分区表product,（1）OEM方式创建,PARTITIONBYHASH(column_name)(PARTITIONpart1TABLESPACEtbs1,PARTITIONpart2TABLESPACEtbs2,.PARTITIONpartNTABLESPACEtbsN);,散列(哈希)分区（HashPartitioning）,（2）命令方式创建,根据该列创建分区,分区的名称,将各个分区部署到指定的表空间,散列分区表product的创建：duct(product_idnumber(6),descriptionvarchar2(30)PARTITIONBYHASH(product_id)(PARTITIONp1tablespaceusers,PARTITIONp2tablespaceusers);,（1）通过OEM或者查询数据字典视图user_segments,查看分区段信息。SQLCOLsegment_nameformata10;SQLSELECTtablespace_name,segment_name,partition_nameFROMuser_segmentsWHEREsegment_name=PROCUDT;,（2）插入示例数据T11.sql，观察数据的分布。,散列(哈希)分区（HashPartitioning）,查看和使用散列分区表product,列表分区（listPartitioning）,列表分区是专门用于数据建模的离散值分布的一种分区方式，它可以将离散数据（如城市、地域）有效的部署到不同的分区中。适用场合：大型表中有一些关于城市、地域或类似的属性值且大部分应用基于不同的地域或城市来进行的情况下。,4.4分区表(PartitionsTable),列表分区示例：某公司（在全国北京、上海、重庆、广州、南京、武汉六个地区均有销售点）经常需要以地理位置统计销售数据，比如统计上海的销售数据，统计北京的销售数据等等。由于数据量大，公司建议将数据存储在不同的分区上，避免查找信息的时候扫描全表增加开销。根据以上要求为该公司建立销售数据表SALES_BY_REGION(deptno部门编号,dname部门名称,quantity_sales销售数量,city部门所在城市）。,列表分区（ListPartitioning）,创建散列分区表SALES_BY_REGION,（1）OEM方式创建,列表分区（ListPartitioning）,（2）命令方式创建,PARTITIONBYLIST(column_name)(PARTITIONpart1VALUES(values_list1),PARTITIONpart2VALUES(values_list2),.PARTITIONpartNVALUES(DEFAULT);,根据该列(离散值如地理位置)创建的列表分区,分区的名称,包含values_list1值的记录,列表分区sales_by_region的创建：createtablesales_by_region(deptnonumber,dnamevarchar2(20),quantity_salesnumber(10,2),cityvarchar2(10)PARTITIONBYLIST(city)(PARTITIONp1VALUES(北京,上海),PARTITIONp2VALUES(重庆,广州),PARTITIONp3VALUES(南京,武汉);,复合分区（CompositePartitioning）,组合范围-散列分区(CompositeRange-HashPartitioning)首先用范围分区对表进行分区,然后使用散列模式将每个分区再分区。该分区方式既具有范围分区良好的逻辑管理性,还提供了散列分区均匀分布数据的优势。,4.4分区表(PartitionsTable),某公司的销售单表sales_order包含销单编号order_id、销售日期order_date、产品编号procduct_id和数量quantity四个字段，但是该表按照逻辑范围分区后，不同范围的数据分布不均匀，试通过范围/散列（基于order_id）组合分区有效的部署销售单表的数据。,组合范围-散列分区(CompositeRange-HashPartitioning)示例,createtablesales_order(order_idnumber,order_datedate,product_idnumber,quantitynumber)PARTITIONBYRANGE(order_date)SUBPARTITIONBYHASH(order_id)SUBPARTITIONS2(PARTITIONp1VALUESLESSTHAN(01-APR-2001),PARTITIONp2VALUESLESSTHAN(01-JUN-2001),PARTITIONp3VALUESLESSTHAN(01-OCT-2001),PARTITIONp4VALUESLESSTHAN(01-JAN-2002);,创建的四个范围分区的名称,在表的order_date列中创建范围分区,在每个范围分区中创建2个散列子分区,复合分区（CompositePartitioning）,组合范围-列表分区(CompositeRange-HashPartitioning),组合范围-列表分区(CompositeRange-ListPartitioning)首先用范围分区对表进行分区,然后用列表分区的方法将每个分区再进行分区。用户既可以按照时间范围来访问数据（在分区内访问），也可以按照指定地理位置来访问数据（在子分区内），通过减少访问的数据量，来进一步提高访问的性能。,组合范围-列表分区(CompositeRange-HashPartitioning)示例,创建组合范围-列表分区表createtablesales_region(deptnonumber,sale_datedate,sale_amountnumber,cityvarchar2(10)PARTITIONBYRANGE(sale_date)SUBPARTITIONBYLIST(CITY)(PARTITIONr1_2003VALUESLESSTHAN(to_date(1-JUL-2003,DD-MON-YYYY)TABLESPACEusers(SUBPARTITIONr1_2003_1VALUES(北京,上海),SUBPARTITIONr1_2003_2VALUES(重庆,广州),SUBPARTITIONr1_2003_3VALUES(南京,武汉),PARTITIONr2_2003VALUESLESSTHAN(to_date(1-JAN-2004,DD-MON-YYYY)TABLESPACEusers(SUBPARTITIONr2_2003_1VALUES(北京,上海),SUBPARTITIONr2_2003_2VALUES(重庆,广州),SUBPARTITIONr2_2003_3VALUES(南京,武汉);,4.4分区表(PartitionsTable),4.4分区表(PartitionsTable),表分区为了简化数据库大表的管理，例如在数据仓库中一般都是TB级的数量级。Oracle8以后推出了分区选项，分区将表分离在若干不同的表空间上，用分而治之的方法来支撑无限膨胀的大表，提高大表在物理一级的可管理性。将大表分割成较小的分区可以改善表的维护、备份、恢复、事务及查询性能。,分区的优点1.增强可用性2.减少关闭时间3.维护轻松4.均衡I/O5.改善性能6.分区对用户透明,4.5索引组织表(IndexOrganizedTableIOT),叶子数据块中存储以排好序的索引主键以及记录对应的物理地址rowid，根据rowid定位记录。,4.5索引组织表(IndexOrganizedTableIOT),数据是按照主键顺序存放在叶子数据块中。找到主键也就找到完整的数据记录。,索引组织表拥有索引和表两者的特征。索引组织表是以B-树索引结构存储数据的表,它将表的数据（非主键列）和索引字段（主键列）一同存储在索引段中。因此找到记录的主键也就找到了相应记录的完整内容。索引组织表的缺点：数据块中的数据会随着记录的插入、删除等操作在数据块之间移动,因此而产生开销，适用于OLAP,不适用OLTP.,4.5索引组织表(IndexOrganizedTableIOT),建立索引组织表的关键建表的同时必须定义主键。,-创建索引组织表EMP_WORK，包括员工编号empnovarchar2(8),工作日期work_date两个字段，并检查是否创建成功。createtableemp_work(empnovarchar2(8),work_datedate,constraintpk_ewprimarykey(empno,work_date)organizationindex;-测试索引组织表物理存储的有序性t15.sql。,4.5索引组织表(IndexOrganizedTableIOT),例：假设每个员工都有34个（或者更多）的（地址）详细记录，但是这些详细记录是随机到来的。创建员工详细地址表包括员工编号empno,地址类型addr_type,街道street,城市city,州state,邮政编码zip，主键为(empno,addr_type)。要求:将同一员工的地址信息尽量存储在相同的或邻近的数据块上,以便在反复获取记录时,减少系统访问的工作量.,分析：由于员工的地址信息是随机到来的，那么同一员工的各个地址存放在同一个数据块或临近数据库块上概率基本接近于0，但是我们在执行SQL操作的时候又希望总是把所有地址详细记录都取出来。因此，建议对该地址表使用IOT表，每插入一个员工地址，都会根据索引主键将记录插入与该员工其他地址相互“靠近”的地方，这样在反复获取记录时，可以减少工作量。,将地址表实现为常规表，数据的具体存储,将地址表实现为索引组织表，数据的具体存储,4.5索引组织表(IndexOrganizedTableIOT),1、将EMP表设置为主表，EMPNO为其主关键字。将其子表（员工的详细地址表），用堆组织表和索引组织表的方式实现两次。创建EMP表并填充测试数据；将员工详细地址表创建为索引组织表iot_addresses；将员工详细地址表创建为堆组织表heap_addresses；2、分别向heap_addresses、iot_addresses表中插入大量示例数据。3、启用SQLTRACE工具查看以下两条SQL语句的CPU使用情况。select*fromemp,heap_addresseswhereemp.empno=heap_addresses.empnoandemp.empno=42;select*fromemp,iot_addresseswhereemp.empno=iot_addresses.empnoandemp.empno=42;,使用索引组织表可快速准确地进行匹配查找或根据主关键字在一定范围内的查询。一旦找到键值，在该位置中也找到其余数据。这样消除了频繁的I/O操作。最适合数据库24x7状态下的表结构，当数据库必须保持随时联机状态时，可联机重新组织IOT，无需重建其辅助索引。减少了存储需求。关键字列与表和索引并不重复，也不需要额外存储rowid。当关键字列占据了一行的大部分时，可节省存储空间。,4.5索引组织表(IndexOrganizedTableIOT),4.6对象表(ObjectTable),传统关系型仅支持简单数据类型；不支持数组、嵌套和递归的数据结构，给新型数据模型-面向对象的数据模型(OODBS)提供了应用背景。目前,以关系数据库和SQL为基础扩展关系模型以及对象关系数据库(ORDBMS)的发展满足了许多应用需求。ORACLE、DB2UDB、INFORMIX都推出ORDBMS,ANSI/ISO发布的SQL-99标准是一个对象关系标准。,例:createtypenamesex_typeasobject(namevarchar2(8),sexvarchar2(2);,一个对象类型由多个成员属性构成,ORACLE中对象类型由Createtype语句创建，也可在OEM中创建。createtype类型名asobject(字段1类型（长度），字段2类型（长度）;/,SQL*plus中创建对象类型必须有/定义了一个对象构造器。,4.6对象表(ObjectTable),4.6对象表(ObjectTable),createtableemployees(eidvarchar2(4),enamenamesex_type,jobvarchar2(6);,4.6对象表(O