oracle和mysql的堆表_Oracle 数据库表之堆组织表(最常用的表类型)！

堆组织表就是oracle默认的表类型，在create table创建表的时候，如果没有指定特定的表类型，那默认的就是堆组织表。堆其实就是一种数据结构，以随机的方式管理，而不是按照特定的顺序来放置数据，数据只会放置合适的地方。堆实际上就是一个很大的空间，数据库表的相应磁盘或者内存区。

1. 创建一个堆组织表，测试一部分数据，查看插入的行的顺序

PgSQL

SQL> create table jerry_test(

2 transaction_id number,

3 transaction_type number default 0,

4 transaction_name varchar2(20) default ''

5 );

Table created.

SQL> insert into jerry_test(transaction_id) values (1);

1 row created.

SQL> insert into jerry_test(transaction_id) values (2);

1 row created.

SQL> insert into jerry_test(transaction_id) values (3);

1 row created.

SQL> delete from jerry_test where transaction_id=2;

1 row deleted.

SQL> insert into jerry_test(transaction_id) values (4);

1 row created.

SQL> select * from jerry_test;

TRANSACTION_ID TRANSACTION_TYPE TRANSACTION_NAME

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

1 0

4 0

3 0

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

SQL>createtablejerry_test(

2transaction_idnumber,

3transaction_typenumberdefault0,

4transaction_namevarchar2(20)default''

5);

Tablecreated.

SQL>insertintojerry_test(transaction_id)values(1);

1rowcreated.

SQL>insertintojerry_test(transaction_id)values(2);

1rowcreated.

SQL>insertintojerry_test(transaction_id)values(3);

1rowcreated.

SQL>deletefromjerry_testwheretransaction_id=2;

1rowdeleted.

SQL>insertintojerry_test(transaction_id)values(4);

1rowcreated.

SQL>select*fromjerry_test;

TRANSACTION_IDTRANSACTION_TYPETRANSACTION_NAME

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

10

40

30

在开发的过程中，可能开发者遇到一些疑问，为什么select查看表数据的时候，不是按照插入的顺序来展示的？这篇文章就可以解答你的疑惑。堆组织表在全表扫描的时候，会按照命中的顺序来获取数据，而不是以插入的顺序，比如这样一个例子: 一个块大小是8K的情况下，首先插入一个大小为3K的一行数据，然后又插入一个大小为6K的一行数据，这2行就不能存放在同一个块上，然后又插入一个大小为3K的一行数据，当查询这个表的时候，可能看到的表数据顺序就是3k的行，3k的行和6k的行，提取的数据并不是按照插入的顺序来提取的，oracle只会把数据放在能放下的任何地方，这里的例子oracle就把第一次插入的3K数据和最后一次插入的3K数据放到一个块中，所以插入的数据顺序并不代表提取数据的顺序。

当然如果想按照插入的顺序来提取数据，只需在表中增加一列，标识插入的时间点或者序列就可以，在查询的时候order by一下就没问题了。

其实在创建堆组织表的时候，oracle为表指定了很多默认的选项，当使用create table创建一个堆组织表的时候，可以使用dbms_metadata.get_ddl包来查询真正的表的定义

PgSQL

SQL> create table jerry_test1(

2 transaction_id number primary key,

3 transaction_type number,

4 transaction_name varchar2(20),

5 transaction_clob clob

6 );

Table created.

SQL> set long 100000

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

DBMS_METADATA.GET_DDL('TABLE','JERRY_TEST1','SCOTT')

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

CREATE TABLE "SCOTT"."JERRY_TEST1"

( "TRANSACTION_ID" NUMBER,

"TRANSACTION_TYPE" NUMBER,

"TRANSACTION_NAME" VARCHAR2(20),

"TRANSACTION_CLOB" CLOB,

PRIMARY KEY ("TRANSACTION_ID")

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

TABLESPACE "USERS" ENABLE

) SEGMENT CREATION DEFERRED

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

DBMS_METADATA.GET_DDL('TABLE','JERRY_TEST1','SCOTT')

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

NOCOMPRESS LOGGING

TABLESPACE "USERS"

LOB ("TRANSACTION_CLOB") STORE AS BASICFILE (

TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION

NOCACHE LOGGING )

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

SQL>createtablejerry_test1(

2transaction_idnumberprimarykey,

3transaction_typenumber,

4transaction_namevarchar2(20),

5transaction_clobclob

6);

Tablecreated.

SQL>setlong100000

SQL>selectdbms_metadata.get_ddl('TABLE','JERRY_TEST1','SCOTT')fromdual;

DBMS_METADATA.GET_DDL('TABLE','JERRY_TEST1','SCOTT')

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

CREATETABLE"SCOTT"."JERRY_TEST1"

("TRANSACTION_ID"NUMBER,

"TRANSACTION_TYPE"NUMBER,

"TRANSACTION_NAME"VARCHAR2(20),

"TRANSACTION_CLOB"CLOB,

PRIMARYKEY("TRANSACTION_ID")

USINGINDEXPCTFREE10INITRANS2MAXTRANS255

TABLESPACE"USERS"ENABLE

)SEGMENTCREATIONDEFERRED

PCTFREE10PCTUSED40INITRANS1MAXTRANS255

DBMS_METADATA.GET_DDL('TABLE','JERRY_TEST1','SCOTT')

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

NOCOMPRESSLOGGING

TABLESPACE"USERS"

LOB("TRANSACTION_CLOB")STOREASBASICFILE(

TABLESPACE"USERS"ENABLESTORAGEINROWCHUNK8192RETENTION

NOCACHELOGGING)

当然我们在创建表的时候，无需指定这么多属性(除非有必要)，了解到真实的create table语句，下面来看一下每个属性的含义

FREELIST：只适用于手动管理的段，现在大部分使用的数据库版本都是11G，使用自动段管理，所以这个属性就不介绍了。

PCTFREE：适用于手动管理和自动管理的段，这个和即将介绍的PCTUSED有一定的关联性，根据块当前的使用情况，决定能否将一行增加到这个块中，这个属性设置不合理还会导致过多的行迁移。

PCTUSED：和PCTFREE不同的是，这个属性只能适用于手动管理的段中，为块分配初始的事务槽数。

COMPRESS/NOCOMPRESS：适用于手动管理和自动管理的段，用来启用和禁用表数据的压缩，在目前使用的11g r2版本中，有多个选项:nologging禁用所有压缩，compress for oltp启用所有操作的压缩(无论是直接路径加载还是普通的insert)，compress basic只启动直接路径操作的压缩。

INITRANS: 适用于手动管理和自动管理的段,表示为block分配的初始slot数目.默认是2,每一个slot占据的空间是23-24个字节

ENABLE/DISABLE STORAGE IN ROW：启用/禁用随结构化数据在航中存储lob数据，禁用后会把log数据存储在另外一个段中。

转载请注明: 版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!

最后编辑：2014-06-29作者：Jerry

一个积极向上的小青年，热衷于分享--Focus on DB,BI,ETL