第四章 ORACLE表管理.ppt

1、第四章 ORACLE数据库对象-表,.1 ORACLE表和数据类型,第四讲 Oracle数据表,.2 DUAL表,.3 常规表(堆表)(Normal Table),.5 索引组织表(Index Organized Table IOT),.6 对象表(Object Table),.7 嵌套表(Nested Table),.8 其他表,.4 分区表(Partitions Table),重点 掌握各种类型表的创建方法 了解常规表、分区表、索引组织表和对象表的物理组织形式及其特点 了解各种表的使用场合,.1 ORACLE表和数据类型,表是数据库数据存储的基本单元,它对应于显示世界中的对象(部门和雇员等

2、)。当进行数据库设计时,需要构造E-R图,在将E-R图转变为数据库对象时,实体最终要转换为数据库表。,.1.1 ORACLE表简介,表中存储的数据的逻辑结构是一张由行列组成的二维表。表中的一行又叫一条记录，或一个元组。一条记录描述一个实体;一列描述实体的属性，如部门有部门代码、部门名称、位置等属性，每个列还具有列名、数据类型、长度、约束条件、默认值等等。,ROWID是表的伪列， ROWID给出了表行的物理位置,用来唯一的标识表行。是定位表行最快的方式，它与其他列一样可以直接查询。ROWID在数据文件中其数据是掩码格式存放，所以一般用户不能直接读懂它的内容。而必须用包DBMS_ROWID进行转换

3、。,.1.2 ORACLE数据行的物理标识rowid,.1 ORACLE表和数据类型,select deptno,dname,rowid from scott.dept; select deptno,dname,rowid, dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block#, dbms_rowid.rowid_row_number(rowid) row# from scott.dept;,为DEPT表的DEPTNO建立索引,分析数据库是如何存储索引信息的?如何利用索引来

4、加快查找速度的?,形成索引列表,Select * from dept Where deptno=20,字符型,.1.3 ORACLE基本数据类型(PAGE40),.1 ORACLE表和数据类型,注解：因为char类型总是会用空格填充使之达到一个固定宽度，无论在是表段中还是索引段中，它都会占用很大的存储空间。,数值型,.1.3 ORACLE基本数据类型,为数值型分配存储空间的公式: Round(length(number)+s)/2)+1 当number=0 s=0; 当number0 s=1,.1 ORACLE表和数据类型,注解：number可以很精确的存储数值 ;如果对科学数据执行数据挖掘或

5、进行复杂的数值分析，精度损失往往是可以接受的，使用binary_float、binary_double可能会得到非常显著的性能提升。,.1 ORACLE表和数据类型,日期时间型,.1.3 ORACLE基本数据类型,注解:date类型世纪和年份采用一种“加100”表示法来存储。月和日采用自然的存储方式。时、分、秒采用“加1”表示法存储。,.1 ORACLE表和数据类型,其他数据型,.1.3 ORACLE基本数据类型,.2 DUAL表,DUAL表属于SYS模式,它在数据字典创建时自动建立。DUAL表只有一列和一行，列名为dummy。由于ORACLE中大部分操作都必须在表中，如对一个代数表达式求值，

6、可以在该表上使用SELECT命令计算一个常量表达式。 DUAL表在此充当了那些表达式的包罗万象的容器。,.3常规表(Normal Table),.3常规表(Normal Table),常规表又叫做堆组织表（heap）,它是一个无序行集合，并不是以某种特定的顺序来存储数据。 堆是计算机领域中深入研究的一种经典的数据结构，它的特点是会将数据放在最合适的地方，而不是按照某种特定顺序来放置。,Example:建立一个测试表t，数据库中的每个数据块刚好 能放一个整行（数据库块是8KB),向表中插入多条记录, 验证记录存储的无序性。,根据数据需求分析设计数据库表的模式,应该使用范式来规划每个表。 定义表列

7、时，应选择合适的数据类型和长度。 确定表中所需要的完整性约束。 规划表的位置，将表部署在特定的表空间上(USERS表空间)。 预计和规划表的大小。在oracle10G中，通过OEM Database control估算表大小。 用户与权限问题。(CREATE ANY TABLE、 CREATE TABLE、UNLIMITED TABLESPACE),.3常规表(Normal Table),.3常规表(Normal Table),report_card,学生成绩管理数据库,dept,course,student,实 例：学生成绩管理项目数据库设计,.3常规表(Normal Table),使用Cr

8、eate Table脚本在SQL Plus环境中实现。 使用OEM控制台通过图形界面创建表结构。 在原来已有表的基础上建立新表。,.3常规表(Normal Table),使用Create Table脚本创建常规表,Create table schema.table_name (列名1 类型（长度） 列约束 , 列名2 类型（长度） 列约束,表约束) Pctfree n Pctused n Initrans n Maxtrans n Tablespace tablespace_name Storage ( Initial n Next n Pctincrease n Minextents n M

9、axextents n),Create table test(a number)storage( initial 100K next 100K minextents 2 maxextents 100 pctincrease 100);,.3常规表(Normal Table),示例 : 以新用户zhang登录,在方案zhang下创建学生成绩 管理项目四个表。,zhang,CREATE TABLE ZHANG.dept (DEPT_NO varchar2(8), DEPT_NAME varchar2(40), DEPT_MNG varchar2(8), ADDRESS varchar2(80),

10、CONT number, PRIMARY KEY (DEPT_NO) ) pctfree 20 pctused 50 tablespace users storage( initial 100k next 100k minextents 2 maxextents 100 pctincrease 0) ;,使用OEM创建常规表,.3常规表(Normal Table),示例 :创建学生基本信息表student,包含student_no等15个字段。其中 student_no为主键；student_name非空；dept_no参照与表dept中的dept_no 的取值; type取值仅为1、2、3；

11、sex取值只能为0或1。 该表的表空间为users，保留用于更新的空闲空间的百分比为10%，并 发事务个数初始值为2个，最多并发255个事务。 为表分配的区最少为2个，最多为20个，第一个区大小为64K，第一个 扩展区的大小为64K，随着数据量的增加新分配的区相对于上一个区增长 50%.,通过Create table.as select. 语句(Create table嵌套子查询,简称CTAS) ，可以基于原有的表或视图来创建新表，而不必逐个定义列。当新表与原表结构相似或者新表需要使用原表中大量数据时很有必要采用这种方式。,.3常规表(Normal Table),CTAS方法建立常规表,语法一

12、: create table NOLOGGING as select from table_old where condition;,语法二: create table NOLOGGING as select from table_old where 1=2;,建议使用NOLOGGING选项。如果不使用这个选项，则每插入一条记录都将会产生重做日志信息，占用了空间和时间。,注意： 不能修改列的数据类型和长度，新表中的数据类型和长度都必须和查询中的一致。 SELECT语句中不能包含大对象数据类型和LONG数据类型。 约束性条件和列的默认值定义都不会被复制。 建议使用NOLOGGING选项。如果不使

13、用这个选项，则每插入一条记录都将会产生重做日志信息，占用了空间和时间。,.3常规表(Normal Table),CTAS方法建立常规表,.3常规表(Normal Table),可通过数据字典dba_tables、all_tables、user_tables查看表的定义信息 如： SELECT owner,tablespace_name, pct_free,pct_used, initial_extent,next_extent,min_extents,max_extents FROM dba_tables WHERE table_name=STUDENT; 可通过查询数据字典dba_tab_c

14、olumns、dba_constraints查看表的字段信息与约束信息。,通过SQL命令修改表的语法格式如下所示： ALTER TABLE schema. table_name RENAME TO NewTablename ADD col_name datatype DEFAULT expression col_constraint MODIFY col_name datatype DEFAULT expression col_constraint DROP COLUMN col_name (col_name1,col_name2) SET UNUSED COLUMN col_name DRO

15、P UNUSED COLUMN STORAGE 子句;,使用rename to 子句修改表名,使用ADD、MODIFY和DROP子句来修改列定义,使用STORAGE子句来修改存储参数及区分配参数,删除表的结构: Drop table schema.tablename cascade constraints purge 备注:不仅会删除了表中的数据,还会删除表结构。这条命令是不能回退的，所以，删除表的时候应格外小心。 恢复被删除表： FLASHBACK TABLE schema.tablename TO BEFORE DROP,4.4 分区表(Partitions Table),访问一季度数据,

16、4.4 分区表(Partitions Table),假设一张销售表SALES年数据总量达到10G，每个季度平均2.5G。现在执 行一条SQL语句访问SALES表中的第一季度销售总额。这时候服务器进程会对 SALES表进行全表扫描。如果我们使用分区表把四个季度的数据分别存放到 不同的分区中，当统计某一季度销售数据只需要扫描2.5G数据，提高I/O性能。,2.5G,-oracle允许用户将一个表分成多个分区 用户可以只访问表中的特定分区，避免全表扫描，降低磁盘I/O,提高访问性能 将不同的分区存储在不同的磁盘，均衡分布I/O，提高访问性能 可以独立地备份和恢复每个分区，提高系统健壮性、可靠性及可用

17、性,4.4 分区表(Partitions Table),分区是一种方法，它将一个大表从逻辑上根据某些条 件把数据分成若干个较小的且更容易管理的区-分区段(表)。 分区表是一种特殊的常规表。,按照分区的方式可将分区表分为： 范围分区（Range Partitioning） 散列分区（Hash Partitioning） 列表分区（List Partitioning） 复合分区( Composite Partitioning) -复合范围-散列分区( Composite Range-Hash Partitioning) -复合范围-列表分区( Composite Range-List Partit

18、ioning),4.4 分区表(Partitions Table),范围分区 （Range Partitioning）,根据表中某一列值的范围(通 常是基于时间的范围)，将表中的 行映射到各个分区。 适用场合: 当数据量较大且跨时间范围 均匀分布数据时，非常适合于创 建范围分区表，这时的表性能是 最佳的。,范围分区示例： 假设一张销售表SALES（customer_id销售单号、 sales_amount销售额、sales_date销售日期）年数据总量达到 10G，每个季度平均2.5G。创建范围分区表，将一、二、三、 四季度的销售数据存放到不同分区段（p1,p2,p3,p4）中。 操作： 1）

19、范围分区表的创建：OEM实现、SQL脚本实现。 2）查看范围分区表的分区情况 3）使用范围分区表,范围分区 （Range Partitioning）,SALES范围分区表的创建： CREATE TABLE sales(customer_id number(3), sales_amount number(10,2) , sales_date DATE) PARTITION BY RANGE(sales_date) ( PARTITION p1 VALUES LESS THAN (01-APR-2003) TABLESPACE users, PARTITION p2 VALUES LESS THA

20、N (01-JUL-2003) TABLESPACE users, PARTITION p3 VALUES LESS THAN (01-OCT-2003) TABLESPACE users, PARTITION p4 VALUES LESS THAN (01-JAN-2004) TABLESPACE users );,步骤一：创建分区表SALES,（1）OEM方式创建,（2）命令方式创建,范围分区的语法： PARTITION BY RANGE (column_name) ( PARTITION part1 VALUE LESS THAN(range1) TABLESPACE tablespac

21、e_name, PARTITION part2 VALUE LESS THAN(range2), TABLESPACE tablespace_name, . PARTITION partN VALUE LESSTHAN(MAXVALUE) );,根据该列创建分区,分区的名称,该分区包含低于range1值 的数据信息，并将该分区部署到相应的表空间,范围分区 （Range Partitioning）,范围分区表SALES的创建： create table zhang.sales(customer_id number(3), sales_amount number(10,2), sales_date

22、 DATE) PARTITION BY RANGE(sales_date) ( PARTITION p1 VALUES LESS THAN (01-APR-2003), PARTITION p2 VALUES LESS THAN (01-JUL-2003), PARTITION p3 VALUES LESS THAN (01-OCT-2003), PARTITION p4 VALUES LESS THAN (01-JAN-2004);,SQL脚本创建sales表：,步骤二：查看和使用分区表SALES,（1）通过OEM或者查询数据字典视图user_segments, 查看分 区段信息。 SQL

23、COL segment_name format a10; SQL SELECT tablespace_name,segment_name,partition_name FROM user_segments WHERE segment_name=SALES;,（2）当在分区表上执行DML操作时，ORACLE会根据分区列值的 范围在相应的分区上执行操作(T10.SQL) SQL insert into sales values(1,28500,25-JAN-2003); SQL select * from sales -全表扫描 where sales_date=to_date(25-JAN-20

24、03,dd-mon-yyyy); SQLselect * from sales partition(p1) - 分区扫描 where sales_date=to_date(25-JAN-2003,dd-mon-yyyy);,范围分区 （Range Partitioning）,散列(哈希)分区（Hash Partitioning）,散列分区是指按照ORACLE所提 供的散列（HASH）函数,计算列值数 据，并最终按照函数结果将数据均匀 的部署在不同的分区中。 适用场合： 不符合时间范围分区且需要均匀 分布数据的场合。,4.4 分区表(Partitions Table),散列分区示例： 创建一张产

25、品编码表PRODUCT(product_id产品编号, description 产品名称)，将产品编码的信息均匀的部署在两个不 同的逻辑分区上，插入示例数据进行验证。 操作： 1）散列分区表的创建：OEM实现、SQL脚本实现。 2）查看散列分区表的分区情况 3）使用散列分区表,散列(哈希)分区（Hash Partitioning）,创建散列分区表product,（1）OEM方式创建,PARTITION BY HASH (column_name) ( PARTITION part1 TABLESPACE tbs1, PARTITION part2 TABLESPACE tbs2, . PARTI

26、TION partN TABLESPACE tbsN );,散列(哈希)分区（Hash Partitioning）,（2）命令方式创建,根据该列创建分区,分区的名称,将各个分区部署到指定的表空间,散列分区表product的创建： create table duct (product_id number(6), description varchar2(30) PARTITION BY HASH(product_id) ( PARTITION p1 tablespace users, PARTITION p2 tablespace users );,（1）通过OEM或者查询数据

27、字典视图user_segments, 查看分 区段信息。 SQL COL segment_name format a10; SQL SELECT tablespace_name,segment_name,partition_name FROM user_segments WHERE segment_name=PROCUDT;,（2）插入示例数据T11.sql，观察数据的分布。,散列(哈希)分区（Hash Partitioning）,查看和使用散列分区表product,列表分区（list Partitioning）,列表分区是专门用于数据建模的 离散值分布的一种分区方式，它可以 将离散数据（如城

28、市、地域）有效的 部署到不同的分区中。 适用场合： 大型表中有一些关于城市、地域 或类似的属性值且大部分应用基于不 同的地域或城市来进行的情况下。,4.4 分区表(Partitions Table),列表分区示例： 某公司（在全国北京、上海、重庆、广州、南京、武汉六 个地区均有销售点）经常需要以地理位置统计销售数据，比如 统计上海的销售数据，统计北京的销售数据等等。由于数据量 大，公司建议将数据存储在不同的分区上，避免查找信息的时 候扫描全表增加开销。根据以上要求为该公司建立销售数据表 SALES_BY_REGION (deptno 部门编号,dname部门名称, quantity_sales

29、 销售数量,city部门所在城市）。,列表分区（List Partitioning）,创建散列分区表SALES_BY_REGION,（1）OEM方式创建,列表分区（ List Partitioning）,（2）命令方式创建,PARTITION BY LIST (column_name) ( PARTITION part1 VALUES (values_list1), PARTITION part2 VALUES (values_list2), . PARTITION partN VALUES (DEFAULT) );,根据该列(离散值如地理位置)创建的列表分区,分区的名称,包含values_l

30、ist1值的记录,列表分区sales_by_region的创建： create table sales_by_region (deptno number, dname varchar2(20), quantity_sales number(10,2), city varchar2(10) ) PARTITION BY LIST (city) ( PARTITION p1 VALUES(北京,上海), PARTITION p2 VALUES(重庆,广州), PARTITION p3 VALUES(南京,武汉) );,复合分区（ Composite Partitioning ）,组合范围-散列分区

31、 ( Composite Range-Hash Partitioning) 首先用范围分区对表进行分区,然 后使用散列模式将每个分区再分区。 该分区方式既具有范围分区良好的逻辑 管理性,还提供了散列分区均匀分布数据 的优势。,4.4 分区表(Partitions Table),某公司的销售单表sales_order包含销单编号order_id、销售日 期order_date、产品编号procduct_id和数量quantity四个字段，但 是该表按照逻辑范围分区后，不同范围的数据分布不均匀，试 通过范围/散列（基于order_id）组合分区有效的部署销售单表的 数据。,组合范围-散列分区( C

32、omposite Range-Hash Partitioning)示例,create table sales_order(order_id number,order_date date, product_id number,quantity number) PARTITION BY RANGE (order_date) SUBPARTITION BY HASH(order_id) SUBPARTITIONS 2 ( PARTITION p1 VALUES LESS THAN(01-APR-2001), PARTITION p2 VALUES LESS THAN(01-JUN-2001), PA

33、RTITION p3 VALUES LESS THAN(01-OCT-2001), PARTITION p4 VALUES LESS THAN(01-JAN-2002);,创建的四个范围分区的名称,在表的 order_date 列中创建范围分区,在每个范围分区中 创建 2 个散列子分区,复合分区（ Composite Partitioning ）,组合范围-列表分区( Composite Range-Hash Partitioning),组合范围-列表分区( Composite Range-List Partitioning) 首先用范围分区对表进行分区,然后用列表分区的方 法将每个分区再进行

34、分区。用户既可以按照时间范围来访 问数据（在分区内访问） ，也可以按照指定地理位置来 访问数据（在子分区内） ，通过减少访问的数据量，来 进一步提高访问的性能。,组合范围-列表分区( Composite Range-Hash Partitioning)示例,创建组合范围-列表分区表 create table sales_region ( deptno number,sale_date date, sale_amount number,city varchar2(10) ) PARTITION BY RANGE (sale_date) SUBPARTITION BY LIST (CITY) (

35、PARTITION r1_2003 VALUES LESS THAN (to_date(1-JUL-2003,DD-MON-YYYY) TABLESPACE users( SUBPARTITION r1_2003_1 VALUES(北京,上海), SUBPARTITION r1_2003_2 VALUES(重庆,广州), SUBPARTITION r1_2003_3 VALUES(南京,武汉), PARTITION r2_2003 VALUES LESS THAN (to_date(1-JAN-2004,DD-MON-YYYY) TABLESPACE users( SUBPARTITION r

36、2_2003_1 VALUES(北京,上海), SUBPARTITION r2_2003_2 VALUES(重庆,广州), SUBPARTITION r2_2003_3 VALUES(南京,武汉) );,4.4 分区表(Partitions Table),4.4 分区表(Partitions Table),表分区 为了简化数据库大表的管理，例如在数据仓库中一般都是TB 级的数量级。Oracle 8以后推出了分区选项，分区将表分离在若干不同的表空间上，用分而治之的方法来支撑无限膨胀的大表，提高大表在物理一级的可管理性。将大表分割成较小的分区可以改善表的维护、备份、恢复、事务及查询性能。,分区的优

37、点 1. 增强可用性 2. 减少关闭时间 3. 维护轻松 4. 均衡I/O 5. 改善性能 6. 分区对用户透明,4.5 索引组织表(Index Organized Table IOT),叶子数据块中存储以排好序的 索引主键以及记录对应的物理地 址rowid，根据rowid定位记录。,4.5 索引组织表(Index Organized Table IOT),数据是按照主键顺序存放 在叶子数据块中。找到主键 也就找到完整的数据记录。,索引组织表拥有索引和表两者的特征。索引组织表是以B-树索引结构存储数据的表, 它将表的数据（非主键列）和索引字段（主键列）一同存储在索引段中。因此找到记录的主键也就

38、找到了相应记录的完整内容。 索引组织表的缺点：数据块中的数据会随着记录的插入、删除等操作在数据块之间移动,因此而产生开销，适用于OLAP,不适用OLTP.,4.5 索引组织表(Index Organized Table IOT),建立索引组织表的关键 建表的同时必须定义主键。,-创建索引组织表EMP_WORK，包括员工编号empno varchar2(8), 工作日期work_date两个字段，并检查是否创建成功。 create table emp_work (empno varchar2(8), work_date date, constraint pk_ew primary key(emp

39、no,work_date) organization index; -测试索引组织表物理存储的有序性t15.sql。,4.5 索引组织表(Index Organized Table IOT),例： 假设每个员工都有34个（或者更多）的（地址）详细记录，但是这些详细记录是随机到来的。创建员工详细地址表包括员工编号empno,地址类型addr_type,街道street,城市city,州state,邮政编码zip，主键为(empno,addr_type)。要求:将同一员工的地址信息尽量存储在相同的或邻近的数据块上,以便在反复获取记录时,减少系统访问的工作量.,分析：由于员工的地址信息是随机到来的，

40、那么同一员工的各个地址存放在同一个数据块或临近数据库块上概率基本接近于0，但是我们在执行SQL操作的时候又希望总是把所有地址详细记录都取出来。因此，建议对该地址表使用IOT表，每插入一个员工地址，都会根据索引主键将记录插入与该员工其他地址相互“靠近”的地方，这样在反复获取记录时，可以减少工作量。,将地址表实现为常规表，数据的具体存储,将地址表实现为索引组织表，数据的具体存储,4.5 索引组织表(Index Organized Table IOT),1、将EMP表设置为主表，EMPNO为其主关键字。将其子表（员工的详细地址表），用堆组织表和索引组织表的方式实现两次。 创建EMP表并填充测试数据；

41、 将员工详细地址表创建为索引组织表iot_addresses； 将员工详细地址表创建为堆组织表heap_addresses； 2、分别向heap_addresses、iot_addresses表中插入大量示例数据。 3、启用SQL TRACE工具查看以下两条SQL语句的CPU使用情况。 select * from emp,heap_addresses where emp.empno=heap_addresses.empno and emp.empno=42; select * from emp,iot_addresses where emp.empno=iot_addresses.empno and emp.empno=42;, 使用索引组织表可快速准确地进行匹配查找或根据主关键字在一定范围内的查询。一旦找到键值，在该位置中也找到其余数据。这样消除了频繁的I/O操作。 最适合数据库24x7状态下的表结构，当数据库必须保持随时联机状态时，可联机重新组织IOT，无需重建其辅助索引。 减少了存储需求。关键字列与表和索引并不重复，也不需要额外存储rowid。当关键字列占据了一行的大部分时，可节省存储空间。,4.5 索引组织表(Index Organ