ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 第 6 章 数据库表

第 6 章 数据库表

原创 Linux操作系统 作者:aikangyan 时间:2008-01-09 10:56:42 0 删除 编辑
6.1 表的类型

1.        堆组织表

2.        索引组织表

3.        聚簇表

4.        散列聚簇表

5.        嵌套表

6.        临时表

7.        对象表

一张表最多有1000列;表的行数理论上没有限制;表上索引个数可以是列的全排列数,而且一次性能够使用32个;表的数量没有限制。

6.2 术语

高水位标记 High Water Mark

    曾经包含数据的最右边的块。在全表扫描时,Oracle将扫描高水标记一下的所有块,即使它们不含数据。TRUNCATE将重新设置高水标记。

自由列表   Freelist

Oracle中用来跟踪高水标记以下有空闲空间的块对象。保留在高水标记以上的块,只有Freelist为空时才能被用到。

并行更新数据时,配置多个Freelist能提高整体性能,代价是增加了存储空间。

PCTFREEPCTUSED

INITIAL, NEXTPCTINCREASE

建议使用Local Managed表空间并设置Extents大小相等。而在没有使用Local Managed表空间的情况下,建议总是设置INITIAL=NEXTPCTINCREASE=0,以模拟Local Managed表空间的使用。

MINEXTENTSMAXEXTENTS

LOGGINGNOLOGGING

INITRANSMAXTRANS

 堆组织表

6.3 索引组织表

数据在IOT中根据主键存储和排序。IOT特别适用于IR(信息检索)、空间和OLAP应用程序。

IOT名义上是表,但它们的段实际上是索引段。要显示空间使用等就要先把IOT表的名字转换成潜在的索引名。默认值是SYS_IOT_TOP_object_id是为表分配的内部对象ID。推荐在建表时指定索引名。

主要应用

对只包含主键列的表:使用堆组织表将有100%多的额外开销;

1. 构建自己的索引结构:例如自己实现一个提供大小写不敏感查询的类似函数索引

CREATE TABLE emp AS SELECT * FORM. scott.emp;

 

CREATE TABLE upper_name

(x$ename,x$rid,

 PRIMARY KEY(x$ename,x$rid)

)

ORGANIZATION INDEX

AS

  SELECT UPPER(ename),ROWID FROM emp;

 

CREATE OR REPLACE TRIGGER upper_ename

AFTER INSERT OR UPDATE OR DELETE ON emp

FOR EACH ROW

BEGIN

  IF (UPDATING AND (:OLD.ename||'x'<>:NEW.ename||'x'))

  THEN

    DELETE FROM upper_name

    WHERE x$ename=UPPER(:OLD.ename)

      AND x$rid=:OLD.rowid;

     

    INSERT INTO upper_ename(x$ename,x$rid) VALUES (UPPER(:NEW.ename),:NEW.rowid);

  ELSIF (INSERTING)

  THEN

    INSERT INTO upper_ename(x$ename,x$rid) VALUES (UPPER(:NEW.ename),:NEW.rowid);

  ELSIF (DELETING)

  THEN

    DELETE FROM upper_name

    WHERE x$ename=UPPER(:OLD.ename)

      AND x$rid=:OLD.rowid;

  END IF;

END;

2. 需要加强数据的共同定位或希望数据按特定的顺序物理存储时

对应SybaseSQL Server用户,这种情况会采用聚簇索引,而这可能达到110%的额外开销,而IOT没有。经常用BETWEEN对主键或者唯一键进行查询,则会降低I/O数量。

主要选项

NOCOMPRESS/COMPRESS N

压缩N列,即对其中前N列相同的值进行压缩。从而能够允许更多数据进入Buffer Cache,代价是略多的CPU能量。

OVERFLOW PCTTHRESHOLD N/INCLUDING column_name

索引段的存储要密集于普通数据段(每块的行数要多),一般PCTUSED是没有意义的。而OVERFLOW子句允许设置另一个段以允许IOT中的行数据太大时溢出的这个段中。它再次引入PCTUSED,这样PCTUSEDPCTFREEOVERFLOW段有对于堆组织表中相同的含义。而使用方法是如下中的一种:

PCTTHRESHOLD——当行中数据超出此百分比,该行尾部的列溢出到溢出块;

INCLUDING——指定列之前的列均存入索引块,之后的列存入溢出块。

二次索引

只要主键是IOT,可以在索引中拥有索引。但不像其他一般索引,它不包含真正rowid(物理地址),而是基于主键IOT的逻辑rowid,作用稍小。对于IOT的二次索引访问实际有两个扫描执行(一般表只需一个扫描索引结构),一个在二次结构中,一个在IOT本身中。

6.4 索引聚簇表

Oracle中聚簇是存储一组表的方法,而不是如同SQL ServerSybase中那样(那是Oracle中的IOT)。概念上是通过聚簇码列将几张表预连接,尽可能将聚簇码列相同的几张表的行放入同一个块中。

CREATE CLUSTER emp_dept_cluster

(deptno NUMBER(2))

SIZE 1024;

 

CREATE INDEX emp_dept_cluster_idx

ON CLUSTER emp_dept_cluster;

 

CREATE TABLE dept

(deptno NUMBER(2) PRIMARY KEY,

 dname VARCHAR2(14),

 loc VARCHAR2(3)

)

CLUSTER emp_dept_cluster(deptno);

 

CREATE TABLE emp

(empno NUMBER PRIMARY KEY,

 ename VARCHAR2(10),

 ...

 deptno NUMBER(2) REFERENCES dept(deptno)

)

CLUSTER emp_dept_cluster(deptno);

 

BEGIN

  FOR x IN(SELECT * FROM scott.dept)

  LOOP

    INSERT INTO dept VALUES(x.deptno,x.dname,x.loc);

    INSERT INTO emp

      SELECT * FROM scott.emp

      WHERE deptno=x.deptno;

  END LOOP;

END;

注意这里的插入方法,这将尽可能保证每个块中放置尽可能多的聚簇码值,并让可以预连接的两个表中的值尽可能在同一个块中。

DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)可用于检查rowid所属块。

很容易发现deptemp有重复的rowid,表和rowid可以唯一确定行,rowid伪列只有在一张表中才是唯一的!

不使用聚簇的情况:

1.聚簇可能消极影响DML性能;

2.全扫描表的性能会受到影响——不仅仅扫描一个表,而是对多个表全扫描;

3.聚簇中的表不能TRUNCATE

 

6.5 散列聚簇表

概念类似索引聚簇表,但用散列函数代替了聚簇码索引。Oracle采用行的码值,使用内部函数或者自定义的函数进行散列运算,从而指定数据的存放位置。这样没有在表中增加传统的索引,因此不能Range Scan散列聚簇中的表,而只能全表扫描(除非单独建立索引)。

CREATE CLUSTER hash_cluster

(hash_key NUMBER)

HASHKEYS 1000

SIZE 8192;

索引聚簇需要空间时是动态分配,而散列聚簇表在创建时确定了散列码数(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. 作为物理存储机制,以持久地存储集合。

嵌套表语法

    创建嵌套表类型:

CREATE TABLE dept

(deptno     NUMBER(2) PRIMARY KEY,

 dname     VARCHAR2(14),

 loc         VARCHAR2(13)

);

 

CREATE TABLE emp

(empno       NUMBER(4) PRIMARY KEY,

 ename       VARCHAR2(10),

 job          VARCHAR2(9),

 mgr         NUMBER(4) REFERENCES emp,

 hiredate      DATE,

 sal           NUMBER(7, 2),

 comm        NUMBER(7, 2),

 deptno       NUMBER(2) REFERENCES dept

);

 

INSERT INTO dept SELECT * FROM scott.dept;

INSERT INTO emp SELECT * FROM scott.emp;

 

CREATE OR REPLACE TYPE emp_type

AS OBJECT

(empno       NUMBER(4),

 ename       VARCHAR2(10),

 job          VARCHAR2(9),

 mgr         NUMBER(4),

 hiredate      DATE,

 sal           NUMBER(7, 2),

 comm        NUMBER(7, 2)

);

 

CREATE OR REPLACE TYPE emp_tab_type

AS TABLE OF emp_type;

使用嵌套表:

CREATE TABLE dept_and_emp

(deptno     NUMBER(2) PRIMARY KEY,

 dname     VARCHAR2(14),

 loc         VARCHAR2(13),

 emps       emp_tab_type

)

NESTED TABLE emps STORE AS emps_nt;

可以在嵌套表上增加约束:

ALTER TABLE emps_nt ADD CONSTRAINT emps_empno_unique

           UNIQUE(empno) ;

嵌套表不支持参照完整性约束,不能参考任何其他表甚至自己:

ALTER TABLE emps_nt ADD CONSTRAINT mgr_fk

FOREIGN KEY(mgr) REFERENCES emps_nt(empno);

会产生错误ORA-30730

INSERT INTO dept_and_emp

SELECT dept.*,

   CAST( MULTISET( SELECT empno, ename, job, mgr, hiredate, sal, comm

                              FROM emp

                              WHERE emp.deptno = dept.deptno ) AS emp_tab_type )

  FROM dept;

MULTISET用来告诉Oracle子查询返回不止一行,CAST用来告诉Oracle将返回设置为一个集合类型。

查询时,嵌套表中的数据将在同一列中:

SELECT deptno, dname, loc, d.emps AS employees

FROM dept_and_emp d

WHERE deptno = 10;

Oracle同样提供方法去掉集合的嵌套,像关系型表一样处理(能够将EMPS列当作一个表,并自然连接且不需要连接条件):

SELECT d.deptno, d.dname, emp.*

FROM dept_and_emp D, TABLE(d.emps) emp;

按照每行实际是一张表的思想来更新:

UPDATE

  TABLE( SELECT emps

         FROM dept_and_emp

         WHERE deptno = 10

        )

SET comm = 100;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12379366/viewspace-120038/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-12

  • 博文量
    81
  • 访问量
    43372