ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 数据库表(一)

数据库表(一)

原创 Linux操作系统 作者:xw_z 时间:2009-08-16 19:00:57 0 删除 编辑

数据库表

 

1.         段空间管理

²        段空间管理有两种方法:

1)  手动段空间管理(Manual Segment Space Management):由你设置FREELISTFREELIST GROUPSPCTUSED和其他参数来控制如何分配、使用和重用段中的空间。

2)  自动段空间管理(Automatic Segment Space Management,ASSM):你只需控制与空间使用相关的一个参数:PCTFREE。其他参数都将被忽略。

²        Freelists

1)  使用手动段空间管理时,每个对象至少有一个相关的freelist,使用块时,可能会根据需要把块放在freelist上或者从freelist删除。只有位于高水位线(HWM)以下的对象块才会出现在freelist中。仅当freelist为空时才会使用HWM之上的块,此时,Oracle会推进HWM,并把这些块增加到freelist中,采用这种方式,Oracle会延迟到不得已时才增加对象的HWM.

2)  一个对象可以有多个freelist。如果预计会有多个并发用户在一个对象上执行大量的INSERTUPDATE活动,就可以配置多个freelist,这对性能提升很有好处(但是可能要以额外的存储空间为代价)。

3)  使用多个freelist时,有一个主freelist,还有一些进程freelist。对于一个给定的会话,会根据其会话ID的散列值为之指定一个进程freelist。每个进程freelist都只有很少的块,余下的自由块都在主freelist上。使用一个进程freelist时,它会根据需要从主freelist拉出一些块,如果主freelist无法满足空间需求,Oracle就会推进HWM,并向主freelist中增加块。过一段时间后,主freelist会把其存储空间分配多个进程freelist(每个进程freelist都只有为数不多的块)。因此,每个进程会使用一个进程freelist,它不会从一个进程freelist到另一个进程freelist上寻找空间。故,一方面,使用多个freelist 可以大幅度提升性能。另一方面,有可能导致表不太必要地使用稍多的磁盘空间。

²        PCTFREEPCTUSED

1)  PCTFREE参数用来告诉Oracle应该在块上保留多少空间来完成将来的更新,默认是10%,如果自由空间的百分比高于PCTFREE中的指定值,这个块就认为是“自由的”。PCTUSED则告诉Oracle当前不“自由”的一个块上自由空间百分比需要达到多大才能使它再次变为自由的,默认是40%

2)  使用手动段空间管理时,这些参数控制着块何时放入freelist中,以及何时从freelist中取出。如果使用默认值:PCTFREE10PCTUSER40,那么在块到达90%前(有10%的自由空间),这个块会一直在freelist上。一旦到达90%,就会从freelist中取出,而且直到块上的自由空间超过了块的60%时,才会重新回到freelist上,在此之前,这个块一直不在freelist上。

3)  使用ASSM时,PCTFREE仍然会限制能否将一个新行插入到一个块中,但它不会控制一个块是否在freelist上,因为ASSM根本不使用freelist。在ASSM中,PCTUSED将被忽略。

4)  PCTFREE设置得过高,就会浪费空间;设置得太低,更新行时就会导致行迁移。

5)  Oracle迁移一行时,不能简单的移动这一行,它必须留下一个“转发地址”,因为可能有一些索引物理地指向该行原来的地址,简单的更新不会同时修改这些索引,所以,在该行原来的地址上会留下一个指针,指示这一行实际上在什么位置。当一行需要多次迁移时,Oracle或者把这一行迁移回原来的块(如果原来的块有足够的空间),或者把这一行迁移到另外的某个块上,并修改原来块上的转发地址。因此,行迁移总是只涉及一层间接性。

6)  设置PCTFREEPCTUSED值:

Ø         PCTFREE,PCTUSED:如果你插入了将要更新的大量数据,而且这些更新会频繁的增加行的大小。

Ø         PCTFREE,高PCTUSED:如果你只想对表完成INSERTDELETE,或者UPDATE只是缩小行的大小。

²        LOGGINGNOLOGGING

1)  通常对象都采用LOGGING方式创建,说明对象上完成的操作只要能生成redo就都会生成redo.

2)  NOLOGGING则允许该对象完成某些操作时可以不生成redo,如对象的初始创建,SQL*Loder的直接路径加载或重建。它不会完全禁用对象的重做日志生成,只是几个特定的操作不生成日志而已。

²        INITRANSMAXTRANS

1)  段中每个块都有一个块首部,在块首部中有一个事务表,事务表中会建立一些条目来描述哪些事务将块上的哪些行/元素锁定。

2)  INITRANS设置事务表的初始大小,对于表和索引,默认值都为2.

3)  事务表会根据需要动态扩展,最大达到MAXTRANS个条目(假设块上有足够的空间)。在Oracle 10g中,MAXTRANS则被忽略,所有段的MAXTRANS都是255.

 

2.         堆组织表

²        执行CREATE TABLE语句时,默认得到的表类型就是堆组织表。如果想要得到任何其他类型的表结构,就需要在CREATE语句本身中指定它。

²        堆以一种显然随机的方式管理。数据会放在最合适的地方,而不是以某种特定顺序来放置。一般来讲,数据库表本质上是无序的数据集合,全部扫描时,会按命中的顺序来获取数据,而不是以插入的顺序。因此,不要过分依赖查询得到的顺序,除非查询中有一个ORDER BY语句!

²        使用DBMS_METADATA,查询表的定义,该技巧显示了CREATE TABLE语句的许多选项,在定制详细的版本时可以避免去记大量的选项。

SQL> create table t

  2  (x int primary key,

  3  y date,

  4  z clob

  5  )

  6  /

表已创建。

SQL> set serveroutput on

SQL> set linesize 1000

SQL> set long 999999    --不设置会显示不完整

SQL> set pagesize 1000

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

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

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

CREATE TABLE "SCOTT"."T"

   (    "X" NUMBER(38,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 BASICFILE (

  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192

  NOCACHE LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))

²        对于堆表需要注意的选项

1)  FREELISTS:仅适用于手动段空间管理。

2)  PCTFREEASSM和手动段空间管理都使用。在INSERT过程中,会测量块的充满程度。这个参数用于控制能否将一行增加到一个块上,还可以控制因后续更新导致的行迁移。

3)  PCTUSED:仅适用于手动段空间管理。度量一个块必须为多空才允许再次插入行。

4)  INITRANSASSM和手动段空间管理都适合。为块初始分配的事务槽数。

注意:单独存储在LOB段中的LOB数据并不使用表的PCTFREE/PCTUSED参数设置。这些LOB块以不同的方式管理:它们总是会填入,直至达到最大容量,而且仅当完全为空时才返回freelist

 

3.         索引组织表(index organized table,IOT

²        存储在堆中的表是无组织的(即:只要有可用的空间,数据可以放在任何地方);IOT中的数据则按主键存储和排序。

²        使用堆组织表时,必须为表和表主键上的索引分别留出空间,而IOT则不存在主键的空间开销,因为索引就是数据,数据就是索引;索引是一个复杂的数据结构,需要大量的工作来管理和维护,堆组织表管理起来则很容易。

²        适合采用IOT的几种应用

1)  假设有如下的一个表

Create table keywords

(      word varchar2(50),

Position int,

Doc_id int,

Primary key(word,position,doc_id)

);

该表完全由主键组成,因此有超过100%的(主键索引)开销:因为表的大小与主键索引的大小相当(实际上,主键索引更大,因为索引物理的存储了rowid,而表中并不存储rowid,表中的行ID是推断出来的)。如果使用这个表时,WHERE子句只选择了WORD列或WORDPOSITION列,即只使用了表上的索引,而没有使用表,则此时表本身完全是开销。这个应用就非常适合采用IOT

2)  如果你只会通过主键来访问一个表,这个表就非常适合实现为IOT

3)  如果你想保证数据存储在某个位置上,或者希望数据以某种特定的顺序物理存储,IOT就是一种合适的结构。

4)  如果你希望某些数据能物理地共同存储在一起,则适合采用IOT,此种情况下,IOT提供了以下好处:

Ø         提供缓冲区效率,因为给定查询的缓存中需要的块更少;

Ø         减少缓冲区缓存访问,这会改善可扩缩性;

Ø         获取数据的工作总量更少,因为获取数据更快;

Ø         每个查询完成的物理I/O更少,因为对于任何给定的查询,需要的块更少,而且对地址记录的一个物理I/O很可能可以获取所有地址(而不只是其中一个地址,但堆表实现就只是获取一个地址)。

5)  如果经常在一个主键或惟一键上使用BETWEEN查询,利用IOT将数据有序地物理存储,就能提升这些查询的性能。

²        使用DMBS_METADATA显示详细选项

SQL> create table t1

  2  (x int primary key,

  3  y varchar2(25),

  4  z date

  5  )

  6  organization index;

表已创建。

SQL> create table t2

  2  (x int primary key,

  3  y varchar2(25),

  4  z date

  5  )

  6  organization index

  7  OVERFLOW

表已创建。

SQL>create table t3

  2  (x int primary key,

  3  y varchar2(25),

  4  z date

  5  )

  6  organization index

  7  overflow INCLUDING y

表已创建。

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

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

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

  CREATE TABLE "ORACLE"."T1"

   (    "X" NUMBER(38,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

注意:没有PCTUSED子句,但有一个PCTFREE。在堆中,块只能有时能插入新行,而索引则不同,块总是可以插入新的索引条目。PCTFREE会在新创建的索引上预留空间,但对于以后对索引的操作不预留空间。

²        选项NOCOMPRESS

1)  这个选项对索引一般都可用。它告诉Oracle 把每个值分别存储在各个索引条目中(也就是不压缩)。如果对象的主键在AB C 列上,AB C 的每一次出现都会物理地存储。NOCOMPRESS 反过来就是COMPRESS N,在此N 是一个整数,表示要压缩的列数。这样可以避免重复值,并在块级提取“公因子”(factor out)。这样在A 的值(以及B 的值)重复出现时,将不再物理地存储它们。

2)  对前面CREATE TABLE SELECT 分别采用NOCOMPRESSCOMPRESS 1 COMPRESS 2 选项,来展示能节省多少空间。

Ø         先来创建IOT,但不进行压缩:

SQL> 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  /

表已创建。

使用ANALYZE INDEX VALIDATE STRUCTURE命令测量所用的空间

SQL> analyze index iot_pk validate structure;

索引已分析

SQL>select lf_blks,br_blks,used_space,

opt_cmpr_count,opt_cmpr_pctsave

from index_stats;

 LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE

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

404        3       2905039            2                   33

结果说明:索引目前使用了404 个叶子块(即数据所在的块),并使用了3 个分支块(Oracle在索引结构中导航所用的块)来找到这些叶子块。使用的空间大约是3MB2,905,039字节)。OPT_CMPR_COUNT(最优压缩数)列要说的是:“如果你把这个索引置为COMPRESS 2,就会得到最佳的压缩”。OPT_CMPR_PCTSAVE(最优的节省压缩百分比)则是说,如果执行COMPRESS 2,就能节省大约1/3 的存储空间,索引只会使用现在2/3 的磁盘空间。

Ø         使用COMPRESS 1重建这个IOT

SQL> alter table iot move compress 1;

表已更改。

SQL> analyze index iot_pk validate structure;

索引已分析

查询结果如下:

 LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE

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

351        3      2517886              2                  23

结果说明:可以看到,索引确实更小了:大约2.5MB,叶子块更少。但是,现在它说“你还能再节省另外23%的空间”,因为我们没有充分地压缩。

Ø         使用COMPRESS 2重建这个IOT

SQL> alter table iot move compress 2;

表已更改。

SQL> analyze index iot_pk validate structure;

索引已分析

查询结果如下:

 LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE

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

269        3      1934625               2                 0

结果说明:现在大小有了显著减少,不论是叶子块数还是总的使用空间都大幅下降,现在使用的空间大约是1.9MB

²        选项PCTTHRESHOLD

1)    它与以下两个选项有关:OVERFLOWINCLUDING

Ø         OVERFLOW 子句允许你建立另一个段(这就使得IOT 成为一个多段对象,就像有一个CLOB 列一样),如果IOT 的行数据变得太大,就可以溢出到这个段中。注意构成主键的列不能溢出,它们必须直接放在叶子块上。对于OVERFLO W段和堆表来说,PCTFREEPCTUSED 的含义都相同。

Ø         使用溢出段的条件可以采用两种方式来指定:

1)   PCTTHRESHOLD:行中的数据量超过块的这个百分比时,行中余下的列将存储在溢出段中。所以,如果PCTTHRESHOLD 10%,而块大小是8KB,长度大于800 字节的行就会把其中一部分存储在别处,而不能在索引块上存储。

2 INCLUDING:行中从第一列直到INCLUDING 子句所指定列(也包括这一列)的所有列都存储在索引块上,余下的列存储在溢出段中。

²        二次索引(secondary index

1IOT 本身可以有一个索引,就像在索引之上再加索引,这称为二次索引。

2Oracle 引入了一个逻辑rowidlogical rowid)。这些逻辑rowid 根据IOT 主键建立。对于行的当前位置还可以包含一个“猜测”,不过这个猜测几乎是错的,因为稍过一段时间后,IOT中的数据可能就会移动。这个猜测是行第一次置于二次索引结构中时在IOT 中的物理地址。如果IOT 中的行必须移动到另外一个块上,二次索引中的猜测就会变得“过时”。

3)与常规表相比,IOT 上的索引效率稍低。在一个常规表上,索引访问通常需要完成一个I/O 来扫描索引结构,然后需要一个读来读取表数据。对于IOT 通常要完成两个扫描;一次扫描二次结构,另一次扫描IOT 本身。

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

下一篇: 数据库表(二)
请登录后发表评论 登录
全部评论

注册时间:2009-05-22

  • 博文量
    28
  • 访问量
    38363