关于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.
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/