：普通表的不足
1）表的更新有日志开销
2）表的DELETE不彻底 ()
3）大表查询慢
4）索引表读开销大
1：日志开销
--查看产生多少redo 日志
1）
col name format a30
select a.name,b.value
from v$statname a,v$mystat b
where a.statistic#=b.statistic#
and a.name='redo size';
2）通过执行计划查看
set autotrace on
alter table t nologging;
create table t as select * from dba_objects where 1=2;
insert into t select * from dba_objects ;
insert /*+ append,nologging*/ into t select * from dba_objects ;
insert /*+ append nologging */ into t select * from dba_objects;
alter table t logging;
--查看普通表是否为nologging
col OWNER format a20
col table_name format a30
select OWNER,TABLE_NAME,logging from dba_tables where TABLE_NAME='T';
Noarchivelog模式下,对于常规表的insert append只产生少量redo
archivelog 常规表的insert append产生和insert同样的redo此时的insert append实际上并不会有性能提高.
但是此时的append是生效了的
--优化建议
不考虑空间和数据安全（性能和安全需要相互妥协）：
1) alter table t nologging
2) 以append 方式插入数据
2：删除不彻底
delete是最消耗性能的操作：产生UNDO最多，同时UNDO也需要REDO保护，delete 产生的REDO最大
creater table delete_t as select * from dba_objects;
set autotrace on
select count(1) from delete_t;
delete from delete_t;
select count(1) from delete_t;
truncate table delete_t;
select count(1) from delete_t;
insert into delete_t select * from dba_objects;
delete from delete_t;
truncate table delete_t;
优化方法：分区表
3：大表查询慢
全表扫描
create table big_t as select * from dba_objects;
insert into big_t select * from big_t;
set timing on
set autotrace on
EXEC DBMS_STATS.gather_table_stats('sys','big_t');
select object_name from big_t where object_id=50;
drop index big_object_id;
create index big_object_id on big_t(object_id);
解决办法：减少访问路径
1）分区表
2）索引
4： 索引表读开销大
set autotrace on
select object_name from big_t where object_id=50;
create index big_object_id on big_t(object_id,object_name);
select object_name from big_t where object_id=50;
5: 分区表设计
分区表类型:范围分区、列表分区、哈希分区、组合分区
1）范围分区
create table PART_T_RANGE partition by range(object_id)
(
partition p1 values less than (10000),
partition p2 values less than (20000),
partition p3 values less than (30000),
partition p4 values less than (40000),
partition p5 values less than (50000),
partition p6 values less than (60000),
partition p7 values less than (70000),
--partition p8 values less than (80000),
partition p_max values less than (maxvalue)
)
as select * from dba_objects;
create table BIG_T as select * from dba_objects ;
select * from dba_segments where segment_name in ('PART_T_RANGE','BIG_T');
2）列表分区
create table PART_T_LIST
partition by list(OBJECT_TYPE)
(
partition p_SYNONYM values ('SYNONYM'),
partition p_VIEW values ('VIEW'),
partition p_other values (default)
)
as select * from dba_objects;
select * from dba_segments where segment_name in ('PART_T_RANGE','BIG_T','PART_T_LIST');
select * from dba_segments where owner='WEISI'
3）散列分区（HASH 分区）
create table PART_T_HASH
partition by hash(object_id)
partitions 10
as select * from dba_objects;
select * from dba_segments where segment_name='PART_T_HASH'
4）组合分区
4.1范围-列表
create table PART_T_RANGE_LIST
partition by range(object_id)
subpartition by list(object_type)
subpartition template
( subpartition p_SYNONYM values ('SYNONYM'),
subpartition p_VIEW values ('VIEW'),
subpartition p_other values (default)
)
(
partition p1 values less than (10000),
partition p2 values less than (20000),
partition p3 values less than (30000),
partition p4 values less than (40000),
partition p5 values less than (50000),
partition p6 values less than (60000),
partition p7 values less than (70000),
partition p8 values less than (80000),
partition p_max values less than (maxvalue)
)
as select * from dba_objects;
select * from dba_segments where segment_name in ('PART_T_RANGE','BIG_T','PART_T_LIST','PART_T_RANGE_LIST');
分区表原理： select * from dba_segments where segment_name in ('PART_T_RANGE','BIG_T','PART_T_LIST','PART_T_RANGE_LIST');
将一个大的对象切割成一个小对象
分表表重要特性 ：
1）高效消除分区
EXEC DBMS_STATS.gather_table_stats('SYS','PART_T_RANGE');
range
set linesize 200
set autotrace traceonly
set timing on
select * from part_t_range where object_id < 50000 and object_id > 30000;
create table normal_t as select * from dba_objects;
select * from normal_t where object_id < 50000 and object_id > 30000;
select * from part_t_range where object_id < 50000 and object_id > 30000 and object_type='SYNONYM';
select * from part_t_range_list where object_id < 50000 and object_id > 30000 and object_type='SYNONYM';
select * from norm_t where object_id < 50000 and object_id > 30000 and object_type='SYNONYM';
2) 多样的分区操作
2.1 删除分区
delete 无法释放空间
truncate 有效的释放空间
PART_T_RANGE_LIST
alter table PART_T_range truncate partition P1;
delete normal_t where object_id<10000;
2.2 分区转移
select count(1) from part_t_list where object_type='VIEW';
select count(1) from part_t_list partition(p_VIEW);
create table change_t as select * from dba_objects where 1=2;
select count(1) from change_t;
alter table part_t_list exchange partition P_VIEW with table change_t;
select count(1) from part_t_list where object_type='VIEW';
select count(1) from change_t;
insert into change_t select * from dba_objects where rownum< 100;
commit;
alter table part_t_list exchange partition P_VIEW with table change_t;
3.3 分区切割
select object_type from part_t_list partition(p_other) group by object_type ;
select object_type from part_t_list partition(p_other) where object_type='INDEX'group by object_type ;
alter table part_t_list split partition p_other values('INDEX') into(partition p_index,partition p_other);
alter table part_t_range split partition p_max at(71000) into(partition p_71000,partition p_max);
select object_id from part_t_range partition(p_71000);
select * from dba_segments where segment_name in ('PART_T_LIST','PART_T_RANGE') ;
3.4 分区合并
select count(1) from part_t_range partition(p7);
select count(1) from part_t_range partition(p_max);
alter table part_t_range merge partitions p7, p_max into partition p_max;
alter table part_t_list merge partitions p_index,p_other into partition p_other;
select * from dba_segments where segment_name in ('PART_T_LIST','PART_T_RANGE') ;
3.5 分区增与删
alter table part_t_range add partition p_72000 values less than (72000);
select OBJECT_ID from part_t_range partition(p_71000);
3.5 分区改名
alter table part_t_range rename partition p_7100 to p_71000;
alter table part_t_range1 rename to part_t_range;
EXEC DBMS_STATS.gather_table_stats('SYS','PART_T_RANGE');
6： 分区索引
1）全局索引
创建全局索引
create index index_range on part_t_range(object_id);
select segment_name,partition_name,segment_type,bytes from dba_segments where segment_name='INDEX_RANGE';
2) 局部索引
创建局部索引
create index index_list_local on part_t_list(object_type) local;
select segment_name,partition_name,segment_type,bytes from dba_segments where segment_name='INDEX_LIST_LOCAL';
7： 分区索引注意事项
1：索引失效
select index_name,status from dba_indexes where index_name in ('INDEX_RANGE','INDEX_LIST_LOCAL');
select index_name,partition_name,status from dba_ind_partitions where index_name='INDEX_LIST_LOCAL';
alter table part_t_range truncate partition p2;
select count(1) from part_t_range partition(p2);
alter table part_t_list truncate partition p_view;
select count(1) from part_t_list partition(p_view);
alter index index_range rebuild;
select index_name,status from dba_indexes where index_name in ('INDEX_RANGE','INDEX_LIST_LOCAL');
2：本地索引并不是一定是最好的
create index index_range_list on PART_T_RANGE_LIST(object_name) local;
create index index_norm on norm_t(object_name);
set autotrace traceonly
set linesize 300
select * from part_t_range_list where object_name='PK_DEPT';
select * from norm_t where object_name='PK_DEPT';
3：无法应用分区条件
set autotrace traceonly
set linesize 300
select * from part_t_range_list where object_name='PK_DEPT';
select * from part_t_range_list where object_name='PK_DEPT_WEISI';
select * from part_t_range_list where object_name='PK_DEPT_WEISI' and object_id<1000 and object_type='VIEW';
select * from norm_t where object_name='PK_DEPT_WEISI' and object_id<1000 and object_type='VIEW';
