Table 参数总结

关于Oracle Tables 的一些基础知识：

Ref: http://spaces.msn.com/sunmoonking/

1.BUFFER POOL SQL> CREATE TABLE wwm_test(ID NUMBER(9)) STORAGE (BUFFER_POOL DEFAULT);

Table created.

1* SELECT TABLE_NAME,BUFFER_POOL FROM USER_TABLES WHERE TABLE_NAME='WWM_TEST'
SQL> /

TABLE_NAME BUFFER_
------------------------------ -------
WWM_TEST DEFAULT

SQL> ALTER TABLE WWM_TEST STORAGE(BUFFER_POOL RECYCLE);

Table altered.

SQL> SELECT TABLE_NAME,BUFFER_POOL FROM USER_TABLES WHERE TABLE_NAME='WWM_TEST';

TABLE_NAME BUFFER_
------------------------------ -------
WWM_TEST RECYCLE

SQL> ALTER TABLE WWM_TEST STORAGE(BUFFER_POOL KEEP);

Table altered.

SQL> SELECT TABLE_NAME,BUFFER_POOL FROM USER_TABLES WHERE TABLE_NAME='WWM_TEST';

TABLE_NAME BUFFER_
------------------------------ -------
WWM_TEST KEEP

2. CACHE

index-organized table不能被这样CACHE。

SQL> CREATE TABLE WWM_TEST2(ID NUMBER(10)) CACHE;

Table created.

SQL> SELECT TABLE_NAME,CACHE FROM USER_TABLES WHERE TABLE_NAME='WWM_TEST2';

TABLE_NAME CACHE
------------------------------ ----------
WWM_TEST2 Y

SQL> ALTER TABLE WWM_TEST2 NOCACHE;

Table altered.

SQL> SELECT TABLE_NAME,CACHE FROM USER_TABLES WHERE TABLE_NAME='WWM_TEST2';

TABLE_NAME CACHE
- ----------------------------- ----------
WWM_TEST2 N

3. COMPRESS

COMPRESS常用在数据仓库里，插入更新不频繁，能节省存储空间。

建立NOCOMPRESS的TABLE 和INDEX

SQL> create table nocompress_wwm as select * from all_objects;

Table created.

SQL> create index ind_nocompress_wwm_object_id on nocompress_wwm(objecT_id);

Index created.

建立COMPRESS的TABLE和INDEX

1* create table compress_wwm compress as select * from all_objects
SQL> /

Table created.

SQL> create index ind_compress_wwm_object_id on compress_wwm(object_id);

Index created.

统计信息以便比较

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>'SYSTEM',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT TABLE_NAME,BLOCKS FROM USER_TABLES WHERE TABLE_NAME LIKE '%COMPRESS%';

TABLE_NAME BLOCKS
------------------------------ ----------
COMPRESS_WWM 420
NOCOMPRESS_WWM 809

可见所用BLOCK少了很多
1* SELECT INDEX_NAME,LEAF_BLOCKS FROM USER_INDEXES WHERE INDEX_NAME LIKE '%COMPRE%'
SQL> /

INDEX_NAME LEAF_BLOCKS
------------------------------ -----------
IND_COMPRESS_WWM_OBJECT_ID 130
IND_NOCOMPRESS_WWM_OBJECT_ID 130

但索引还是一样

4. LOGGING

SQL> create table wwm_log_test (id number(10));

Table created.

1* select table_name,logging from user_tables where table_name='WWM_LOG_TEST'
SQL> /

TABLE_NAME LOG
------------------------------ ---
WWM_LOG_TEST YES

SQL> alter table wwm_log_test nologging;

Table altered.

SQL> select table_name,logging from user_tables where table_name='WWM_LOG_TEST'
2 /

TABLE_NAME LOG
------------------------------ ---
WWM_LOG_TEST NO

5.PARALLEL

与PARALLEL_THREADS_PER_CPU 初始化参数相关

SQL> create table wwm_parallel_test (id number(10)) parallel(degree 4);

Table created.

SQL> select table_name,degree from user_tables where table_name='WWM_PARALLEL_TEST';

TABLE_NAME DEGREE
------------------------------ --------------------
WWM_PARALLEL_TEST 4

也可以写成

1* create table wwm_parallel_test1(id number(10)) parallel 4
SQL> /

Table created.

SQL> select table_name,degree from user_tables where table_name='WWM_PARALLEL_TEST';

TABLE_NAME DEGREE
------------------------------ --------------------
WWM_PARALLEL_TEST 4

修改

SQL> alter table wwm_parallel_test1 parallel 2;

Table altered.

SQL> select table_name,degree from user_tables where table_name like ' WWM_PARALLEL%';

TABLE_NAME DEGREE
------------------------------ --------------------
WWM_PARALLEL_TEST 4
WWM_PARALLEL_TEST1 2

6. TABLE LOCK

可以防止DDL

SQL> create table wwm_lock(id number(10));

Table created.

SQL> alter table wwm_lock disable table lock;

Table altered.

SQL> alter table wwm_lock add (name varchar2(10));
alter table wwm_lock add (name varchar2(10))
*
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for WWM_LOCK

很神奇吧，想想能用在什么地方呢？

使表不能LOCK，但是现在还是可以DML的。
SQL> insert into wwm_lock values(111);

1 row created.

SQL> commit;

Commit complete.

使表能LOCK

SQL> alter table wwm_lock enable table lock;

Table altered.

SQL> alter table wwm_lock add (name varchar2(10));

Table altered.

SQL> desc wwm_lock
Name Null? Type
----------------------------------------- -------- ---------------
ID NUMBER(10)
NAME VARCHAR2(10)

7. global temporary table

不能指定表空间，只能建立在TEMP表空间上，

这里用on commit delete rows表示在COMMIT的时候清空。

SQL> create global temporary table wwm_temp(id number(10)) on commit delete rows;

Table created.

SQL> insert into wwm_temp values (111);

1 row created.

SQL> select * from wwm_temp;

ID
----------
111

SQL> commit;

Commit complete.

检查是否清空了

SQL> select * from wwm_temp;

no rows selected

SQL> insert into wwm_temp values (222);

1 row created.

TRUNCATE可以用吗？

SQL> truncate table wwm_temp;

Table truncated.

SQL> select * from wwm_temp;

no rows selected

global temporary table 本身是NOLOG的

SQL> select table_name,logging from user_tables where table_name='WWM_TEMP';

TABLE_NAME LOG
------------------------------ ---
WWM_TEMP NO

这里看看ON COMMIT PRESERVER ROWS,这种情况在SESSION结束的时候会清空.
1* create global temporary table wwm_temp_reserve (id number(10)) on commit preserve rows
SQL> /

Table created.

SQL> insert into wwm_temp_reserve values (111);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from wwm_temp_reserve;

ID
----------
111
结束SESSION

SQL> connect user/pass
Connected.
SQL> select * from wwm_temp_reserve;

no rows selected

8. comment

不多解释了

SQL> comment on table wwm2 is 'MY TEST TABLE';

Comment created.

1* select table_name,comments from user_tab_comments where table_name='WWM2'
SQL> /

TABLE_NAME
------------------------------
COMMENTS
-------------------------------------------------------------------------------
WWM2
MY TEST TABLE

在列上作COMMENT

SQL> comment on column wwm2.id is 'id column of wwm2,it is the sequence'
2 /

Comment created.

SQL> select table_name,column_name ,comments from user_col_comments where table_name='WWM2';

TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
COMMENTS
--------------------------------------------------------------------------------
WWM2 ID
id column of wwm2,it is the sequence

WWM2 NAME

可见TABLE里的所有字段都在USER_COL_COMMENTS里存在,只是有的有COMMENT

9. EXTENT ALLOCATE AND DEALLOCATE

SQL> create table wwm_allo(id number(10));

Table created.

实验表占1个EXTENTS

SQL> select segment_name,extents from user_segments where segment_name='WWM_ALLO';

SEGMENT_NAME
--------------------------------------------------------------------------------
EXTENTS
----------
WWM_ALLO
1

使表扩展
SQL> alter table wwm_allo allocate extent;

Table altered.

扩展后占2个EXTENT

SQL> select segment_name,extents from user_segments where segment_name='WWM_ALLO';

SEGMENT_NAME
--------------------------------------------------------------------------------
EXTENTS
----------
WWM_ALLO
2

回收相临的EXTENT
SQL> alter table wwm_allo deallocate unused;

Table altered.

SQL> select segment_name,extents from user_segments where segment_name='WWM_ALLO';

SEGMENT_NAME
--------------------------------------------------------------------------------
EXTENTS
----------
WWM_ALLO
1

10. 不相临EXTENT如何合并

建立测试表

SQL> create table wwm_ext (id number(6),name varchar2(20));

Table created.

SQL> select block_num,count(*) from (
2 select dbms_rowid.rowid_block_number(rowid) block_num from wwm_ext)
3 group by block_num
4 /

no rows selected

插入测试数据

1 begin
2 for n in 1..100000
3 loop
4 insert into wwm_ext (id,name)
5 values (n,'asdfghjklqwertyuiopz');
6 end loop;
7 commit;
8* end;
SQL> /

PL/SQL procedure successfully completed.

查其占用情况

select block_num,count(*) from (
select dbms_rowid.rowid_block_number(rowid) block_num from wwm_ext)
group by block_num

BLOCK_NUM COUNT(*)
---------- ----------
89452 115
89453 115
89454 115
89455 115
89456 115

............................

89458 115
89459 115
89460 71

867 rows selected.

人工制造碎片

SQL> delete from wwm_ext where mod (id,2)=1;

50000 rows deleted.

SQL> commit;

Commit complete.

查其占用情况

select block_num,count(*) from (
select dbms_rowid.rowid_block_number(rowid) block_num from wwm_ext)
group by block_num

BLOCK_NUM COUNT(*)
---------- ----------

89452 58
89453 57
89454 58
89455 57
...............................
89457 57
89458 58
89459 57
89460 36

867 rows selected.

ALTER TABLE WWM_EXT DEALLOCATE UNUSED;对这种情况是没有任何作用的

10G 提供了ALTER TABLE TABLE_NAME SHRINK SPACE COMPACT CASCADE；的语法可以解决碎片问题，但要求表是ENABLE ROW MOVEMENT的。

9I最有效的解决方法只有EXT/IMP或MOVE 了。

1* alter table wwm_ext move tablespace users
SQL> /

Table altered.

select block_num,count(*) from (
select dbms_rowid.rowid_block_number(rowid) block_num from wwm_ext)
group by block_num

BLOCK_NUM COUNT(*)
---------- ----------
468 114
469 114
470 114
471 114
...................................
473 114
474 114
475 114
476 11

438 rows selected.

11. 其他

ALTER TABLE wwm DROP COLUMN id CHECKPOINT 10000;

ALTER TABLE wwm SET UNUSED COLUMN id;

SELECT * FROM user_unused_col_tabs;

ALTER TABLE wwm DROP UNUSED COLUMNS;

ALTER TABLE wwm DROP UNUSED COLUMNS CHECKPOINT 250;

DROP TABLE wwm CASCADE CONSTRAINTS;

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

转载于:http://blog.itpub.net/35489/viewspace-85083/