q 堆组织表(heap organized table):这些就是“普通”的标准数据库表。数据以堆的方式管理。增加数据时,会使用段中找到的第一个能放下此数据的自由空间。从表中删除数据时,则允许以后的INSERT和UPDATE重用这部分空间。这就是这种表类型中的“堆”这个名词的由来。堆(heap)是一组空间,以一种有些随机的方式使用。
q 索引组织表(index organized table):这些表按索引结构存储。这就强制要求行本身有某种物理顺序。在堆中,只要放得下,数据可以放在任何位置;而索引组织表(IOT)有所不同,在IOT中,数据要根据主键有序地存储。
q 索引聚簇表(index clustered table):聚簇(cluster)是指一个或多个表组成的组,这些表物理地存储在相同的数据库块上,有相同聚簇键值的所有行会相邻地物理存储。这种结构可以实现两个目标。首先,多个表可以物理地存储在一起。一般而言,你可能认为一个表的数据就在一个数据库块上,但是对于聚簇表,可能把多个表的数据存储在同一个块上。其次,包含相同聚簇键值(如DEPTNO=10)的所有数据会物理地存储在一起。这些数据按聚簇键值“聚簇”在一起。聚簇键使用B*树索引建立。
q 散列聚簇表(hash clustered table):这些表类似于聚簇表,但是不使用B*树索引聚簇键来定位数据,散列聚簇将键散列到聚簇上,从而找到数据应该在哪个数据库块上。在散列聚簇中,数据就是索引(这是隐喻的说法)。如果需要频繁地通过键的相等性比较来读取数据,散列聚簇表就很适用。
q 有序散列聚簇表(sorted hash clustered table):这种表类型是Oracle 10g中新增的,它结合了散列聚簇表的某些方面,同时兼有IOT的一些方面。其概念如下:你的行按某个键值(如CUSTOMER_ID)散列,而与该键相关的一系列记录以某种有序顺序到达(因此这些记录是基于时间戳的记录),并按这种有序顺序处理。例如,客户在你的订单输入系统中下订单,这些订单会按先进先出(first in, first out, FIFO)的方式获取和处理。在这样一个系统中,有序散列聚簇就是适用的数据结构。
q 嵌套表(nested table):嵌套表是Oracle对象关系扩展的一部分。它们实际上就是系统生成和维护的父/子关系中的子表。嵌套表的工作类似于SCOTT模式中的EMP和DEPT。可以认为EMP是DEPT表的子表,因为EMP表有一个指向DEPT的外键DEPTNO。嵌套表与子表的主要区别是:嵌套表不像子表(如EMP)那样是“独立”表。
q 临时表(temporary table):这些表存储的是事务期间或会话期间的“草稿”数据。临时表要根据需要从当前用户的临时表空间分配临时区段。每个会话只能看到这个会话分配的区段;它从不会看到其他任何会话中创建的任何数据。
q 对象表(object table):对象表基于某种对象类型创建。它们拥有非对象表所没有的特殊属性,如系统会为对象表的每一行生成REF(对象标识符)。对象表实际上是堆组织表、索引组织表和临时表的特例,还可以包含嵌套表作为其结构的一部分。
q 外部表(external table):这些表并不存储在数据库本身中,而是放在数据库之外,即放在平常的操作系统文件中。在Oracle9i及以上版本中,利用外部表可以查询数据库之外的一个文件,就好像这个文件也是数据库中平常的表一样。外部表对于向数据库加载数据最有用(外部表是非常强大的数据加载工具)。Oracle 10g则更进一步,还引入了一个外部表卸载功能,在不使用数据库链接的情况下,这为在Oracle数据库之间移动数据提供了一种简单的方法。我们将在第15章更详细地讨论外部表。
q 不论哪种类型的表,都有以下一般信息:
q 一个表最多可以有1000列,不过我不鼓励设计中真的包含这么多列,除非存在某个硬性需求。表中的列数远远少于1000列时才最有效。Oracle在内部会把列数大于254的行存储在多个单独的行段(row piece)中,这些行段相互指向,而且必须重新组装为完整的行影像。
q 表的行数几乎是无限的,不过你可能会遇到另外某个限制,使得这种“无限”并不实际。例如,一般来讲,一个表空间最多有1022个文件(不过,Oracle 10g中有一些新的BIGFILE表空间,这些表空间可以超出上述文件大小限制)。假设你有一些32GB的文件,也就是说,每个表空间有32,704GB,就会有2,143,289,344个块,每个块大小为16KB。你可能在每个块上放160行(每行大约80~100字节)。这样就会有342,926,295,040行。不过,如果对这个表分区,这个行数还能很容易地翻倍。例如,假设一个表有1024个散列分区,则能有1024×342,926,295,040行。确实存在着上限,但是在接近这些上限之前,你肯定会遇到另外某个实际限制。
q 表中的列有多少种排列(以及这些列的函数有多少种排列),表就可以有多少个索引。随着基于函数的索引的出现,理论上讲,说能创建的索引数是无限的!不过,同样由于存在一些实际的限制,这会影响真正能创建和维护的索引数。
q 即使在一个数据库中也可以有无限多个表。不过,还是同样的道理,实际的限制会使数据库中的表数在一个合理的范围内。不可能有数百万个表(这么多表对于创建和管理来说都是不实际的),但是有数千个表还是允许的。
q 聚簇(cluster):这种段类型能存储表。有两种类型的聚簇:B*树聚簇和散列聚簇。聚簇通常用于存储多个表上的相关数据,将其“预联结”存储到同一个数据库块上;还可以用于存储一个表的相关信息。“聚簇”这个词是指这个段能把相关的信息物理的聚在一起。
q 表(table):表段保存一个数据库表的数据,这可能是最常用的段类型,通常与索引段联合使用。
q 表分区(table partition)或子分区(subpartition):这种段类型用于分区,与表段很相似。分区表由一个或多个分区段(table partition segment)组成,组合分区表则由一个或多个表子分区段(table subpartition segment)组成。
q 索引(index):这种段类型可以保存索引结构。
q 索引分区(index partition):类似与表分区,这种段类型包含一个索引的某个片。分区索引由一个或多个索引分区段(index partition segment)组成。
q Lob分区(lob partition)、lob子分区(lob subpartition)、lob索引(lobindex)和lob段(lobsegment):lobindex和lobsegment段保存大对象(large object或LOB)的结构。对包含LOB的表分区时,lobsegment也会分区,lob分区段(lob partition segment)正是用于此。有意思的是,并没有一种lobindex分区段(lobindex partition segment)类型——不论出于什么原因,Oracle将分区lobindex标记为一个索引分区(有人很奇怪为什么要另外给lobindex取一个特殊的名字!)。
q 嵌套表(nested table):这是为嵌套表指定的段类型,它是主/明细关系中一种特殊类型的“子”表,这种关系随后将详细讨论。
q 回滚段(rollback)和Type2 undo段:undo数据就存储在这里。回滚段是DBA手动创建的段。Type2 undo段由Oracle自动创建和管理。
Create table t ( x int primary key, y clob, z blob ); |
ops$tkyte@ORA10G> select segment_name, segment_type 2 from user_segments;
no rows selected
ops$tkyte@ORA10G> create table t ( x int primary key, y clob, z blob );
Table created.
ops$tkyte@ORA10G> select segment_name, segment_type 2 from user_segments;
SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------
SYS_IL0000063631C00002$$ LOBINDEX
SYS_LOB0000063631C00003$$ LOBSEGMENT
SYS_C009783 INDEX
SYS_IL0000063631C00003$$ LOBINDEX
SYS_LOB0000063631C00002$$ LOBSEGMENT
T TABLE
6 rows selected.
|
另外,每个LOB列分别创建了两个段:一个段用于存储字符大对象(character large object, CLOB)或二进制大对象(binary large object, BLOB)所指的实际数据块,另一个段用于“组织”这些数据块。LOB为非常大块的信息提供了支持,可以多达几GB。LOB存储在lobsegment的块中,lobindex用于跟踪这些LOB块在哪里,以及应该以何种顺序来访问它们。
q 手动段空间管理(Manual Segment Space Management):由你设置FREELISTS、FREELIST GROUPS、PCTUSED和其他参数来控制如何分配、使用和重用段中的空间。在这一章中我会把这种空间管理方法称为MSSM,但是要记住,这是一个我自造的缩写,Oracle文档中没有这个缩写。
q 自动段空间管理(Automatic Segment Space Management, ASSM):你只需控制与空间使用相关的一个参数:PCTFREE。创建段时也可以接受其他参数,但是这些参数将被忽略。
MSSM是Oracle的遗留实现。它已经存在多年,许多版本都支持MSSM。ASSM则在Oracle 9i Release 1中才首次引入。原先用于控制空间分配和提供高并发性的参数数不胜数,并且需要对这些参数进行调整,人们不希望还要这么做,这正是设计ASSM的出发点。例如,倘若将FREELISTS参数设置为默认值1,可能会出现,如果你的段是插入/更新新密集的(有大量插入/更新操作),对自由空间的分配就会存在竞争。Oracle要在表中插入一行,或更新一个索引键条目,或者由于更新一行而导致这一行迁移时(稍后还会更多地介绍这方面的内容),可能需要从与这个段相关的自由块列表中得到一个块。如果只有一个自由块列表,一次就只有一个事务能查看和修改这个列表,事务之间必须相互等待。在这种情况下,如果有多个FREELISTS和FREELIST GROUPS,就能提高并发性,因为事务可以分别查看不同的列表,而不会相互竞争。
q BUFFER_POOL
q PCTFREE
q INITRANS
q MAXTRANS(仅用于9i;在10g中,所有段都会忽略这个参数)
HWM很重要,因为Oracle在全面扫描段时会扫描HWM之下的所有块,即使其中不包含任何数据。这会影响全面扫描的性能,特别是当HWM之下的大多数块都为空时。要查看这种情况,只需创建一个有1,000,000行的表(或者创建其他有大量行的表),然后对这个表执行一个SELECT COUNT(*)。下面再删除(DELETE)这个表中的每一行,你会发现尽管SELECT COUNT(*)统计出0行,但是它与统计出1,000,000所花的时间一样长(如果需要完成块清除,时间可能还会更长:有关内容请参加9.5.5节)。这是因为Oracle在忙于读取HWM之下的所有块,查看其中是否包含数据。如果对这个表使用TRUNCATE而不是删除其中的每一行,你可以比较一下结果有什么不同。TRUNCATE会把表的HWM重置回“0”,还会截除表上的相关索引。由于以上原因,如果你打算删除表中的所有行,就应该选择使用TRUNCATE(如果可以使用的话)。
ops$tkyte@ORA10GR1> create table t ( x int, y char(50) ) tablespace MSSM;
Table created.
|
begin
for i in 1 .. 100000
loop
insert into t values ( i, 'x' );
end loop;
commit;
end;
/
exit;
|
Snap Id Snap Time Sessions Curs/Sess Comment --------- ------------------ -------- --------- ------------------- Begin Snap: 793 29-Apr-05 13:45:36 15 3.9 End Snap: 794 29-Apr-05 13:46:34 15 5.7 Elapsed: 0.97 (mins)
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Call Time
-------------------------------------------- ------------ ----------- -------------
CPU time 165 53.19 buffer busy waits 368,698 119 38.43
log file parallel write 1,323 21 6.86
latch: cache buffers chains 355 2 .67
enq: HW - contention 2,828 1 .24
|
ops$tkyte@ORA10GR1> create table t ( x int, y char(50) )
2 storage( freelists 5 ) tablespace MSSM;
Table created.
|
ops$tkyteORA10GR1> alter table t storage ( FREELISTS 5 );
Table altered.
|
你会看到,buffer busy waits大幅下降,而且所需的CPU时间也随着耗用时间的下降而减少(因为这里做的工作更少;对闩定数据结构的竞争确实会让CPU焦头烂额):
Snap Id Snap Time Sessions Curs/Sess Comment --------- ------------------ -------- --------- ------------------- Begin Snap: 809 29-Apr-05 14:04:07 15 4.0 End Snap: 810 29-Apr-05 14:04:41 14 6.0 Elapsed: 0.57 (mins)
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Call Time
-------------------------------------------- ------------ ----------- ---------
CPU time 122 74.66 buffer busy waits 76,538 24 14.94
log file parallel write 722 14 8.45
latch: cache buffers chains 144 1 .63
enq: HW - contention 678 1 .46
|
ops$tkyte@ORA10GR1> create tablespace assm
2 datafile size 1m autoextend on next 1m
3 segment space management auto;
Tablespace created.
ops$tkyte@ORA10GR1> create table t ( x int, y char(50) ) tablespace ASSM;
Table created.
|
Snap Id Snap Time Sessions Curs/Sess Comment --------- ------------------ -------- --------- ------------------- Begin Snap: 812 29-Apr-05 14:12:37 15 3.9 End Snap: 813 29-Apr-05 14:13:07 15 5.6 Elapsed: 0.50 (mins)
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Call Time
-------------------------------------------- ------------ ----------- ---------
CPU time 107 78.54
log file parallel write 705 12 9.13
buffer busy waits 12,485 12 8.52
latch: library cache 68 1 .70
LGWR wait for redo copy 3,794 1 .47 |
1. 实际上PCTUSED的含义是,如果块上不自由的空间到达或小于PCTUSED参数指定的百分比时,这个块将重新变为自由,如倘若PCTUSED为40%,那么块上不自由的空间小于40%时,即自由空间达到60%时,这个块就重新变为自由。——译者注。
q 高PCTFREE,低PCTUSED:如果你插入了将要更新的大量数据,而且这些更新会频繁地增加行的大小,此时就适合采用这种设置。这种设置在插入后会在块上预留大量的空间(高PCTFREE),并使得将块放回到freelist之前必须几乎为空(低PCTUSED)。
q 低PCTFREE,高PCTUSED:如果你只想对表完成INSERT或DELETE,或者如果你确实要完成UPDATE,但UPDATE只是缩小行的大小,此时这种设置就很适合。
通常对象都采用LOGGING方式创建,这说明对象上完成的操作只要能生成redo就都会生成redo。NOLOGGING则允许该对象完成某些操作时可以不生成redo;这个内容在上一章详细介绍过。NOLOGGING只影响几个特定的操作,如对象的初始创建,或使用SQL*Loader的直接路径加载,或者重建(请参考Oracle SQL Reference手册来了解你使用的数据库对象可以应用哪些操作)。
这个选项并不会完全禁用对象的重做日志生成,只是几个特定的操作不生成日志而已。例如,如果把一个表创建为SELECT NOLOGGING,然后INSERT INTO THAT_TABLE VALUES(1),这个INSERT就会生成日志,但是表创建可能不生成redo(DBA可以在数据库或表空间级强制生成日志)。
段中每个块都有一个块首部。这个块首部中有一个事务表。事务表中会建立一些条目来描述哪些事务将块上的哪些行/元素锁定。这个事务表的初始大小由对象的INITRANS设置指定。对于表,这个值默认为2(索引的INITRANS也默认为2)。事务表会根据需要动态扩展,最大达到MAXTRANS个条目(假设块上有足够的自由空间)。所分配的每个事务条目需要占用块首部中的23~24字节的存储空间。注意,对于Oracle 10g,MAXTRANS则会忽略,所有段的MAXTRANS都是255。
ops$tkyte@ORA10GR1> create table t
2 ( a int,
3 b varchar2(4000) default rpad('*',4000,'*'),
4
c varchar2(3000) default rpad('*',3000,'*')
5 )
6 /
Table created.
ops$tkyte@ORA10GR1> insert into t (a) values ( 1);
1 row created.
ops$tkyte@ORA10GR1> insert into t (a) values ( 2);
1 row created.
ops$tkyte@ORA10GR1> insert into t (a) values ( 3);
1 row created.
ops$tkyte@ORA10GR1> delete from t where a = 2 ;
1 row deleted.
ops$tkyte@ORA10GR1> insert into t (a) values ( 4);
1 row created.
ops$tkyte@ORA10GR1> select a from t;
A
----------
1
4
3
|
ops$tkyte@ORA10GR1> create table t 2 ( x int primary key,
3 y date,
4 z clob
5 )
6 /
Table created.
|
ops$tkyte@ORA10GR1> select dbms_metadata.get_ddl( 'TABLE', 'T' ) from dual;
DBMS_METADATA.GET_DDL('TABLE','T')
-------------------------------------------------------------------------------
CREATE TABLE "OPS$TKYTE"."T"
( "X" NUMBER(*,0),
"Y" DATE,
"Z" CLOB,
PRIMARY KEY ("X")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
LOB ("Z") STORE AS ( TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) |
这个技巧的好处是,它显示了CREATE TABLE语句的许多选项。我只需要提供数据类型,Oracle就会为我生成详细的“版本”(CREATE TABLE版本)。现在我可以定制这个详细的版本,可能把ENABLE STORAGE IN ROW改成DISABLE STORAGE IN ROW,这样会禁用随结构化数据在行中存储LOB数据,而把LOB数据存储在另外一个段中。我一直都在使用这个技巧来节省我的时间,因为要从那个庞大的线路图中找出该使用哪个选项很让人犯愁,如果不采用这个技巧,可能就会为此浪费好几分钟。使用这个技术还可以了解不同的情况下CREATE TABLE语句有哪些可用的选项。
既然你知道了如何查看一个给定的CREATE TABLE语句可用的大多数选项,那么对于堆表来说,需要注意哪些重要的选项呢?在我看来,对于ASSM有两个重要选项,对于MSSM,重要选项有4个:
索引组织表(index organized table,IOT)就是存储在一个索引结构中的表。存储在堆中的表是无组织的(也就是说,只要有可用的空间,数据可以放在任何地方),IOT中的数据则按主键存储和排序。对你的应用来说,IOT表现得与一个“常规”表并无二致;还是要使用SQL正常地访问这些表。IOT对信息获取、空间应用和OLAP应用特别有用。
create table keywords
( word varchar2(50),
position int,
doc_id int,
primary key(word,position,doc_id)
);
|
ops$tkyte@ORA10GR1> create table emp
2 as
3 select object_id empno, 4 object_name ename, 5 created hiredate, 6 owner job 7 from all_objects
8 /
Table created.
ops$tkyte@ORA10GR1> alter table emp add constraint emp_pk primary key(empno)
2 /
Table altered.
ops$tkyte@ORA10GR1> begin
2 dbms_stats.gather_table_stats( user, 'EMP', cascade=>true );
3 end;
4 /
PL/SQL procedure successfully completed. |
ops$tkyte@ORA10GR1> create table heap_addresses 2 ( empno references emp(empno) on delete cascade, 3 addr_type varchar2(10), 4 street varchar2(20), 5 city varchar2(20), 6 state varchar2(2), 7 zip number, 8 primary key (empno,addr_type)
9 )
10 /
Table created.
ops$tkyte@ORA10GR1> create table iot_addresses 2 ( empno references emp(empno) on delete cascade, 3 addr_type varchar2(10), 4 street varchar2(20), 5 city varchar2(20), 6 state varchar2(2), 7 zip number, 8 primary key (empno,addr_type)
9 )
10 ORGANIZATION INDEX
11 /
Table created.
|
ops$tkyte@ORA10GR1> insert into heap_addresses 2 select empno, 'WORK', '123 main street', 'Washington', 'DC', 20123
3 from emp;
48250 rows created.
ops$tkyte@ORA10GR1> insert into iot_addresses 2 select empno, 'WORK', '123 main street', 'Washington', 'DC', 20123
3 from emp;
48250 rows created.
|
ops$tkyte@ORA10GR1> exec dbms_stats.gather_table_stats( user, 'HEAP_ADDRESSES' ); PL/SQL procedure successfully completed. ops$tkyte@ORA10GR1> exec dbms_stats.gather_table_stats( user, 'IOT_ADDRESSES' ); PL/SQL procedure successfully completed. |
ops$tkyte@ORA10GR1> set autotrace traceonly ops$tkyte@ORA10GR1> select *
2 from emp, heap_addresses
3 where emp.empno = heap_addresses.empno
4 and emp.empno = 42;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=4 Bytes=336) 1 0 NESTED LOOPS (Cost=8 Card=4 Bytes=336) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=1... 3 2 INDEX (UNIQUE SCAN) OF 'EMP_PK' (INDEX (UNIQUE)) (Cost=1 Card=1) 4 1 TABLE ACCESS (BY INDEX ROWID) OF 'HEAP_ADDRESSES' (TABLE) (Cost=6... 5 4 INDEX (RANGE SCAN) OF 'SYS_C008078' (INDEX (UNIQUE)) (Cost=2 Card=4)
Statistics
----------------------------------------------------------
...
11 consistent gets
...
4 rows processed
|
ops$tkyte@ORA10GR1> select * 2 from emp, iot_addresses 3 where emp.empno = iot_addresses.empno 4 and emp.empno = 42;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=4 Bytes=336) 1 0 NESTED LOOPS (Cost=4 Card=4 Bytes=336) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=1... 3 2 INDEX (UNIQUE SCAN) OF 'EMP_PK' (INDEX (UNIQUE)) (Cost=1 Card=1) 4 1 INDEX (RANGE SCAN) OF 'SYS_IOT_TOP_59615' (INDEX (UNIQUE)) (Cost=2...
Statistics
----------------------------------------------------------
...
7 consistent gets
...
4 rows processed
ops$tkyte@ORA10GR1> set autotrace off |
ops$tkyte@ORA10GR1> begin
2 for x in ( select empno from emp )
3 loop
4 for y in ( select emp.ename, a.street, a.city, a.state, a.zip 5 from emp, heap_addresses a 6 where emp.empno = a.empno 7 and emp.empno = x.empno )
8 loop
9 null;
10 end loop;
11 end loop;
12 end;
13 /
PL/SQL procedure successfully completed. |
SELECT EMP.ENAME, A.STREET, A.CITY, A.STATE, A.ZIP
FROM EMP, HEAP_ADDRESSES A
WHERE EMP.EMPNO = A.EMPNO AND EMP.EMPNO = :B1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 48244 7.66 7.42 0 0 0 0 Fetch 48244 6.29 6.56 0 483393 0 192976 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 96489 13.95 13.98 0 483393 0 192976
Rows Row Source Operation
----------- ------------------------------------------------------------------------------------------------------------------------ 192976 NESTED LOOPS (cr=483393 pr=0 pw=0 time=5730335 us) 48244 TABLE ACCESS BY INDEX ROWID EMP (cr=144732 pr=0 pw=0 time=1594981 us) 48244 INDEX UNIQUE SCAN EMP_PK (cr=96488 pr=0 pw=0 time=926147 us)... 192976 TABLE ACCESS BY INDEX ROWID HEAP_ADDRESSES (cr=338661 pr=0 pw=0 time=... 192976 INDEX RANGE SCAN SYS_C008073 (cr=145685 pr=0 pw=0 time=1105135 us)...
********************************************************************************
SELECT EMP.ENAME, A.STREET, A.CITY, A.STATE, A.ZIP
FROM EMP, IOT_ADDRESSES A
WHERE EMP.EMPNO = A.EMPNO AND EMP.EMPNO = :B1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 48244 8.17 8.81 0 0 0 0 Fetch 48244 4.31 4.12 0 292918 0 192976 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 96489 12.48 12.93 0 292918 0 192976 Rows Row Source Operation ----------- ---------------------------------------------------------------------------------------------------------------- 192976 NESTED LOOPS (cr=292918 pr=0 pw=0 time=3429753 us) 48244 TABLE ACCESS BY INDEX ROWID EMP (cr=144732 pr=0 pw=0 time=1615024 us) 48244 INDEX UNIQUE SCAN EMP_PK (cr=96488 pr=0 pw=0 time=930931 us)... 192976 INDEX RANGE SCAN SYS_IOT_TOP_59607 (cr=148186 pr=0 pw=0 time=1417238 us)... |
STAT...consistent gets 484,065 293,566 -190,499
STAT...no work - consistent re 194,546 4,047 -190,499
STAT...consistent gets from ca 484,065 293,566 -190,499
STAT...session logical reads 484,787 294,275 -190,512
STAT...table fetch by rowid 241,260 48,260 -193,000
STAT...buffer is not pinned co 337,770 96,520 -241,250
LATCH.cache buffers chains 732,960 349,380 -383,580
Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 990,344 598,750 -391,594 165.40% |
q 提供缓冲区缓存效率,因为给定查询的缓存中需要的块更少。
q 减少缓冲区缓存访问,这会改善可扩缩性。
q 获取数据的工作总量更少,因为获取数据更快。
q 每个查询完成的物理I/O更少,因为对于任何给定的查询,需要的块更少,而且对地址记录的一个物理I/O很可能可以获取所有地址(而不只是其中一个地址,但堆表实现就只是获取一个地址)。
ops$tkyte@ORA10GR1> create table stocks 2 ( ticker varchar2(10), 3 day date, 4 value number, 5 change number, 6 high number, 7 low number, 8 vol number, 9 primary key(ticker,day)
10 )
11 organization index
12 /
Table created.
|
Select * from stocks
where ticker = 'ORCL'
and day between sysdate-100 and sysdate;
|
ops$tkyte@ORA10GR1> create table t1 2 ( x int primary key, 3 y varchar2(25), 4 z date
5 )
6 organization index;
Table created.
ops$tkyte@ORA10GR1> create table t2 2 ( x int primary key, 3 y varchar2(25), 4 z date
5 )
6 organization index
7 OVERFLOW;
Table created.
ops$tkyte@ORA10GR1> create table t3 2 ( x int primary key, 3 y varchar2(25), 4 z date
5 )
6 organization index
7 overflow INCLUDING y;
Table created.
|
ops$tkyte@ORA10GR1> select dbms_metadata.get_ddl( 'TABLE', 'T1' ) from dual;
DBMS_METADATA.GET_DDL('TABLE','T1')
--------------------------------------------------------------------------------
CREATE TABLE "OPS$TKYTE"."T1"
( "X" NUMBER(*,0),
"Y" VARCHAR2(25),
"Z" DATE,
PRIMARY KEY ("X") ENABLE
)
ORGANIZATION INDEX
NOCOMPRESS
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
PCTTHRESHOLD 50
|
这个表引入了两个新的选项:NOCOMPRESS和PCTTHRESHOLD,稍后将介绍它们。你可能已经注意到了,与前面的CREATE TABLE语法相比,这里好像少了点什么:没有PCTUSED子句,但是这里有一个PCTFREE。这是因为,索引是一个复杂的数据结构,它不像堆那样随机组织,所以数据必须按部就班地存放到它该去的地方去。在堆中,块只是有时能插入新行,而索引则不同,块总是可以插入新的索引条目。如果每个数据(根据它的值)属于一个给定块,在总会放在那个块上,而不论这个块多满或者多空。另外,只是在索引结构中创建对象和填充数据时才会使用PCTFREE。其用法与堆组织表中的用法不同。PCTFREE会在新创建的索引上预留空间,但是对于以后对索引的操作不预留空间,这与不使用PCTUSED的原因是一样的。堆组织表上关于freelist的考虑同样完全适用于IOT。
ops$tkyte@ORA10GR1> create table iot 2 ( owner, object_type, object_name, 3 primary key(owner,object_type,object_name)
4 )
5 organization index
6 NOCOMPRESS
7 as
8 select owner, object_type, object_name from all_objects
9 /
Table created.
|
Sys,table,t1 Sys,table,t2 Sys,table,t3 Sys,table,t4
Sys,table,t5 Sys,table,t6 Sys,table,t7 Sys,table,t8
. . . . . . . . . . . .
Sys,table,t100 Sys,table,t101 Sys,table,t102 Sys,table,t103
Sys,table t1 t2 t3
t4 t5 . . . . . .
. . . t103 t104 . . .
t300 t301 t302 t303
下面做一个快速的测试,对前面CREATE TABLE 的SELECT分别采用NOCOMPRESS、COMPRESS 1和COMPRESS 2选项,来展示能节省多少空间。先来创建IOT,但不进行压缩:
ops$tkyte@ORA10GR1> create table iot
2 ( owner, object_type, object_name,
3 constraint iot_pk primary key(owner,object_type,object_name)
4 )
5 organization index
6 NOCOMPRESS
7 as
8 select distinct owner, object_type, object_name 9 from all_objects
10 /
Table created.
|
现在可以测量所用的空间。为此我们将使用ANALYZE INDEX VALIDATE STRUCTURE命令。这个命令会填写一个名为INDEX_STATS的动态性能视图,其中最多只包含一行,即这个ANALYZE命令最后一次执行的信息:
ops$tkyte@ORA10GR1> analyze index iot_pk validate structure;
Index analyzed.
ops$tkyte@ORA10GR1> select lf_blks, br_blks, used_space, 2 opt_cmpr_count, opt_cmpr_pctsave
3 from index_stats;
LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE ---------- ---------- -------------------- ---------------------------- -------------------------------- 284 3 2037248 2 33 |
由此显示出,我们的索引目前使用了284个叶子块(即数据所在的块),并使用了3个分支块(Oracle在索引结构中导航所用的块)来找到这些叶子块。使用的空间大约是2MB(2,038,248字节)。另外两列名字有些奇怪,这两列是要告诉我们一些信息。OPT_CMPR_COUNT(最优压缩数)列要说的是:“如果你把这个索引置为COMPRESS 2,就会得到最佳的压缩”。OPT_CMPR_PCTSAVE(最优的节省压缩百分比)则是说,如果执行COMPRESS 2,就能节省大约1/3的存储空间,索引只会使用现在2/3的磁盘空间。
为了测试上述理论,我们先用COMPRESS 1重建这个IOT:
ops$tkyte@ORA10GR1> alter table iot move compress 1;
Table altered.
ops$tkyte@ORA10GR1> analyze index iot_pk validate structure;
Index analyzed.
ops$tkyte@ORA10GR1> select lf_blks, br_blks, used_space,
2 opt_cmpr_count, opt_cmpr_pctsave
3 from index_stats;
LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE ---------- ---------- ---------- -------------- ---------------- 247 1 1772767 2 23 |
ops$tkyte@ORA10GR1> alter table iot move compress 2;
Table altered.
ops$tkyte@ORA10GR1> analyze index iot_pk validate structure;
Index analyzed.
ops$tkyte@ORA10GR1> select lf_blks, br_blks, used_space, 2 opt_cmpr_count, opt_cmpr_pctsave
3 from index_stats;
LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE ---------- ---------- ---------- -------------- ---------------- 190 1 1359357 2 0 |
ops$tkyte@ORA10GR1> select (2/3) * 2037497 from dual;
(2/3)*2037497
-------------
1358331.33
|
ops$tkyte@ORA10GR1> begin
2 dbms_metadata.set_transform_param 3 ( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false );
4 end;
/
ops$tkyte@ORA10GR1> select dbms_metadata.get_ddl( 'TABLE', 'T2' ) from dual;
DBMS_METADATA.GET_DDL('TABLE','T2')
--------------------------------------------------------------------------------
CREATE TABLE "OPS$TKYTE"."T2"
( "X" NUMBER(*,0),
"Y" VARCHAR2(25),
"Z" DATE,
PRIMARY KEY ("X") ENABLE
) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
TABLESPACE "USERS"
PCTTHRESHOLD 50 OVERFLOW
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
TABLESPACE "USERS"
ops$tkyte@ORA10GR1> select dbms_metadata.get_ddl( 'TABLE', 'T3' ) from dual;
DBMS_METADATA.GET_DDL('TABLE','T3')
--------------------------------------------------------------------------------
CREATE TABLE "OPS$TKYTE"."T3"
( "X" NUMBER(*,0),
"Y" VARCHAR2(25),
"Z" DATE,
PRIMARY KEY ("X") ENABLE
) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
TABLESPACE "USERS"
PCTTHRESHOLD 50 INCLUDING "Y" OVERFLOW PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
TABLESPACE "USERS"
|
q PCTTHRESHOLD:行中的数据量超过块的这个百分比时,行中余下的列将存储在溢出段中。所以,如果PCTTHRESHOLD是10%,而块大小是8KB,长度大于800字节的行就会把其中一部分存储在别处,而不能在索引块上存储。
q INCLUDING:行中从第一列直到INCLUDING子句所指定列(也包括这一列)的所有列都存储在索引块上,余下的列存储在溢出段中。
ops$tkyte@ORA10GR1> create table iot
2 ( x int,
3 y date,
4 z varchar2(2000), 5 constraint iot_pk primary key (x)
6 )
7 organization index
8 pctthreshold 10
9 overflow
10 /
Table created.
|
ops$tkyte@ORA10GR1> create table iot
2 ( x int,
3 y date, 4 z varchar2(2000), 5 constraint iot_pk primary key (x)
6 )
7 organization index
8 including y
9 overflow
10 /
Table created.
|
对于IOT最后要考虑的是建立索引。IOT本身可以有一个索引,就像在索引之上再加索引,这称为二次索引(secondary index)。正常情况下,索引包含了所指向的行的物理地址,即rowid。而IOT二次索引无法做到这一点;它必须使用另外某种方法来指示行的地址。这是因为IOT中的行可以大量移动,而且它不像堆组织表中的行那样“迁移”。IOT中的行肯定在索引结构中的每个位置上,这取决于它的主键值;只有当索引本身的大小和形状发生改变时行才会移动(下一章将更详细地讨论索引结构如何维护)。为了适应这种情况,Oracle引入了一个逻辑rowid(logical rowid)。这些逻辑rowid根据IOT主键建立。对于行的当前位置还可以包含一个“猜测”,不过这个猜测几乎是错的,因为稍过一段时间后,IOT中的数据可能就会移动。这个猜测是行第一次置于二次索引结构中时在IOT中的物理地址。如果IOT中的行必须移动到另外一个块上,二次索引中的猜测就会变得“过时”。因此,与常规表相比,IOT上的索引效率稍低。在一个常规表上,索引访问通常需要完成一个I/O来扫描索引结构,然后需要一个读来读取表数据。对于IOT,通常要完成两个扫描;一次扫描二次结构,另一次扫描IOT本身。除此之外,IOT上的索引可以使用非主键列提供IOT数据的快速、高效访问。
ops$tkyte@ORA10GR1> create cluster emp_dept_cluster
2 ( deptno number(2) )
3 size 1024
4 /
Cluster created.
|
在此,我们创建了一个索引聚簇(index cluster,还有一种类型是散列聚簇(hash cluster),将在下一节介绍)。这个聚簇的聚簇列是DEPTNO列。表中的列不必非得叫DEPTNO,但是必须是NUMBER(2),这样才能与定义匹配。我们在这个聚簇定义中加一个SIZE 1024选项。这个选项原来告诉Oracle:我们希望与每个聚簇键值关联大约1024字节的数据,Oracle会在用这个数据库块上设置来计算每个块最多能放下多少个聚簇键。假设块大小为8KB,Oracle会在每个数据库块上放上最多7个聚簇键(但是如果数据比预想的更大,聚簇键可能还会少一些)。也就是说,对应部门10、20、30、40、50、60和70的数据会放在一个块上,一旦插入部门80,就会使用一个新块。这并不是说数据按一种有序的方式存储,而是说如果按这种顺序插入部门,它们会很自然地放在一起。如果按下面的顺序插入部门,即先插入10、80、20、30、40、50、60,然后插入70,那么最后一个部门(70)将放在新增的块上。稍后会看到,数据的大小以及数据插入的顺序都会影响每个块上都存储的聚簇键个数。
ops$tkyte@ORA10GR1> create index emp_dept_cluster_idx
2 on cluster emp_dept_cluster
3 /
Index created.
|
ops$tkyte@ORA10GR1> 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 /
Table created.
ops$tkyte@ORA10GR1> create table emp
2 ( empno number primary key,
3 ename varchar2(10), 4 job varchar2(9), 5 mgr number, 6 hiredate date, 7 sal number, 8 comm number, 9 deptno number(2) references dept(deptno)
10 )
11 cluster emp_dept_cluster(deptno)
12 /
Table created.
|
ops$tkyte@ORA10GR1> begin
2 for x in ( select * from scott.dept )
3 loop
4 insert into dept 5 values ( x.deptno, x.dname, x.loc ); 6 insert into emp 7 select * 8 from scott.emp 9 where deptno = x.deptno;
10 end loop;
11 end;
12 /
PL/SQL procedure successfully completed. |
ops$tkyte@ORA10GR1> create table emp
2 ( empno number primary key,
3 ename varchar2(10), 4 job varchar2(9), 5 mgr number, 6 hiredate date, 7 sal number, 8 comm number, 9 deptno number(2) references dept(deptno), 10 data char(1000)
11 )
12 cluster emp_dept_cluster(deptno)
13 /
Table created.
|
ops$tkyte@ORA10GR1> insert into dept 2 select * from scott.dept;
4 rows created.
ops$tkyte@ORA10GR1> insert into emp 2 select emp.*, '*' from scott.emp;
14 rows created.
ops$tkyte@ORA10GR1> select dept_blk, emp_blk, 2 case when dept_blk <> emp_blk then '*' end flag,
3 deptno
4 from ( 5 select dbms_rowid.rowid_block_number(dept.rowid) dept_blk, 6 dbms_rowid.rowid_block_number(emp.rowid) emp_blk,
7 dept.deptno
8 from emp, dept 9 where emp.deptno = dept.deptno
10 )
11 order by deptno
12 /
DEPT_BLK EMP_BLK F DEPTNO
---------------- -------------- - ---------- 4792 4788 * 10 4792 4788 * 10 4792 4791 * 10 4792 4788 * 20 4792 4788 * 20 4792 4792 20 4792 4792 20 4792 4791 * 20 4792 4788 * 30 4792 4792 30 4792 4792 30 4792 4792 30 4792 4792 30 4792 4788 * 30
14 rows selected.
|
ops$tkyte@ORA10GR1> begin
2 for x in ( select * from scott.dept )
3 loop
4 insert into dept 5 values ( x.deptno, x.dname, x.loc ); 6 insert into emp 7 select emp.*, 'x' 8 from scott.emp 9 where deptno = x.deptno; 10 end loop;
11 end;
12 /
PL/SQL procedure successfully completed. ops$tkyte@ORA10GR1> select dept_blk, emp_blk, 2 case when dept_blk <> emp_blk then '*' end flag,
3 deptno
4 from (
5 select dbms_rowid.rowid_block_number(dept.rowid) dept_blk, 6 dbms_rowid.rowid_block_number(emp.rowid) emp_blk,
7 dept.deptno
8 from emp, dept 9 where emp.deptno = dept.deptno
10 )
11 order by deptno
12 /
DEPT_BLK EMP_BLK F DEPTNO
---------- ---------- - ---------- 12 12 10 12 12 10 12 12 10 11 11 20 11 11 20 11 11 20 11 12 * 20 11 11 20 10 10 30 10 10 30 10 10 30 10 10 30 10 10 30 10 11 * 30
14 rows selected.
|
ops$tkyte@ORA10GR1> select rowid from emp
2 intersect
3 select rowid from dept;
ROWID
------------------
AAAOniAAJAAAAAKAAA
AAAOniAAJAAAAAKAAB
AAAOniAAJAAAAALAAA
AAAOniAAJAAAAAMAAA
|
sys@ORA10GR1> break on cluster_name sys@ORA10GR1> select cluster_name, table_name 2 from user_tables 3 where cluster_name is not null 4 order by 1;
CLUSTER_NAME TABLE_NAME
------------------------------ ------------------------------
C_COBJ# CCOL$
CDEF$
C_FILE#_BLOCK# UET$
SEG$
C_MLOG# MLOG$
SLOG$
C_OBJ# ICOL$
CLU$
COL$
TYPE_MISC$
VIEWTRCOL$
ATTRCOL$
SUBCOLTYPE$
COLTYPE$
LOB$
TAB$
IND$
ICOLDEP$
OPQTYPE$
REFCON$
LIBRARY$
NTAB$
C_OBJ#_INTCOL# HISTGRM$
C_RG# RGROUP$
RGCHILD$
C_TOID_VERSION# TYPE$
COLLECTION$
METHOD$
RESULT$
PARAMETER$
ATTRIBUTE$
C_TS# TS$
FET$
C_USER# USER$
TSQ$
SMON_SCN_TO_TIME SMON_SCN_TIME
36 rows selected.
|
q 如果预料到聚簇中的表会大量修改:必须知道,索引聚簇会对DML的性能产生某种负面影响(特别是INSERT语句)。管理聚簇中的数据需要做更多的工作。
q 如果需要对聚簇中的表执行全表扫描:不只是必须对你的表中的数据执行全面扫描,还必须对(可能的)多个表中的数据进行全面扫描。由于需要扫描更多的数据,所以全表扫描耗时更久。
q 如果你认为需要频繁地TRUNCATE和加载表:聚簇中的表不能截除。这是显然的,因为聚簇在一个块上存储了多个表,必须删除聚簇表中的行。
散列聚簇表(Hash clustered table)在概念上与前面介绍的索引聚簇表非常相似,只有一个主要区别:聚簇键索引被一个散列函数所取代。表中的数据就是索引;这里没有物理索引。Oracle会取得一行的键值,使用每个内部函数或者你提供的每个函数对其计算散列,然后使用这个散列值得出数据应该在磁盘上的哪个位置。不过,使用散列算法来定位数据有一个副作用,如果不向表增加一个传统的索引,将无法对散列聚簇中的表完成区间扫描。在一个索引聚簇中,如果有以下查询:
select * from emp where deptno between 10 and 20 |
ops$tkyte@ORA10GR1> create cluster hash_cluster
2 ( hash_key number )
3 hashkeys 1000
4 size 8192
5 tablespace mssm
6 /
Cluster created.
ops$tkyte@ORA10GR1> exec show_space( 'HASH_CLUSTER', user, 'CLUSTER' )
Free Blocks............................. 0
Total Blocks............................ 1,024
Total Bytes............................. 8,388,608
Total MBytes............................ 8
Unused Blocks........................... 14
Unused Bytes............................ 114,688
Last Used Ext FileId.................... 9
Last Used Ext BlockId................... 1,033
Last Used Block......................... 114
PL/SQL procedure successfully completed. |
Ops$tkyte@ORA10GR1> create table hashed_table
2 ( x number, data1 varchar2(4000), data2 varchar2(4000) )
3 cluster hash_cluster(x);
Table created.
|
ops$tkyte@ORA10GR1> create cluster hash_cluster
2 ( hash_key number )
3 hashkeys 75000
4 size 150
5 /
Cluster created.
ops$tkyte@ORA10GR1> create table t_hashed
2 cluster hash_cluster(object_id)
3 as
4 select *
5 from all_objects
6 /
Table created.
ops$tkyte@ORA10GR1> alter table t_hashed add constraint 2 t_hashed_pk primary key(object_id)
3 /
Table altered.
ops$tkyte@ORA10GR1> begin
2 dbms_stats.gather_table_stats( user, 'T_HASHED', cascade=>true );
3 end;
4 /
PL/SQL procedure successfully completed.
|
ops$tkyte@ORA10GR1> create table t_heap
2 as
3 select *
4 from t_hashed
5 /
Table created.
ops$tkyte@ORA10GR1> alter table t_heap add constraint 2 t_heap_pk primary key(object_id)
3 /
Table altered.
ops$tkyte@ORA10GR1> begin
2 dbms_stats.gather_table_stats( user, 'T_HEAP', cascade=>true );
3 end;
4 /
PL/SQL procedure successfully completed.
|
ops$tkyte@ORA10GR1> create or replace package state_pkg
2 as
3 type array is table of t_hashed.object_id%type;
4
g_data array;
5 end;
6 /
Package created.
ops$tkyte@ORA10GR1> begin
2 select object_id bulk collect into state_pkg.g_data
3 from t_hashed
4 order by dbms_random.random;
5 end;
6 /
PL/SQL procedure successfully completed.
|
ops$tkyte@ORA10GR1> declare
2
l_rec t_hashed%rowtype;
3 begin
4 for i in 1 .. state_pkg.g_data.count
5 loop
6 select * into l_rec from t_hashed
7 where object_id = state_pkg.g_data(i);
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
|
ops$tkyte@ORA10GR1> exec runstats_pkg.rs_stop(10000);
Run1 ran in 263 hsecs
Run2 ran in 268 hsecs
run 1 ran in 98.13% of the time
Name Run1 Run2 Diff
LATCH.cache buffers chains 99,891 148,031 48,140
STAT...Cached Commit SCN refer 48,144 0 -48,144 STAT...no work - consistent re 48,176 0 -48,176
STAT...cluster key scans 48,176 0 -48,176
STAT...cluster key scan block 48,176 0 -48,176
STAT...table fetch by rowid 0 48,176 48,176
STAT...rows fetched via callba 0 48,176 48,176 STAT...buffer is not pinned co 48,176 96,352 48,176
STAT...index fetch by key 0 48,176 48,176
STAT...session logical reads 48,901 145,239 96,338
STAT...consistent gets 48,178 144,530 96,352
STAT...consistent gets from ca 48,178 144,530 96,352 STAT...consistent gets - exami 1 144,529 144,528 Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
347,515 401,961 54,446 86.45%
|
SELECT * FROM T_HASHED WHERE OBJECT_ID = :B1
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 48174 4.77 4.83 0 2 0 0 Fetch 48174 1.50 1.46 0 48174 0 48174 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 96349 6.27 6.30 0 48176 0 48174 Rows Row Source Operation
------- ---------------------------------------------------
48174 TABLE ACCESS HASH T_HASHED (cr=48174 pr=0 pw=0 time=899962 us)
********************************************************************************
SELECT * FROM T_HEAP WHERE OBJECT_ID = :B1
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 48174 5.37 5.02 0 0 0 0 Fetch 48174 1.36 1.32 0 144522 0 48174 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 96349 6.73 6.34 0 144522 0 48174 Rows Row Source Operation
------- ---------------------------------------------------
48174 TABLE ACCESS BY INDEX ROWID T_HEAP (cr=144522 pr=0 pw=0 time=1266695 us) 48174 INDEX UNIQUE SCAN T_HEAP_PK (cr=96348 pr=0 pw=0 time=700017 us)(object ... |
q 散列聚簇完成的I/O(查询列)少得多。这正是我们期望的。查询只是取随机的OBJECT_ID,对其完成散列,然后找到块。散列聚簇至少要做一次I/O来得到数据。有索引的传统表则必须完成索引扫描,然后要根据rowid访问表,才能得到同样的答案。在这个例子中,索引表必须至少完成3个I/O才能得到数据。
q 不论用于什么目的,散列聚簇查询与索引查询所用的CPU是一样的,尽管它访问缓存区缓存的次数只是后者的1/3。同样,这也在预料之中。执行散列是一个CPU相当密集的操作,执行索引查询则是一个I/O密集的操作,这里要做个权衡。不过,随着用户数的增加,可以想见,散列聚簇查询能更好地扩缩,因为要想很好地扩缩,就不能太过频繁地访问缓存区缓存。
散列聚簇有一个特例,称为单表散列聚簇(single table hash cluster)。这是前面介绍的一般散列聚簇的优化版本。它一次只支持聚簇中的一个表(必须DROP(删除)单表散列聚簇中现有的表,才能在其中创建另一个表)。另外,如果散列键和数据行之间存在一对一的映射,访问行还会更快一些。这种散列聚簇是为以下情况设计的:如果你想按主键来访问一个表,但是不关心其他表是否与这个表聚簇在一起存储。如果你需要按EMPNO快速地访问员工记录,可能就需要一个单表散列聚簇。我在一个单表散列聚簇上执行了前面的测试,发现性能比一般的散列聚簇还要好。不过,甚至还可以将这个例子更进一步,由于Oracle允许你编写自己的散列函数(而不是使用Oracle提供的默认散列函数),所以能利用这一点。不过,你只能使用表中可用的列,而且编写自己的散列函数时只能使用Oracle的内置函数(例如,不能有PL/SQL代码)。由于上例中OBJECT_ID是一个介于1~75,000之间的数,充分利用这一点,我建立了自己的“散列函数”:就是OBJECT_ID本身。采用这种方式,可以保证绝对不会有散列冲突。综合在一起,我如下创建一个单表散列聚簇(有我自己的散列函数):
ops$tkyte@ORA10GR1> create cluster hash_cluster
2 ( hash_key number(10) )
3 hashkeys 75000
4 size 150
5 single table
6 hash is HASH_KEY
7 /
Cluster created.
|
这里只是增加了关键字SINGLE TABLE,使之作为一个单步散列聚簇。在这种情况下,我的散列函数就是HASH_KEY聚簇键本身。这是一个SQL函数,所以如果我愿意,也可以使用trunc(mod(hash_key/324+278,555)/abs(hash_key+1))(并不是说这是一个好的散列函数,这只是说明,只要我们愿意,完全可以使用一个复杂的函数)。我使用了NUMBER(10)而不是NUMBER,这是因为散列值必须是一个整数,所以不能有任何小数部分。下面,在这个表单散列聚簇中创建表:
ops$tkyte@ORA10GR1> create table t_hashed
2 cluster hash_cluster(object_id)
3 as
4 select OWNER, OBJECT_NAME, SUBOBJECT_NAME,
5 cast( OBJECT_ID as number(10) ) object_id,
6 DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
7 LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY,
8 GENERATED, SECONDARY
9 from all_objects
10 /
Table created.
|
Run1 ran in 224 hsecs
Run2 ran in 269 hsecs
run 1 ran in 83.27% of the time
Name Run1 Run2 Diff
STAT...index fetch by key 0 48,178 48,178
STAT...buffer is not pinned co 48,178 96,356 48,178
STAT...table fetch by rowid 0 48,178 48,178
STAT...cluster key scans 48,178 0 -48,178
STAT...session logical reads 48,889 145,245 96,356
STAT...consistent gets 48,184 144,540 96,356
STAT...consistent gets from ca 48,184 144,540 96,356 STAT...consistent gets - exami 48,184 144,540 96,356
LATCH.cache buffers chains 51,663 148,019 96,356
Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 298,987 402,085 103,098 74.36%
PL/SQL procedure successfully completed.
|
SELECT * FROM T_HASHED WHERE OBJECT_ID = :B1
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 48178 4.45 4.52 0 2 0 0 Fetch 48178 0.67 0.82 0 48178 0 48178 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 96357 5.12 5.35 0 48180 0 48178 Rows Row Source Operation
------- ---------------------------------------------------
48178 TABLE ACCESS HASH T_HASHED (cr=48178 pr=0 pw=0 time=551123 us)
********************************************************************************
SELECT * FROM T_HEAP WHERE OBJECT_ID = :B1
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 48178 5.38 4.99 0 0 0 0 Fetch 48178 1.25 1.65 0 144534 0 48178 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 96357 6.63 6.65 0 144534 0 48178 Rows Row Source Operation
------- ---------------------------------------------------
48178 TABLE ACCESS BY INDEX ROWID T_HEAP (cr=144534 pr=0 pw=0 time=1331049 us) 48178 INDEX UNIQUE SCAN T_HEAP_PK (cr=96356 pr=0 pw=0 time=710295 us)(object... |
q 散列聚簇一开始就要分配空间。Oracle根据你的HASHKEYS和SIZE来计算HASHKEYS/trunc(blocksize/SIZE),立即分配空间,并完成格式化,一旦将第一个表放入这个聚簇中,任何全面扫描都会命中每一个已分配的块。在这方面,它与其他的所有表都不同。
q 散列聚簇中的HASHKEY数是固定大小的。除非重新聚簇,否则不能改变散列表的大小。这并不会限制聚簇中能存储的数据量,它只是限制了能为这个聚簇生成的惟一散列键的个数。如果HASHKEY值设置得太低,可能因为无意的散列冲突影响性能。
q 不能在聚簇键上完成区间扫描。诸如WHERE cluster_key BETWEEN 50 AND 60谓词条件不能使用散列算法。介于50~60之间的可能值有无限多个,服务器必须生成所有可能的值,并分别计算散列,来查看相应位置是否有数据。这是不可能的。如果你在一个聚簇键上使用区间扫描,而且没有使用传统索引,实际上会全面扫描这个聚簇。
Select *
From t
Where KEY=:x
Order by SORTED_COLUMN
|
ops$tkyte@ORA10G> select cust_id, order_dt, order_number
2 from cust_orders
3 order by cust_id, order_dt;
CUST_ID ORDER_DT ORDER_NUMBER
------------- -------------------------------------------- -------------------------- 1 31-MAR-05 09.13.57.000000 PM 21453 11-APR-05 08.30.45.000000 AM 21454 28-APR-05 06.21.09.000000 AM 21455 2 08-APR-05 03.42.45.000000 AM 21456 19-APR-05 08.59.33.000000 AM 21457 27-APR-05 06.35.34.000000 AM 21458 30-APR-05 01.47.34.000000 AM 21459
7 rows selected.
|
ops$tkyte@ORA10G> CREATE CLUSTER shc
2 (
3 cust_id NUMBER,
4 order_dt timestamp SORT
5 )
6 HASHKEYS 10000
7 HASH IS cust_id
8 SIZE 8192
9 /
Cluster created.
|
ops$tkyte@ORA10G> 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 /
Table created.
|
ops$tkyte@ORA10G> set autotrace traceonly explain ops$tkyte@ORA10G> variable x number ops$tkyte@ORA10G> select cust_id, order_dt, order_number
2 from cust_orders
3 where cust_id = :x
4 order by order_dt;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=0 Card=4 Bytes=76) 1 0 TABLE ACCESS (HASH) OF 'CUST_ORDERS' (CLUSTER (HASH)) ops$tkyte@ORA10G> select job, hiredate, empno
2 from scott.emp
3 where job = 'CLERK'
4 order by hiredate;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=3 Bytes=60) 1 0 SORT (ORDER BY) (Cost=3 Card=3 Bytes=60) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=3 ... 3 2 INDEX (RANGE SCAN) OF 'JOB_IDX' (INDEX) (Cost=1 Card=3) ops$tkyte@ORA10G> set autotrace off |
ops$tkyte@ORA10GR1> create table dept
2 (deptno number(2) primary key,
3 dname varchar2(14),
4 loc varchar2(13)
5 );
Table created.
ops$tkyte@ORA10GR1> create table emp
2 (empno number(4) primary key,
3 ename varchar2(10),
4 job varchar2(9),
5 mgr number(4) references emp,
6 hiredate date,
7 sal number(7, 2),
8 comm number(7, 2),
9 deptno number(2) references dept
10 );
Table created.
|
ops$tkyte@ORA10GR1> 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 /
Type created.
ops$tkyte@ORA10GR1> create or replace type emp_tab_type
2 as table of emp_type
3 /
Type created.
|
ops$tkyte@ORA10G> 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;
Table created.
ops$tkyte@ORA10G> alter table emps_nt add constraint
2 emps_empno_unique unique(empno)
3 /
Table altered.
|
ops$tkyte@ORA10G> alter table emps_nt add constraint mgr_fk 2 foreign key(mgr) references emps_nt(empno);
alter table emps_nt add constraint mgr_fk
*
ERROR at line 1:
ORA-30730: referential constraint not allowed on nested table column |
ops$tkyte@ORA10G> 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 rows created.
|
q 只创建了“4”行。DEPT_AND_EMP表中确实只有4行。没有独立地存在14个EMP行。
q 这个语法开始有点奇怪了。CAST和MULTISET是大多数人从来没有用过的语法。处理数据库中的对象关系组件时,你会看到很多奇怪的语法。MULTISET关键字用于告诉Oracle:这个子查询返回多行(SELECT列表中的子查询先前限制为只返回一行)。CAST用于指示Oracle:要把返回的结果集处理为一个集合类型,在这里,我们将MULTISET强制转换(CAST)为一个EMP_TAB_TYPE。CAST是一个通用的例程, 并不仅限于在集合中使用。例如,如果想从EMP中将EMPNO列获取为VARCHAR2(20)而不是NUMBER(4)类型,可以使用以下查询:SELECT CAST(EMPNO AS VARCHAR2(20)) E FROM EMP。
ops$tkyte@ORA10G> 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,
---------- -------------- ---------------- ---------------------------------------------------
10 ACCOUNTING NEW YORK EMP_TAB_TYPE(EMP_TYPE(7782,
'CLARK', 'MANAGER', 7839, '0 9-JUN-81', 2450, NULL), EMP_ TYPE(7839, 'KING', 'PRESIDEN T', NULL, '17-NOV-81', 5000, NULL), EMP_TYPE(7934, 'MILL ER', 'CLERK', 7782, '23-JAN- 82', 1300, NULL)) |
ops$tkyte@ORA10G> 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-JUN-81 2450
10 ACCOUNTING 7839 KING PRESIDENT 17-NOV-81 5000
10 ACCOUNTING 7934 MILLER CLERK 7782 23-JAN-82 1300 20 RESEARCH 7369 SMITH CLERK 7902 17-DEC-80 800 20 RESEARCH 7566 JONES MANAGER 7839 02-APR-81 2975 20 RESEARCH 7788 SCOTT ANALYST 7566 09-DEC-82 3000
20 RESEARCH 7876 ADAMS CLERK 7788 12-JAN-83 1100
20 RESEARCH 7902 FORD ANALYST 7566 03-DEC-81 3000 30 SALES 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 SALES 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 SALES 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 SALES 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 SALES 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 SALES 7900 JAMES CLERK 7698 03-DEC-81 950
14 rows selected.
|
ops$tkyte@ORA10G> update 2 table( select emps 3 from dept_and_emp 4 where deptno = 10
5 )
6 set comm = 100
7 /
3 rows updated.
|
ops$tkyte@ORA10G> update 2 table( select emps 3 from dept_and_emp 4 where deptno = 1
5 )
6 set comm = 100
7 /
update
*
ERROR at line 1:
ORA-22908: reference to NULL table value
ops$tkyte@ORA10G> update 2 table( select emps 3 from dept_and_emp 4 where deptno > 1
5 )
6 set comm = 100
7 /
table( select emps
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row |
ops$tkyte@ORA10G> insert into table 2 ( select emps from dept_and_emp where deptno = 10 )
3 values
4 ( 1234, 'NewEmp', 'CLERK', 7782, sysdate, 1200, null );
1 row created.
ops$tkyte@ORA10G> delete from table 2 ( select emps from dept_and_emp where deptno = 20 ) 3 where ename = 'SCOTT';
1 row deleted.
ops$tkyte@ORA10G> select d.dname, e.empno, ename 2 from dept_and_emp d, table(d.emps) e 3 where d.deptno in ( 10, 20 );
DNAME EMPNO ENAME
-------------- ---------- ----------
ACCOUNTING 7782 CLARK
ACCOUNTING 7839 KING
ACCOUNTING 7934 MILLER
RESEARCH 7369 SMITH
RESEARCH 7566 JONES
RESEARCH 7876 ADAMS
RESEARCH 7902 FORD
ACCOUNTING 1234 NewEmp
8 rows selected.
|
ops$tkyte@ORA10G> SELECT /*+NESTED_TABLE_GET_REFS*/
2 NESTED_TABLE_ID,SYS_NC_ROWINFO$
3 FROM "OPS$TKYTE"."EMPS_NT"
4 /
NESTED_TABLE_ID SYS_NC_ROWINFO$(EMPNO, EN
-------------------------------- -------------------------
F60DEEE0FF7D7BC1E030007F01001321 EMP_TYPE(7782, 'CLARK', '
MANAGER', 7839, '09-JUN-8 1', 2450, 100)
F60DEEE0FF7D7BC1E030007F01001321 EMP_TYPE(7839, 'KING', 'P
RESIDENT', NULL, '17-NOV- 81', 5000, 100) ... |
ops$tkyte@ORA10G> desc emps_nt Name Null? Type
----------------------------- -------- --------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
|
sys@ORA10G> select name 2 from sys.col$ 3 where obj# = ( select object_id 4 from dba_objects 5 where object_name = 'DEPT_AND_EMP' 6 and owner = 'OPS$TKYTE' )
7 /
NAME
------------------------------
DEPTNO
DNAME
EMPS
LOC
SYS_NC0000400005$
|
ops$tkyte@ORA10G> select SYS_NC0000400005$ from dept_and_emp;
SYS_NC0000400005$
--------------------------------
F60DEEE0FF887BC1E030007F01001321
F60DEEE0FF897BC1E030007F01001321
F60DEEE0FF8A7BC1E030007F01001321
F60DEEE0FF8B7BC1E030007F01001321
|
ops$tkyte@ORA10G> select /*+ nested_table_get_refs */ empno, ename
2 from emps_nt where ename like '%A%';
EMPNO ENAME
---------- ----------
7782 CLARK
7876 ADAMS
7499 ALLEN
7521 WARD
7654 MARTIN
7698 BLAKE
7900 JAMES
7 rows selected.
ops$tkyte@ORA10G> update /*+ nested_table_get_refs */ emps_nt
2 set ename = initcap(ename);
14 rows updated.
ops$tkyte@ORA10G> select /*+ nested_table_get_refs */ empno, ename
2 from emps_nt where ename like '%a%';
EMPNO ENAME
---------- ----------
7782 Clark
7876 Adams
7521 Ward
7654 Martin
7698 Blake
7900 James
6 rows selected.
|
ops$tkyte@ORA10G> select d.deptno, d.dname, emp.*
2 from dept_and_emp D, table(d.emps) emp
3 /
|
ops$tkyte@ORA10G> 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;
Table created.
ops$tkyte@ORA10G> alter table emps_nt add constraint emps_empno_unique
2 unique(empno)
3 /
Table altered.
|
ops$tkyte@ORA10G> begin
2 dbms_metadata.set_transform_param
3 ( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false );
4 end;
5 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA10G> select dbms_metadata.get_ddl( 'TABLE', 'DEPT_AND_EMP' ) from dual;
DBMS_METADATA.GET_DDL('TABLE','DEPT_AND_EMP')
-------------------------------------------------------------------------------
CREATE TABLE "OPS$TKYTE"."DEPT_AND_EMP" ( "DEPTNO" NUMBER(2,0), "DNAME" VARCHAR2(14), "LOC" VARCHAR2(13), "EMPS" "OPS$TKYTE"."EMP_TAB_TYPE" , PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "USERS" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS"
NESTED TABLE "EMPS" STORE AS "EMPS_NT" (PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
TABLESPACE "USERS" ) RETURN AS VALUE
|
到此为止,只有一个新内容,即RETURN AS VALUE。这个选项用于描述如何向客户应用返回嵌套表。默认情况下,Oracle会按值把嵌套表返回给客户:具体数据会随各行传输。这个选项也可以设置为RETURN AS LOCATOR,这说明客户会得到指向数据的一个指针,而不是数据本身。当且仅当客户对这个指针解除引用(dereference)时,才会把数据传输给客户。因此,如果你相信客户通常不会查看对应各个父行的嵌套表(不会查看这些嵌套表中的行),就可以返回一个locator而不是值,这样可以节省网络往返通信开销。例如,如果你的客户应用要显示部门列表,当用户双击一个部门时,客户应用会显示出员工信息,你就可以考虑使用locator。这是因为通常都不会查看详细信息,查看详细信息的情况是例外,而不是一般情况。
那么,我们还能对嵌套表做些什么呢?首先,NESTED_TABLE_ID列必须建立索引。因为我们总是从父表联结到子表来访问嵌套表,我们确实需要这个索引。可以使用CREATE INDEX对该列建立索引,但是最好的解决方案是使用一个IOT来存储嵌套表。嵌套表也是一个适用IOT的绝好例子。它会按NESTED_TABLE_ID将子行物理地共同存储在一块(所以用最少的物理I/O就能完成表的获取),这样就不必在RAW(16)列上建立冗余的索引。再前进一步,由于NESTED_TABLE_ID就是IOT主键的第一列,还应该加入索引键压缩来避免冗余的NESTED_TABLE_ID(否则会重复存储)。另外,我们还可以在CREATE TABLE命令中加入EMPNO列的UNIQUE和NOT NULL约束。因此,对于前面的CREATE TABLE,可以稍作修改,如下所示:
ops$tkyte@ORA10G> CREATE TABLE "OPS$TKYTE"."DEPT_AND_EMP" 2 ("DEPTNO" NUMBER(2, 0), 3 "DNAME" VARCHAR2(14), 4 "LOC" VARCHAR2(13), 5 "EMPS" "EMP_TAB_TYPE") 6 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING 7 STORAGE(INITIAL 131072 NEXT 131072 8 MINEXTENTS 1 MAXEXTENTS 4096 9 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 10 BUFFER_POOL DEFAULT) 11 TABLESPACE "USERS" 12 NESTED TABLE "EMPS" 13 STORE AS "EMPS_NT" 14 ( (empno NOT NULL, unique (empno), primary key(nested_table_id,empno)) 15 organization index compress 1 ) 16 RETURN AS VALUE
17 /
Table created.
|
q 这样会增加不必要的RAW(16)列存储开销。父表和子表都有这个额外的列。父表对于其中的各个嵌套表列都有一个额外的16字节RAW字段。由于父表通常已经有一个主键(在我们这个例子中就是DEPTNO),所以完全可以在子表中使用这个键,而不必使用一个系统生成的键。
q 这会在父表上增加另一个惟一约束(相应地带领不必要的开销),而父表中通常已经有一个惟一约束。
q 如果不使用NESTED_TABLE_GET_REFS(这个方法未得到公开支持),嵌套表本身使用起来并不2.如果是查询,可以通过消除嵌套来访问嵌套表,但是如果是大批量更新,则无法简单地消除嵌套。在实际中,表往往都会“自己”查询自己,我还没有见过这方面的例外。
ops$tkyte@ORA10G> create global temporary table temp_table_session 2 on commit preserve rows
3 as
4 select * from scott.emp where 1=0
5 /
Table created.
|
ops$tkyte@ORA10G> create global temporary table temp_table_transaction 2 on commit delete rows
3 as
4 select * from scott.emp where 1=0
5 /
Table created.
|
ops$tkyte@ORA10G> insert into temp_table_session select * from scott.emp;
14 rows created.
ops$tkyte@ORA10G> insert into temp_table_transaction select * from scott.emp;
14 rows created.
|
ops$tkyte@ORA10G> select session_cnt, transaction_cnt 2 from ( select count(*) session_cnt from temp_table_session ), 3 ( select count(*) transaction_cnt from temp_table_transaction );
SESSION_CNT TRANSACTION_CNT
---------------------- -----------------------------
14 14
ops$tkyte@ORA10G> commit; |
ops$tkyte@ORA10G> select session_cnt, transaction_cnt 2 from ( select count(*) session_cnt from temp_table_session ), 3 ( select count(*) transaction_cnt from temp_table_transaction );
SESSION_CNT TRANSACTION_CNT
----------- ---------------
14 0
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.3.0
With the Partitioning, OLAP and Data Mining options ops$tkyte@ORA10G> connect /
Connected.
|
ops$tkyte@ORA10G> select session_cnt, transaction_cnt 2 from ( select count(*) session_cnt from temp_table_session ), 3 ( select count(*) transaction_cnt from temp_table_transaction );
SESSION_CNT TRANSACTION_CNT
----------- ---------------
0 0
|
q 将所有全局临时表只创建一次,作为应用安装的一部分,就像是创建永久表一样。
q 在你的过程中,只需执行INSERT INTO TEMP(X, Y, Z) SELECT X, Y, Z FROM SOME_TABLE。
q 不能有引用完整性约束。临时表不能作为外键的目标,也不能在临时表中定义外键。
q 不能有NESTED TABLE类型的列。在Oracle 9i及以前版本中,VARRAY类型的列也不允许;不过Oracle 10g中去掉了这个限制。
q 不能是IOT。
q 不能在任何类型的聚簇中。
q 不能分区。
q 不能通过ANALYZE表命令生成统计信息。
不过,我遇到的问题是,由于会分析永久表,所以使用了CBO。但是临时表上没有统计信息(尽管可以分析临时表,但不会收集统计信息),因此CBO会对它做出很多“猜测”。作为一名开发人员,我知道可能的平均行数、数据的分布、查询选择的列等。我需要一种方法来告诉优化器这些更准确的猜测。可以有3中种方法向优化器提供关于全局临时表的统计信息。一种方法是通过动态采样(只是Oracle9i Release 2及以上版本中新增的特性),另一种方法是使用DBMS_STATS包,它有两种做法。下面首先来看动态采样。
动态采样(dynamic sampling)是优化器的一种功能,硬解析一个查询时,会扫描数据库中的段(采样),收集有用的统计信息,来完成这个特定查询的优化。这与硬解析期间完成一个“缩型收集统计”命令很类似。Oracle 10g中大量使用了动态采样,因为默认设置已经从level 1提升到level 2,采用level 2,优化器在结算查询计划之前,会对优化器处理的查询中引用的所有未分析的对象完成动态采样。9i Release2中则设置为level 1,所以动态采样的使用少得多。在Oracle9i Release 2中可以使用一个ALTER SESSION|SYSTEM命令,从而能有Oracle 10g默认行为,或者可以使用动态采样提示,如下:
ops$tkyte@ORA9IR2> create global temporary table gtt
2 as
3 select * from scott.emp where 1=0;
Table created.
ops$tkyte@ORA9IR2> insert into gtt select * from scott.emp;
14 rows created.
ops$tkyte@ORA9IR2> set autotrace traceonly explain ops$tkyte@ORA9IR2> select /*+ first_rows */ * from gtt;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=17 Card=8168 Bytes... 1 0 TABLE ACCESS (FULL) OF 'GTT' (Cost=17 Card=8168 Bytes=710616) ops$tkyte@ORA9IR2> select /*+ first_rows dynamic_sampling(gtt 2) */ * from gtt;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=17 Card=14 Bytes=1218) 1 0 TABLE ACCESS (FULL) OF 'GTT' (Cost=17 Card=14 Bytes=1218)
ops$tkyte@ORA9IR2> set autotrace off
|
在此,我们在这个查询中把表GTT的动态采样设置为level 2.在此之前,优化器猜测会从表GTT返回8,168行。通过使用动态采样,估计的基数会与实际更为接近(这会得到总体上更好的查询计划)。使用level 2设置,优化器会很快地扫描表,对表的真实大小得出更实际的估计。在Oracle 10g中,这应该不成问题,因为默认就会发生动态采样:
ops$tkyte@ORA10G> create global temporary table gtt
2 as
3 select * from scott.emp where 1=0;
Table created.
ops$tkyte@ORA10G> insert into gtt select * from scott.emp;
14 rows created.
ops$tkyte@ORA10G> set autotrace traceonly explain ops$tkyte@ORA10G> select * from gtt;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=14 Bytes=1218) 1 0 TABLE ACCESS (FULL) OF 'GTT' (TABLE (TEMP)) (Cost=2 Card=14 Bytes=1218) ops$tkyte@ORA10G> set autotrace off |
ops$tkyte@ORA10G> create table emp as select * from scott.emp;
Table created.
ops$tkyte@ORA10G> create global temporary table gtt1 ( x number )
2 on commit preserve rows;
Table created.
ops$tkyte@ORA10G> create global temporary table gtt2 ( x number )
2 on commit delete rows;
Table created.
ops$tkyte@ORA10G> insert into gtt1 select user_id from all_users;
38 rows created.
ops$tkyte@ORA10G> insert into gtt2 select user_id from all_users;
38 rows created.
ops$tkyte@ORA10G> exec dbms_stats.gather_schema_stats( user );
PL/SQL procedure successfully completed.
ops$tkyte@ORA10G> select table_name, last_analyzed, num_rows from user_tables;
TABLE_NAME LAST_ANAL NUM_ROWS
------------------------------ --------- ----------
EMP 01-MAY-05 14
GTT1
GTT2
|
ops$tkyte@ORA10G> insert into gtt2 select user_id from all_users;
38 rows created.
ops$tkyte@ORA10G> exec dbms_stats.gather_schema_stats( user, gather_temp=>TRUE ); PL/SQL procedure successfully completed. ops$tkyte@ORA10G> select table_name, last_analyzed, num_rows from user_tables; TABLE_NAME LAST_ANAL NUM_ROWS ------------------------------ --------- ---------- EMP 01-MAY-05 14 GTT1 01-MAY-05 38 GTT2 01-MAY-05 0 |
注意,ON COMMIT PRESERVE ROWS表会有正确的统计,但是ON COMMIT DELETE ROWS表没有。DBMS_STATS将提交,而这会擦除ON COMMIT DELETE ROWS表中的所有信息。不过,要注意,现在GTT2确实有统计信息了,这本身并不好,因为统计信息太离谱了!运行时表居然只有0行,这实在是让人怀疑。所以,如果使用这种方法,要注意两点:
q 要保证在收集统计信息的会话中用代表性数据填充全局临时表。如果做不到,在DBMS_STATS看来它们就是空的。
q 如果有ON COMMIT DELETE ROWS全局临时表,就不应该使用这种方法,因为这样会收集到不正确的值。
对于ON COMMIT PRESERVE ROWS全局临时表,还可以采用第二种技术:直接在表上使用GATHER_TABLE_STATS。你要像我们刚才那样填充全局临时表,然后在这个全局临时表上执行GATHER_TABLE_STATS。注意还是像前面一样,对于ON COMMIT DELETE ROWS全局临时表,这种技术还是不能用,同样是因为存在前面所述的问题。
ops$tkyte@ORA10G> create global temporary table t ( x int, y varchar2(100) );
Table created.
ops$tkyte@ORA10G> 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 procedure successfully completed. ops$tkyte@ORA10G> 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 |
create table t ( x int, y date, z varchar2(25) ); |
create table t of Some_Type; |
ops$tkyte@ORA10G> 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 /
Type created.
ops$tkyte@ORA10G> 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 /
Type created.
ops$tkyte@ORA10G> create table people of person_type
2 /
Table created.
ops$tkyte@ORA10G> desc people Name Null? Type ---------------------------------------- -------- ----------------------------
NAME VARCHAR2(30)
DOB DATE
HOME_ADDRESS ADDRESS_TYPE
WORK_ADDRESS ADDRESS_TYPE
|
ops$tkyte@ORA10G> insert into people values ( 'Tom', '15-mar-1965', 2 address_type( 'Reston', '123 Main Street', 'Va', '45678' ), 3 address_type( 'Redwood', '1 Oracle Way', 'Ca', '23456' ) );
1 row created.
ops$tkyte@ORA10G> select * from people; NAME DOB HOME_ADDRESS(CITY, S WORK_ADDRESS(CITY, S
----- --------- -------------------- --------------------
Tom 15-MAR-65 ADDRESS_TYPE('Reston ADDRESS_TYPE('Redwoo ', '123 Main Street' d', '1 Oracle Way', , 'Va', 45678) 'Ca', 23456) ops$tkyte@ORA10G> select name, p.home_address.city from people p;
NAME HOME_ADDRESS.CITY
----- ------------------------------
Tom Reston
|
ops$tkyte@ORA10G> select name, segcollength
2 from sys.col$
3 where obj# = ( select object_id 4 from user_objects 5 where object_name = 'PEOPLE' )
6 /
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.
|
q SYS_NC_OID$:这是表中系统生成的对象ID。这是一个惟一的RAW(16)列。这个列上有一个惟一约束,而且在这个列上还创建了一个相应的惟一索引。
q SYS_NC_ROWINFO$:这是嵌套表中已经研究过的那个“神奇”函数。如果从表中选择这个列,它会把整行作为一列返回:
ops$tkyte@ORA10G> select sys_nc_rowinfo$ from people; SYS_NC_ROWINFO$(NAME, DOB, HOME_ADDRESS(CITY,STREET,STATE,ZIP), WORK_ADDRESS
----------------------------------------------------------------------------
PERSON_TYPE('Tom', '15-MAR-65', ADDRESS_TYPE('Reston', '123 Main Street', 'Va', 45678), ADDRESS_TYPE('Redwood', '1 Oracle Way', 'Ca', 23456)) |
q NAME.DOB:这些是对象表的标量属性。与我们预期的一样,它们存储为常规的列。
q HOME_ADDRESS,WORK_ADDRESS:这些也是“神奇的”函数。它们把所表示的列集返回为一个对象。这些不占用实际空间,只是为实际指示NULL或NOT NULL。
q SYS_NCnnnnn$:这些是嵌入的对象类型的标量实现。由于PERSON_TYPE中嵌入了ADDRESS_TYPE,Oracle需要留出空间将它们存储在适当类型的列中。系统生成的名字是必要的,因为列名必须惟一,我们很可能多次使用同一个对象类型(像这里一样),如果不采用系统生成的名字,最后就完全有可能重复地使用相同的名字,如有两个ZIP列。
[tkyte@localhost tkyte]$ exp userid=/ tables=people rows=n
Export: Release 10.1.0.3.0 - Production on Sun May 1 14:04:16 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export specified tables via Conventional Path ...
. . exporting table PEOPLE
Export terminated successfully without warnings.
[tkyte@localhost tkyte]$ imp userid=/ indexfile=people.sql full=y
Import: Release 10.1.0.3.0 - Production on Sun May 1 14:04:33 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.01.00 via conventional path import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
Import terminated successfully without warnings.
|
CREATE TABLE "OPS$TKYTE"."PEOPLE" OF "PERSON_TYPE" OID 'F610318AC3D8981FE030007F01001464' OIDINDEX (PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 131072 NEXT 131072
MINEXTENTS 1 MAXEXTENTS 4096
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "USERS") 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" NOCOMPRESS
/
ALTER TABLE "OPS$TKYTE"."PEOPLE" MODIFY ("SYS_NC_OID$" DEFAULT SYS_OP_GUID())
/
|
(1) 创建PEOPLE表。
(2) 导出这个表。
(3) 删除这个表和底层PERSON_TYPE。
(4) 用不同的属性创建一个新的PERSON_TYPE。
(5) 导入原来的PEOPLE数据。
ops$tkyte@ORA10G> create table people of person_type
2 /
Table created.
ops$tkyte@ORA10G> 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
ops$tkyte@ORA10G> insert into people(name) 2 select rownum from all_objects;
48217 rows created.
ops$tkyte@ORA10G> exec dbms_stats.gather_table_stats( user, 'PEOPLE' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA10G> select table_name, avg_row_len from user_object_tables;
TABLE_NAME AVG_ROW_LEN
------------------------------ -----------
PEOPLE 23
|
ops$tkyte@ORA10G> CREATE TABLE "PEOPLE" 2 OF "PERSON_TYPE" 3 ( constraint people_pk primary key(name) )
4 object identifier is PRIMARY KEY
5 /
Table created.
ops$tkyte@ORA10G> 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 81
SYS_NC_ROWINFO$ 121 1
|
ops$tkyte@ORA10G> insert into people (name) 2 values ( 'Hello World!' );
1 row created.
ops$tkyte@ORA10G> select sys_nc_oid$ from people p;
SYS_NC_OID$
-------------------------------------------------------------------------------
F610733A48F865F9E030007F0100149A00000017260100010001002900000000000C07001E01000
02A
00078401FE000000140C48656C6C6F20576F726C642100000000000000000000000000000000
0000
ops$tkyte@ORA10G> select utl_raw.cast_to_raw( 'Hello World!' ) data
2 from dual;
DATA
-------------------------------------------------------------------------------
48656C
6C
6F
20576F
726C
6421
ops$tkyte@ORA10G> select utl_raw.cast_to_varchar2(sys_nc_oid$) data
2 from people;
DATA
-------------------------------------------------------------------------------
<garbage bits and bytes..>Hello World!
|
ops$tkyte@ORA10G> create table people_tab 2 ( name varchar2(30) primary key,
3 dob date,
4 home_city varchar2(30), 5 home_street varchar2(30),
6 home_state varchar2(2),
7 home_zip number,
8 work_city varchar2(30), 9 work_street varchar2(30), 10 work_state varchar2(2),
11 work_zip number
12 )
13 /
Table created.
ops$tkyte@ORA10G> create view people of person_type 2 with object identifier (name)
3 as
4 select name, dob,
5 address_type(home_city,home_street,home_state,home_zip) home_adress, 6 address_type(work_city,work_street,work_state,work_zip) work_adress
7 from people_tab
8 /
View created.
ops$tkyte@ORA10G> insert into people values ( 'Tom', '15-mar-1965', 2 address_type( 'Reston', '123 Main Street', 'Va', '45678' ), 3 address_type( 'Redwood', '1 Oracle Way', 'Ca', '23456' ) );
1 row created.
|
q 使我们能够把多个表的数据共同存储在同一个(多个)数据库块上。
q 是我们能够强制把类似的数据根据某个聚簇键物理地存储在“一起“。例如,采用这种方式,部门10的所有数据(来自多个表)可以存储在一起。