数据库表(二)

最新推荐文章于 2021-04-12 21:30:40 发布

ciqmm402244

最新推荐文章于 2021-04-12 21:30:40 发布

阅读量205

点赞数

1. 索引聚簇表

² 聚簇（cluster）是指：如果一组表有一些共同的列，则将这样一组表存储在相同的数据库块中；聚簇还表示把相关的数据存储在同一个块上。聚簇并不是有序地存储数据（这是IOT的工作），它是按每个键以聚簇方式存储数据，但数据存储在堆中。

² 如何创建聚簇对象

1）对象的存储定义（PCTFREE、PCTUSED、INITIAL）与CLUSTER相关，而不是与表相关，因为聚簇中会有多个表，而且它们在同一个块上，有多个不同的PCTFREE是没有意义的。

2）创建语法：

SQL> create cluster emp_dept_cluster

2 (deptno number(2))

3 size 1024

4 /

簇已创建。

3） SIZE选项：该选项告诉Oracle：我们希望与每个聚簇键值关联大约1024字节的数据，Oracle根据SIZE控制每块上聚簇键的最大个数。

² 聚簇索引

1）向聚簇中放数据之前，需要先对聚簇建立索引。聚簇索引的任务是拿到一个聚簇键值，然后返回包含这个键的块的块地址。实际上这是一个主键，其中每个聚簇键值指向聚簇本身中的一个块。

2）聚簇索引创建语法:

SQL> create index emp_dept_cluster_idx

2 on cluster emp_dept_cluster

3 /

索引已创建。

² 聚簇中创建表

1）创建语法：

SQL> create table dept

2 (deptno number(2) primary key,

3 dname varchar2(14),

4 loc varchar2(13)

5 )

6 cluster emp_dept_cluster(deptno)

7 /

表已创建。

2）与“正常”表惟一的区别是，使用了CLUSTER关键字，并告诉Oracle基表的哪个列会映射到聚簇本身的聚簇键。要记住，这里的段是聚簇，因此基表不会有诸如TABLESPACE、PCTFREE等段属性，它们都是聚簇段的属性，而不是我们所创建的表的属性。

3）在给聚簇进行大批量初始加载时，应同时加载对应一个给定聚簇键的所有数据，以便尽可能紧地塞满块，等空间用完时再开始一个新块。否则，在聚簇键块上会导致过度的串链。

² Rowid不能惟一地标识数据库中的一行，譬如：

SQL> select rowid from emp

2 intersect

3 select rowid from dept;

ROWID

------------------

AAARiMAAEAAAAVsAAA

AAARiMAAEAAAAVtAAA

AAARiMAAEAAAAVwAAA

AAARiMAAEAAAAVwAAB

注意：要由表和行ID共同地惟一标识一行。ROWID伪列只是在一个表中惟一。

² 不应该使用聚簇的应用：

1）如果预料到聚簇中的表会大量修改；

2）如果需要对聚簇中的表执行全表扫描；

3）如果你认为需要频繁地TRUNCATE和加载表：聚簇中的表不能截除，必须删除聚簇表中的行。

² 使用聚簇的应用：

如果数据主要用于读，而且要通过索引来读（可以是聚簇键索引，也可以是聚簇表上的其他索引），另外会频繁地把这些信息联结在一起，此时聚簇就很适应。

2. 散列聚簇表（Hash clustered table）

² 与索引聚簇表的主要区别：

1）聚簇键索引被一个散列函数所取代。表中的数据就是索引，这里没有物理索引。Oracle会取得一行的键值，使用每个内部函数或你提供的每个函数对其计算散列，然后使用这个散列值得出数据应该在磁盘上的哪个位置。

2）使用散列算法来定位数据有一个副作用，如果不向表增加一个传统的索引，将无法对散列聚簇中的表完成区间扫描。譬如，在一个聚簇索引中，如果有以下查询：

Select * from emp where deptno between 10 and 20;

如果是索引聚簇表，则能利用聚簇键索引来找到这些行；但是，如果是散列聚簇表，则会导致一个全表扫描，除非DEPTNO列上已经有一个索引。

² 创建散列聚簇

1）语法：与创建索引聚簇类似，但多了一个HASHKEYS选项

SQL> create cluster hash_cluster

2 (hash_key number)

3 hashkeys 1000

4 size 8192

5 tablespace mssm

6 /

簇已创建。

2） HASHKEYS选项：指定散列表的大小，Oracle将其舍入为与之最接近的质数，然后Oracle再将SIZE参数乘以修改后的HASHKEYS值，计算出一个值，再根据这个值为聚簇分配空间。

3）注意：一般地，如果创建一个空表，该表在HWM下的块数为0.如果对它执行全表扫描，达到HWM就会停止；对于一个散列聚簇，表一开始就很大，需要花更长的时间创建，因为Oracle必须初始化各个块（而对于一般的表，这个动作通常在数据增加到表时才发生）。对于一个几乎为空的散列聚簇进行前面扫描与全面扫描一个满的散列聚簇所花的时间是一样的。建立散列聚簇的本来目的是为了根据散列键值查找从而非常快地访问数据，而不是为了频繁地对它进行全面扫描。

² 散列聚簇与索引表的查询性能比较：

1）散列聚簇完成的I/O少得多。查询只是取被散列键值，对其完成散列，然后找到块，散列聚簇要做一次I/O来得到数据；有索引的传统表则必须得到索引的根块，找出包含该行位置的叶子快，接下来必须得到叶子块信息，其中包含行ROWID，再利用第3个I/O在表中访问这个行，因此，索引表必须至少完成3个I/O才能得到数据。

2）不论用于什么目的，散列聚簇查询与索引查询所用的CPU是一样的。因为执行散列是一个CPU相当密集的操作，而执行索引查询则是一个I/O密集的操作。

² 单表散列聚簇（single table hash cluster）

1）它是散列聚簇的一个特例，一次只支持聚簇中的一个表（必须DROP单表散列聚簇中现有的表，才能在其中创建另一个表）。如果散列键和数据行之间存在一对一的映射，访问行会更快一些。

2）应用目的：如果你想按主键来访问一个表，但是不关心其他表是否与这个表聚簇在一起存储。

3）创建语法：

SQL> create cluster hash_cluster

2 (hash_key number(10))

3 hashkeys 75000

4 size 150

5 single table

6 hash is HASH_KEY

7 /

簇已创建。

² 小结：

1）散列聚簇一开始就要分配空间。Oracle根据你的HASHKEYS和SIZE来计算HASHKEYS/trunc(blocksize/SIZE)，立即分配相应的块数，并完成格式化，一旦将第一个表放入这个聚簇中，任何全面扫描都会命中每一个已分配的块。在这方面，它与其他的所有表都不同。

2）散列聚簇中的HASHKEYS数是固定大小的。除非重新建簇，否则不能改变散列表的大小。这并不会限制聚簇中能存储的数据量，它只是限制了能为这个聚簇生成惟一散列键的个数。

3）不能在聚簇键上完成区间扫描。如果你在一个聚簇键上使用区间扫描，而且没有使用传统索引，实际上会全面扫描这个链表。

4）散列聚簇适用与以下情况：

Ø 你很清楚表中会有多少行，或者你知道一个合理的上界。HASHKEYS和SIZE参数的大小要正确，这对于避免聚簇重建直关重要。

Ø 与获取操作相比，DML（特别是插入）很轻。

Ø 经常按HASHKEY值访问数据。例如查找表。

3. 有序散列聚簇表

² 要按某个键获取数据，但要求这些数据按另外某个列排序，则适合使用有序散列聚簇，Oracle可以返回数据而根本不用执行排序。

Select *

From t

Where key = :x

Order by sorted_column;

² 创建有序散列聚簇

1）语法：

SQL> create cluster shc

2 (cust_id NUMBER,

3 order_dt timestamp SORT

4 )

5 HASHKEYS 10000

6 HASH IS cust_id

7 SIZE 8192

8 /

簇已创建。

说明：引入了新的关键字：SORT。并表示了HASH IS CUST_ID。这说明：数据将按CUST_ID查找，而按ORDER_DT物理地获取和排序。

2）在聚簇中创建表

SQL> create table cust_orders

2 (cust_id number,

3 order_dt timestamp SORT,

4 order_number number,

5 username varchar2(30),

6 ship_addr number,

7 bill_addr number,

8 invoice_num number

9 )

10 CLUSTER shc (cust_id,order_dt)

11 /

表已创建。

² 使用该结构时，应考虑散列聚簇同样的问题。另外还要考虑一个约束条件，即数据应该按键值的有序顺序到达，否则这个数据结构很快会受不了，因为必须移动大量的数据使得这些行在磁盘上物理有序。

4. 嵌套表

² 嵌套表语法

1）创建：

SQL> create or replace type emp_type

2 as object

3 (empno number(4),

4 ename varchar2(10),

5 job varchar2(9),

6 mgr number(4),

7 hiredate date,

8 sal number(7,2),

9 comm number(7,2)

10 );

11 /

类型已创建。

SQL> create or replace type emp_tab_type

2 as table of emp_type

3 /

类型已创建。

说明：要创建一个带嵌套表的表，需要有一个嵌套表类型。以上代码创建了一个复杂的对象类型EMP_TYPE，并创建了它的一个嵌套表类型EMP_TAB_TYPE。以下简单的CREATE TABLE语句使用了这个类型：

SQL> create table dept_and_emp

2 (deptno number(2) primary key,

3 dname varchar2(14),

4 loc varchar2(13),

5 emps emp_tab_type

6 )

7 nested table emps store as emps_nt;

表已创建。

SQL> alter table emps_nt add constraint

2 emps_empno_unique unique(empno)

3 /

表已更改。

注意：嵌套表不支持引用完整性约束，因为它们不能引用任何表，甚至它们自己。

2）填充

SQL> insert into dept_and_emp

2 select dept.*,

3 CAST(multiset(select empno,ename,job,mgr,hiredate,sal,comm

4 from SCOTT.EMP

5 where emp.deptno = dept.deptno) AS emp_tab_type)

6 from SCOTT.DEPT

7 /

已创建4行。

说明：MULTISET关键字告诉Oracle：这个子查询返回多行。

3）查询

Ø 查询一行

SQL> select deptno,dname,loc,d.emps AS employees

2 from dept_and_emp d

3 where deptno = 10

4 /

DEPTNO DNAME LOC

---------- -------------- -------------

EMPLOYEES(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM)

--------------------------------------------------------------------------------

10 ACCOUNTING NEW YORK

EMP_TAB_TYPE(EMP_TYPE(7782, 'CLARK', 'MANAGER', 7839, '09-6月 -81', 2450, NULL), EMP_TYPE(7839, 'KING', 'PRESIDENT', NULL, '17-11月-81', 5000, NULL), EMP_TYPE(7934, 'MILLER', 'CLERK', 7782, '23-1月 -82', 1300, NULL))

可以看到，现有数据都放在一个列中。

Ø 取消集合的嵌套，把它当成一个关系表处理

SQL> select d.deptno,d.dname,emp.*

2 from dept_and_emp d,table(d.emps) emp

3 /

DEPTNO DNAME EMPNO ENAME JOB MGR HIREDATE

SAL COMM

---------- -------------- ---------- ---------- --------- ---------- -----------

--- ---------- ----------

10 ACCOUNTING 7782 CLARK MANAGER 7839 09-6月 -81

2450

10 ACCOUNTING 7839 KING PRESIDENT 17-11月-81

5000

10 ACCOUNTING 7934 MILLER CLERK 7782 23-1月 -82

1300

20 RESEARCH 7369 SMITH CLERK 7902 17-12月-80

800

20 RESEARCH 7566 JONES MANAGER 7839 02-4月 -81

2975

20 RESEARCH 7788 SCOTT ANALYST 7566 19-4月 -87

3000

20 RESEARCH 7876 ADAMS CLERK 7788 23-5月 -87

1100

20 RESEARCH 7902 FORD ANALYST 7566 03-12月-81

3000

30 SALES 7499 ALLEN SALESMAN 7698 20-2月 -81

1600 300

30 SALES 7521 WARD SALESMAN 7698 22-2月 -81

1250 500

30 SALES 7654 MARTIN SALESMAN 7698 28-9月 -81

1250 1400

30 SALES 7698 BLAKE MANAGER 7839 01-5月 -81

2850

30 SALES 7844 TURNER SALESMAN 7698 08-9月 -81

1500 0

30 SALES 7900 JAMES CLERK 7698 03-12月-81

950

已选择14行。

4）更新

SQL> update

2 table(select emps

3 from dept_and_emp

4 where deptno = 10

5 )

6 set comm = 100

7 /

已更新3行。

注意：这说明“每一行都有一个虚拟表”。每次只能更新DEPT_AND_EMP表中的一行，更新零行或多行都会导致错误，因为在嵌套表模型中，每个父行有一个表。

5）插入

SQL> insert into table

2 (select emps from dept_and_emp where deptno = 10)

3 values

4 (1234,'NewEmp','CLERK',7782,sysdate,1200,null);

已创建 1 行。

6）删除

SQL> delete from table

2 (select emps from dept_and_emp where deptno = 20)

3 where ename = 'SCOTT';

已删除 1 行。

² 嵌套表与父/子关系表之间的语义差别：

1) 在嵌套表模型中，每个父行有一个表。而关系模型中，每个父行有一个行集。这种差别使用嵌套表有时使用起来有些麻烦。考虑我们使用的这个模型，它从单个部门的角度提供了一个很好的数据视图。如果我们想问“KIND 为哪个部门工作？”“有多少在职的会计”等待，这个模型就很糟糕了。这些问题最好去问EMP 关系表，而在嵌套表模型中，我们只能通过DEPT 数据来访问EMP 数据。总是必须联结，而无法单独地查询EMP 数据。在这方面，Oracle 没有提供公开支持的方法（也没有相关的文档说明），但是我们可以使用一个技巧来完成（关于这个技巧，稍后还会更多地介绍）。如果需要更新EMPS_NT 中的每一行，我们必须完成4 个更新：分别更新DEPT_AND_EMP 中的各行，从而更新与之关联的虚拟表。

2) 我们是在语义上更新DEPT_AND_EMP 表中的EMPS列。要知道，尽管在物理上涉及两个表，但是语义上只有一个表。即使我们没有更新部门表中的数据，包含所修改嵌套表的行也会被锁定，不允许其他会话更新。传统的父/子表关系中则不是这样。

² 一般而言，必须总是连接，而不能单独查询嵌套表（如emp_nest）中的数据，但是如果确实需要，是可以的。

1）使用这个hint就可以直接操作嵌套表了：
　　SQL> update /*+nested_table_get_refs */ emps_nt

2 set ename = initcap(ename);

² 嵌套表存储

1）对于前一节创建的表DEPT_AND_EMP，Oracle实际上会创建两个实际的表，结构如下：

Ø DEPT_AND_EMP结构如下：

DEPTNO NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

SYS_NC0000400005$ RAW(16)

Ø Emps_NT结构如下：

SYS_NC_ROWINFO$

NESTED_TABLE_ID RAW(16)

EMPNO NUMBER(4)

ENAME VARCHAR2(10)

JOB VARCHAR2(9)

MGR NUMBER(4)

HIREDATE DATE

SAL NUMBER(7,2)

COMM NUMBER(7,2)

Ø DEPT_AND_EMP上有一个额外的隐藏列SYS_NC0000400005$，并在其上创建了一个惟一约束。在嵌套表EMPS_NT上，有两个隐藏列，其中SYS_NC_ROW INFO$列并不真正算一列，而应该是一个虚拟列，它会把所有标量元素返回为一个对象；另一个隐藏列NESTED_TABLE_ID是一个外键，通过该外键可以联结回父表，但该列上没有索引。

2）查看嵌套表的转储信息

SQL> exec dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM,'STO

RAGE',false)

PL/SQL 过程已成功完成。

SQL> select dbms_metadata.get_ddl('TABLE','DEPT_AND_EMP') from dual;

DBMS_METADATA.GET_DDL('TABLE','DEPT_AND_EMP')

---------------------------------------------------------------------

CREATE TABLE "ORACLE"."DEPT_AND_EMP"

( "DEPTNO" NUMBER(2,0),

"DNAME" VARCHAR2(14),

"LOC" VARCHAR2(13),

"EMPS" "ORACLE"."EMP_TAB_TYPE" ,

PRIMARY KEY ("DEPTNO")

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

TABLESPACE "USERS" ENABLE

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

TABLESPACE "USERS"

NESTED TABLE "EMPS" STORE AS "EMPS_NT"

(( CONSTRAINT "EMPS_EMPNO_UNIQUE" UNIQUE ("EMPNO")

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

TABLESPACE "USERS" ENABLE)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING

TABLESPACE "USERS" ) RETURN AS VALUE

说明：RETURN AS VALUE表示Oracle会按值把嵌套表返回给客户：具体数据会随各行传输。该选项也可以设置为RETURN AS LOCATOR，这说明客户会得到指向数据的一个指针，而不是数据本身，当且仅当客户对这个指针解除引用时，才会把数据传输给客户，这样可以节省网络往返通信开销。

3）必须对NESTED_TABLE_ID列建立索引，因为我们总是从父表联结到子表来访问嵌套表。最好的解决方案是使用一个IOT来存储嵌套表，此时，它会按NESTED_TAB LE_ID将子行物理地共同存储在一块（所以用最少的物理I/O就能完成表的获取），这样就不必在RAW(16)列上建立冗余的索引。还应该加入索引键压缩来避免冗余的NESTED_TAB LE_ID（否则会重复存储）。如下：

SQL>create table "ORACLE"."DEPT_AND_EMP"

("DEPTNO" NUMBER(2,0),

"DNAME" VARCHAR2(14),

"LOC" VARCHAR2(13),

"EMPS" "EMP_TAB_TYPE")

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING

STORAGE(INITIAL 131072 NEXT 131072

MINEXTENTS 1 MAXEXTENTS 4096

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT)

TABLESPACE "USERS"

NESTED TABLE "EMPS" STORE AS "EMPS_NT"

((empno NOT NULL,unique(empno),primary key(nested_table_id,empno))

organization index compress 1)

RETURN AS VALUE

² 嵌套表小结

1）不要把嵌套表用作一种持久存储机制，因为：

Ø 这样会增加不必要的RAW(16)列存储开销。父表和子表都有这个列。

Ø 这会在父表上增加另一个惟一约束（相应地带有不必要的开销），而父表通常已经有一个惟一约束。

Ø 如果不使用NESTED_TABLE_GET_REFS（该方法未得到公开支持），嵌套表本身使用起来并不简单。

1）作为一个存储机制，应更倾向于自己创建父/子表；一般把嵌套表用于视图中。

2）如果你确实把嵌套表用作一个存储机制，一定要保证将嵌套表建立为一个IOT，以避免NESTED_TABLE_ID上索引的开销以及嵌套表本身的开销。

5. 临时表（Temporary table）

² 注意点：

1）用于保存事务或会话期间的中间结果集。临时表中保存的数据只对当前会话可见，所有会话都看不到其它会话的数据：即使当前会话已经提交里数据，别的会话也看不到它的数据。

2）临时表比常规表生成的redo少得多。由于临时表必须为其包含的数据生成undo信息，所以也会生成一定的redo。UPDATE和DELETE会生成最多的undo，INSERT和SELECT生成的undo最少。

3）临时表会从当前登陆用户的临时表空间分配存储空间，或者如果从一个定义这权限过程访问临时表，就会使用该过程所有者的临时表空间。

4）创建临时表的动作不涉及存储空间分配，不会为此分配初始区段，这与常规表有所不同。对于临时表，运行时当一个会话第一次在临时表中放入数据时，才会为该会话创建一个临时段。

5） Oracle的临时表是“静态”定义的。每个数据库只创建一次临时表，而不是为数据库中的每个存储过程都创建一次。

6）临时表可以是基于会话的：即提交后临时表中的数据仍然存在，但是断开连接后再连接时数据就没有了。以下创建基于会话的临时表：

SQL> create global temporary table temp_table_session

2 on commit preserve rows

3 as

4 select * from scott.emp where 1=0

5 /

7）临时表也可以是基于事务的：即提交之后数据就消失。以下创建基于事务的临时表：

SQL> create global temporary table temp_table_transaction

2 on commit delete rows

3 as

4 select * from scott.emp where 1=0

5 /

8）一个应用的临时表应该在应用安装期间创建，绝对不要在运行时创建。

9）临时表可以有永久表的许多属性，譬如可以有触发器、检查约束、索引等。但永久表的某些特性在临时表中并不支持，这包括：

Ø 不能有引用完整性约束。

Ø 不能有NESTED TABLE类型的列。

Ø 不能是IOT

Ø 不能在任何类型的聚簇中。

Ø 不能分区。

Ø 不能通过ANALYZE表命令生成统计信息。

² 使用基于代价的优化器（cost-based optimizer,CBO）时，有效的统计对于优化器的成败至关重要。如果没有统计信息，优化器就只能对数据的分布、数据量以及索引的选择性做出猜测。如果这些猜测是错的，为查询生成的查询计划（大量使用临时表）可能就不是最优的。

1）正确的解决方案是根本不使用临时表，而是使用一个INLINE VIEW，采用该方式，Oracle可以访问一个表的所有相关统计信息，而且得出一个最优计划。

2）如果必须向优化器提供全局临时表的统计信息，有以下三种解决方法：

Ø 动态采样：是优化器的一种功能，硬解析一个查询时，会扫描数据库中的段（采样），收集有用的统计信息，来完成这个特定查询的优化。

SQL> create global temporary table gtt

2 as

3 select * from scott.emp where 1=0;

表已创建。

SQL> insert into gtt select * from scott.emp;

已创建14行。

SQL> set autotrace traceonly explain

SQL> select * from gtt;

执行计划

----------------------------------------------------------

Plan hash value: 917624683

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 1218 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| GTT | 14 | 1218 | 2 (0)| 00:00:01 |

--------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement

注意：动态采用不是免费的，由于必须在查询解析时完成，所以存在相当大的代价。如果能提前收集适当的代表性统计信息，就可以避免在硬解析时执行动态采样。为此可以使用DBMS_STATS。

Ø 使用DBMS_STATS包：利用GATHER_SCHEMA_STATS调用来使用DBMS_STATS

说明：这些过程允许你传入一个参数GATHER_TEMP，这是一个布尔值，默认为FALSE。设置为TRUE时，所有ON COMMIT PRESERVE ROWS全局临时表都会收集和存储统计信息（该技术在ON COMMIT DELETE ROWS表上不可行）。

SQL>create global temporary table gtt1(x number)

2 on commit preserve rows

表已创建。

SQL>create global temporary table gtt2(x number)

2 on commit delete rows

表已创建。

SQL> insert into gtt1 select user_id from all_users;

已创建32行。

SQL> insert into gtt2 select user_id from all_users;

已创建32行。

SQL> exec dbms_stats.gather_schema_stats(user,gather_temp=>TRUE)

PL/SQL 过程已成功完成。

SQL> select table_name,last_analyzed,num_rows from user_tables;

TABLE_NAME LAST_ANALYZED NUM_ROWS

------------------------------ -------------- ----------

GTT1 14-8月 -09 32

GTT2 14-8月 -09 0

EMP 14-8月 -09 14

注意：ON COMMIT PRESERVE ROWS 表会有正确的统计，但是ON COMMIT DELETE ROWS 表没有。DBMS_STATS 将提交，而这会擦除ON COMMIT DELETE ROWS 表中的所有信息。如果有ON COMMIT DELETE ROWS 全局临时表，就不应该使用这种方法，因为这样会收集到不正确的值。

Ø 使用DBMS_STATS包：通过一个手动过程用临时表的代表性统计信息填充数据字典

SQL> create global temporary table t (x int,y varchar2(100));

表已创建。

SQL> begin

2 dbms_stats.set_table_stats(ownname => USER,

3 tabname => 'T',

4 numrows => 500,

5 numblks => 7,

6 avgrlen => 100);

7 end;

8 /

PL/SQL 过程已成功完成。

SQL> select table_name,num_rows,blocks,avg_row_len

2 from user_tables

3 where table_name = 'T';

TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN

------------------------------ ---------- ---------- -----------

T 500 7 100

注意：现在，优化器不会使用它自己的最优猜测，而会使用我们给出的最优猜测。

9．对象表

² 创建语法：

Create table t of Some_Type;

T的属性（列）由SOME_TYPE的定义得出。

² 查看隐藏列

SQL> create or replace type address_type

2 as object

3 (city varchar2(30),

4 street varchar2(30),

5 state varchar2(2),

6 zip number

7 )

8 /

类型已创建。

SQL> create or replace type person_type

2 as object

3 (name varchar2(30),

4 dob date,

5 home_address address_type,

6 work_address address_type

7 )

8 /

类型已创建。

SQL> create table people of person_type

2 /

表已创建。

SQL> desc people

名称是否为空? 类型

----------------------------------------- -------- -------------

NAME VARCHAR2(30)

DOB DATE

HOME_ADDRESS ADDRESS_TYPE

WORK_ADDRESS ADDRESS_TYPE

NAME SEGCOLLENGTH

------------------------------ ------------

SYS_NC_OID$ 16

SYS_NC_ROWINFO$ 1

NAME 30

DOB 7

HOME_ADDRESS 1

SYS_NC00006$ 30

SYS_NC00007$ 30

SYS_NC00008$ 2

SYS_NC00009$ 22

WORK_ADDRESS 1

SYS_NC00011$ 30

SYS_NC00012$ 30

SYS_NC00013$ 2

SYS_NC00014$ 22

14 rows selected.

² 分析：

1） SYS_NC_OID$：系统生成的对象ID。是一个惟一的RAW(16)列，该列上有一个惟一约束，而且在该列上创建了一个相应的惟一索引。

2） SYS_NC_ROWINFO$：和嵌套表中的一样，是一个“神奇函数”，把整行作为一列返回。

SYS_NC_ROWINFO$(NAME, DOB, HOME_ADDRESS(CITY, STREET, STATE, ZIP), WORK_ADDRESS(CITY, STREET, STATE, ZIP))

----------------------------------------------------------------------------------------------------------------

PERSON_TYPE('Tom', '15-3月 -65', ADDRESS_TYPE('Reston', '123 Main Street', 'Va',

45678), ADDRESS_TYPE('Redwood', '1 Oracle Way', 'Ca', 23456))

3） HOME_ADDRESS,WORK_ADDRESS：也是“神奇的函数”。把所表示的列集返回为一个对象。这些不占用实际空间，只是为实际只是NULL或NOT NULL。

4） SYS_NCnnnnn$：这些是嵌入的对象类型的标量实现。系统生成的名字是必要的，因为列名必须惟一。

² 使用EXP/IMP查看这个表的完整SQL

SQL> host exp oracle/555 tables=people rows=n

Export: Release 11.1.0.6.0 - Production on 星期六 8月 15 15:41:34 2009

连接到: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

注: 将不导出表数据 (行)

即将导出指定的表通过常规路径...

. . 正在导出表 PEOPLE

成功终止导出, 没有出现警告。

SQL> host imp oracle/555 indexfile=people.sql full=y

Import: Release 11.1.0.6.0 - Production on 星期六 8月 15 15:42:12 2009

连接到: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

经由常规路径由 EXPORT:V11.01.00 创建的导出文件

已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入

成功终止导入, 没有出现警告。

SQL> host type people.sql

REM CREATE TABLE "ORACLE"."PEOPLE" OF "PERSON_TYPE" OID

REM '733DA63D9AE84B82976155A1142642FC' OIDINDEX (PCTFREE 10 INITRANS 2

REM MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1

REM BUFFER_POOL DEFAULT) TABLESPACE "USERS") PCTFREE 10 PCTUSED 40

REM INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST

REM GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS ;

REM ALTER TABLE "ORACLE"."PEOPLE" MODIFY ("SYS_NC_OID$" DEFAULT REM SYS_OP_GUID()) ;

说明：现在明显看到了OIDINDEX子句，还看到了对SYS_NC_OID$列的一个引用。这是这个表的隐藏主键。函数SYS_OP_GUID与SYS_GUID相同，它们都返回一个全局惟一的标识符，这是一个16字节的RAW字段。

² 为对象分配标识符的行为是可以修改的。可以不让系统为我们生成一个伪主键，而是使用对象的自然键。如果对象所基于的是一个主键而不是系统生成的键，这个对象表的SYS_NC_OID$列就是虚拟列，并不占用磁盘上的任何实际存储空间：

1）首先分析系统生成的OID表

SQL> create table people of person_type;

Table created.

SQL> select name,type#,segcollength

2 from sys.col$

3 where obj# = (select object_id

4 from user_objects

5 where object_name = 'PEOPLE')

6 and name like 'SYS\_NC\_%' escape '\'

7 /

NAME TYPE# SEGCOLLENGTH

------------------------------ ---------- ------------

SYS_NC_OID$ 23 16

SYS_NC_ROWINFO$ 121 1

SQL> insert into people(name)

2 select rownum from all_objects;

67759 rows created.

SQL> exec dbms_stats.gather_table_stats(user,'PEOPLE');

PL/SQL procedure successfully completed.

SQL> select table_name,avg_row_len from

2 user_object_tables;

TABLE_NAME AVG_ROW_LEN

------------------------------ -----------

PEOPLE 23

说明：行平均长度为23字节：16字节用于SYS_NC_OID$，7字节用于NAME。

2）使用NAME列上的一个主键作为对象标识符：

SQL> create table "PEOPLE"

2 OF "PERSON_TYPE"

3 (constraint people_pk primary key(name))

4 object identifier is PRIMARY KEY

5 /

Table created.

SQL> select table_name,avg_row_len from

2 user_object_tables;

NAME TYPE# SEGCOLLENGTH

------------------------------ ---------- ------------

SYS_NC_OID$ 23 81

SYS_NC_ROWINFO$ 121 1

说明：现在看来SYS_NC_OID$列不是只有16字节，而变成一个81字节的大列！实际上，它是空的，没有存储任何数据。系统会根据对象表、其底层类型和行本身中的值生成一个惟一的ID：

SQL> insert into people(name)

2 values('Hello World!');

1 row created.

SQL> select sys_nc_oid$ from people;

SYS_NC_OID$

----------------------------------------------------------------------------------------------------------------

D81DF11A98B042C0AE30DA7F3BA9920100000017260100010001002900000000000C07001E0100002A00078401FE000000140C48656C6C6F20576F726C6421000000000000000000000000000000000000

SQL> select utl_raw.cast_to_raw('Hello World!') data from dual;

DATA

----------------------------------------------------------------------------------------------------------------

48656C6C6F20576F726C6421

SQL> select utl_raw.cast_to_varchar2(sys_nc_oid$) data from people;

DATA

---------------------------------------------------------------------------------------------------------------- Hello World!

说明：如果选择SYS_NC_OID$列，查看所插入串的HEX 转储信息，可以看到行数据本身已经嵌入到对象ID中。将对象ID 转换为一个VARCHAR2，可以更清楚地确认这一点。这是不是表示我们的数据要存储两次，而且存在大量开销？不，并非如此，这正是神奇之处，只是在获取时才有这样的SYS_NC_OID $列。Oracle从表中选择SYS_NC_OID $时会合成数据。