普通表
- 优点：
- 语法简单
- 适合大部分情况
- 缺点：
- 表更新日志开销大--》可以适当选择全局表
grant all on dba_objects to swat; grant all on v_$statname to swat; grant all on v_$mystat to swat; grant create any view to swat; conn swat/swat SELECT a.name, b.value from v$statname a, v$mystat b where a.statistic#=b.statistic# and a.name='redo size'; create or replace view v_redo as SELECT a.name, b.value from v$statname a, v$mystat b where a.statistic#=b.statistic# and a.name='redo size'; create table t as select * from dba_objects; NAME VALUE ---------------------------------------------------------------- ---------- redo size 12204 SQL> delete from t; 72493 rows deleted. SQL> select * from v_redo; NAME VALUE ---------------------------------------------------------------- ---------- redo size 28869556 SQL> select (28869556-12204)/1024/1024||'M' redo from dual; REDO --------------------- 27.52051544189453125M SQL> insert into t select * from dba_objects; 72494 rows created. SQL> select * from v_redo; NAME VALUE ---------------------------------------------------------------- ---------- redo size 37166892 SQL> select (37166892-28869556)/1024/1024||'M' redo from dual; REDO -------------------- 7.91295623779296875M SQL> update t set object_id=rownum; 72494 rows updated. SQL> select * from v_redo; NAME VALUE ---------------------------------------------------------------- ---------- redo size 57257080 SQL> select (57257080-37166892)/1024/1024||'M' redo from dual; REDO ---------------------- 19.159496307373046875M 对72494条记录删除产生27.5M > 更新产生19.2M > 插入产生7.9M。 对于全局临时表来说这个数字分别是：22.8m 12.8M 0.43M
- delete无法释放空间--》可以考虑全局临时表和分区表，Truncate和Delete的区别。 Truncate 是DDL不能加where字句，可以用分区表间接利用Truncate好处。
在sys用户下运行： @$ORACLE_HOME/sqlplus/admin/plustrce.sql grant plustrace to swat; conn swat/swat set autotrace on select count(*) from t;
Statistics
----------------------------------------------------------
1037 consistent getsdelete from t;
select count(*) from t;Statistics
----------------------------------------------------------
1037 consistent getstruncate table t;
select count(*) from t;
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | ---上两次选择cost均为288.时间为4秒
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 1 | 2 (0)| 00:00:01 |Statistics
----------------------------------------------------------
20 recursive calls
1 db block gets --上面两次select语句以上项是0，所以没有拷贝过来
10 consistent gets - 表太大检索较慢--》可以选择分区表或者建立索引，索引本身是双刃剑，既可以提高性能，也会给数据库带来负担。
- 索引回表读开销大--》可以选择索引组织表
select * from t where object_id<=10; 4 consistent gets
----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows| Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9 | 1863 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID |T | 9 | 1863 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_T_OID | 9 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- select object_id from t where object_id<=10; 2 consistent gets
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 9 | 117 | 1 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| IDX_T_OID | 9 | 117 | 1 (0)| 00:00:01 |
- 即使有序插入，也难有序读出--》可以选择聚簇表: 一般读取是无序的，需要使用order by来排序，这样就增加了一块sort内存，增加了开销。为了避开orderby有两种方法
- 用order by的排序列建索引。
- 将普通表改造为聚簇表。
- 表更新日志开销大--》可以适当选择全局表
全局临时表：应用场景：为了数据安全和数据保护的目的，对表的日志是不可避免；然而，实际情况中有些应用对于某些操作是不要求恢复的，如：运算中的临时中间结果集。
- 优点：
- 高效删除
- 产生日志少
- 不同session独立，不产生锁
- 缺点：
- 语法特别
- 数据无法得到有效保护
- 类型：
- 基于session：退出删除数据也不产生reod信息
- 基于事务：Commit是删除数据，但是不会对这个删除动作产生redo信息。
会话级别的全局临时表 create global temporary table t on COMMIT PRESERVE ROWS as select * from dba_objects where 1=2; select table_name,temporary,duration from user_tables where table_name='T'; select * from v_redo; insert into t select * from dba_objects; select * from v_redo; 产生redo 0.43M； update t set object_id=rownum; 产生redo 12.8M； delete from t; 产生redo 22.8M; 提交，退出sql，然后重新进入：可以看到全部数据被删除。 SQL> conn swat/swat Connected. SQL> select count(*) from t; COUNT(*) ---------- 0 事务级别的全局临时表 create global temporary table tt on COMMIT DELETE ROWS as select * from dba_objects where 1=2; select table_name,temporary,duration from user_tables where table_name='TT'; insert into tt select * from dba_objects; 产生0.43M redo update tt set object_id=rownum; 产生redo 12.8M； delete from tt; 产生redo 22.8M; 提交以后清零 SQL> select count(*) from tt; COUNT(*) ---------- 72495 SQL> commit; Commit complete. SQL> select count(*) from tt; COUNT(*) ---------- 0
- DML操作日志产生情况：
- 插入
- 更新
- 删除
- 重要实用特性：
- 高效删除记录：基于会话的情况是主流；少数应用非常复杂，执行中间要求对表清零，这个时候可以使用基于事务的，也就是说用commit代替delete以避免产生redo。
- 基于session：退出session时删除表数据。
- 基于事务：commit或突出session删除表数据。
- 不同会话独立,这里只说明基于会话的情况：在用不同会话连进去去操作同一个临时表，会发现它神奇的独立于其它的会话。这个特性结合上一个高效删除的特性，会给我们的应用带来极大的方便。
- 高效删除记录：基于会话的情况是主流；少数应用非常复杂，执行中间要求对表清零，这个时候可以使用基于事务的，也就是说用commit代替delete以避免产生redo。
分区表
- 分区表的原理：
- 原理：不同的分区在不同的segment上
- 类型：
- range分区：最常用的就是时间分区。
create table range_pt(id number,month date,code number,contents varchar2(4000)) PARTITION BY RANGE(month)(partition p1 values less than (TO_DATE('2012-02-01','YYYY-MM-DD')), --partition by range是关键字 partition p2 values less than (TO_DATE('2012-03-01','YYYY-MM-DD')), partition p3 values less than (TO_DATE('2012-04-01','YYYY-MM-DD')), partition p4 values less than (TO_DATE('2012-05-01','YYYY-MM-DD')), partition p5 values less than (TO_DATE('2012-06-01','YYYY-MM-DD')), partition p6 values less than (TO_DATE('2012-07-01','YYYY-MM-DD')), partition p7 values less than (TO_DATE('2012-08-01','YYYY-MM-DD')), partition p8 values less than (TO_DATE('2012-09-01','YYYY-MM-DD')), partition p9 values less than (TO_DATE('2012-10-01','YYYY-MM-DD')), partition p10 values less than (TO_DATE('2012-11-01','YYYY-MM-DD')), partition p11 values less than (TO_DATE('2012-12-01','YYYY-MM-DD')), partition p12 values less than (TO_DATE('2013-01-01','YYYY-MM-DD')), partition p_max VALUES LESS THAN (MAXVALUE) --所有不属于上面12个区的全部落这个区，避免出错。 ) ; insert into range_pt (id,month,code,contents) select rownum, to_date(to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'), ceil(dbms_random.value(590,599)), rpad('*',400,'*') from dual connect by rownum <=100000; commit；
- list分区
create table list_pt (id number,month date,code number,contents varchar2(4000)) PARTITION BY LIST (CODE) --这里指明这是一个list分区表以列值分区 ( PARTITION P_591 VALUES (591), --这里并不是说取值只能写一个，也可以写多个如：（591,592,593） PARTITION P_592 VALUES (592), --分区可以落在不同的表空间，如果不写就是默认表空间。 PARTITION P_593 VALUES (593), PARTITION P_594 VALUES (594), PARTITION P_595 VALUES (595), PARTITION P_596 VALUES (596), PARTITION P_597 VALUES (597), PARTITION P_598 VALUES (598), PARTITION P_599 VALUES (599), PARTITION P_other VALUES (DEFAULT) --表示剩下的数据全部落入这个分区。 ) ; insert into list_pt(id,month,code,contents) select rownum, to_date(to_char(sysdate-365,'J')+TRUNC(dbms_random.value(0,365)),'J'), ceil(dbms_random.value(590,599)), rpad('*',400,'*') from dual connect by rownum<=100000;
commit;
- hash分区
create table hash_pt (id number,month date,code number,contents varchar2(4000)) partition by hash(month) partitions 12; --没有指定表分区名，只指定了个数。这个个数尽量是偶数。可以指定表空间：store in (ts1,ts2…ts12) insert into hash_pt(id,month,code,contents) select rownum, to_date(to_char(sysdate-365,'J')+trunc(dbms_random.value(0,365)),'J'), ceil(dbms_random.value(590,599)), rpad('*',400,'*') from dual connect by rownum<=100000; commit;
- 组合分区
create table range_list (id number,month date,code number,contents varchar2(4000)) partition by range (month) subpartition by list (code) subpartition template (subpartition p_591 values (591), subpartition p_592 values (592), subpartition p_593 values (593), subpartition p_594 values (594), subpartition p_595 values (595), subpartition p_596 values (596), subpartition p_597 values (597), subpartition p_598 values (598), subpartition p_599 values (599), subpartition p_other values (DEFAULT)) ( partition p1 values less than (TO_DATE('2014-05-01','YYYY-MM-DD')), partition p2 values less than (TO_DATE('2014-06-01','YYYY-MM-DD')), partition p3 values less than (TO_DATE('2014-07-01','YYYY-MM-DD')), partition p4 values less than (TO_DATE('2014-08-01','YYYY-MM-DD')), partition p5 values less than (TO_DATE('2014-09-01','YYYY-MM-DD')), partition p6 values less than (TO_DATE('2014-10-01','YYYY-MM-DD')), partition p7 values less than (TO_DATE('2014-11-01','YYYY-MM-DD')), partition p8 values less than (TO_DATE('2014-12-01','YYYY-MM-DD')), partition p9 values less than (TO_DATE('2015-01-01','YYYY-MM-DD')), partition p11 values less than (TO_DATE('2015-02-01','YYYY-MM-DD')), partition p12 values less than (TO_DATE('2015-03-01','YYYY-MM-DD')), partition p_max values less than (maxvalue) ) ; insert into range_list (id,month,code,contents) select rownum, to_date( to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'), CEIL(DBMS_RANDOM.VALUE(590,599)), RPAD('*',400,'*') FROM DUAL CONNECT BY ROWNUM <=100000; select segment_name,partition_name,segment_type,bytes/1024/1024 M, tablespace_name from user_segments where segment_name In('RANGE_LIST');
- range分区：最常用的就是时间分区。
- 优点/重要特性：
- 有效的分区消除：将一年分为12个月，那么你只需访问特定的月。就不用去访问另外的11个月。
SQL> set pagesize 5000 SQL> col segment_name format a20 SQL> col partition_name format a20 SQL> col segment_type format a20 SQL> select segment_name,partition_name,segment_type,bytes/1024/1024 M,tablespace_name from user_segments; set linesize 1000 set autotrace traceonly set timing on
SQL> select COUNT(*) from range_list where month>=to_date('2014-09-04','YYYY-MM-DD') and month<=to_date('2014-09-07','YYYY-MM-DD');
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 1002295668------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 143 (0)| 00:00:02 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION RANGE SINGLE| | 1409 | 11272 | 143 (0)| 00:00:02 | 6 | 6 |
| 3 | PARTITION LIST ALL | | 1409 | 11272 | 143 (0)| 00:00:02 | 1 | 10 |
|* 4 | TABLE ACCESS FULL | RANGE_LIST | 1409 | 11272 | 143 (0)| 00:00:02 | 51 | 60 |
------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------4 - filter("MONTH"<=TO_DATE(' 2014-09-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"MONTH">=TO_DATE(' 2014-09-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
552 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedSQL> select COUNT(*) from range_list where month>=to_date('2014-09-04','YYYY-MM-DD') and month<=to_date('2014-09-07','YYYY-MM-DD') AND CODE='594';
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1058855316------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 17 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 12 | | | | |
| 2 | PARTITION RANGE SINGLE| | 156 | 1872 | 17 (0)| 00:00:01 | 6 | 6 |
| 3 | PARTITION LIST SINGLE| | 156 | 1872 | 17 (0)| 00:00:01 | KEY | KEY |
|* 4 | TABLE ACCESS FULL | RANGE_LIST | 156 | 1872 | 17 (0)| 00:00:01 | 54 | 54 |
------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------4 - filter("MONTH"<=TO_DATE(' 2014-09-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"MONTH">=TO_DATE(' 2014-09-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
44 recursive calls
0 db block gets
67 consistent gets
1 physical reads
0 redo size
527 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed - 高效的记录清理：如果想删除某区数据，可以用truncate来清空，避免了delete删除慢，高水位不释放的问题。
SQL> select segment_name,partition_name,segment_type,bytes/1024/1024 M,tablespace_name from user_segments; SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE M TABLESPACE_NAME -------------------- -------------------- -------------------- ---------- ------------------------------ RANGE_LIST P2_P_595 TABLE SUBPARTITION .5 NYC SQL> ALTER TABLE RANGE_LIST TRUNCATE PARTITION P2_P_595; ALTER TABLE RANGE_LIST TRUNCATE PARTITION P2_P_595 * ERROR at line 1: ORA-02149: Specified partition does not exist 说明只能对主分区有效。对子分区无效。 Elapsed: 00:00:00.00 SQL> ALTER TABLE RANGE_LIST TRUNCATE PARTITION P2; Table truncated.
Elapsed: 00:00:00.93 《《---速度比delete快得多。
SQL> select count(*) from range_list partition(p2);
COUNT(*)
----------
0Elapsed: 00:00:00.01
- 高效的记录转移和其他分区操作：
alter table range_list exchange partition p2 with table mid_table; --这样可以实现分区数据的快速备份。
alter table range_list split partition p_max at (to_date('2015-05-01','YYYY-MM-DD')) INTO (partition p13,partition p_max);
Table altered. --分区切割，这分出来的p13同样有子分区。
alter table range_list merge partitions p13,p_max into partition p_max; --同样合并也对子分区生效。
alter table range_list drop partition p_max; –删除分区
alter table range_list add partition p_13 values less than (to_date('2015-05-01','YYYY-MM-DD')); --添加分区
- 有效的分区消除：将一年分为12个月，那么你只需访问特定的月。就不用去访问另外的11个月。
- 分区索引：
- 全局索引： 基本上可以理解为普通索引
create index idx_month on range_list(month); SQL> select segment_name,partition_name,segment_type,bytes/1024/1024 M, tablespace_name from user_segments where segment_name In('IDX_MONTH'); SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE 字节数 （M） TABLESPACE_NAME -------------------- -------------------- -------------------- ---------- ------------------------------------- ----------------------- ---------------------------------------- IDX_MONTH INDEX 3 NYC
- 局部索引：
create index idx_code_pt on range_list(code) local; select segment_name,partition_name,segment_type,bytes/1024/1024 M, tablespace_name from user_segments where segment_name In('IDX_CODE_PT'); 会按所有分区（包括子分区）来分别建索引。
- 全局索引： 基本上可以理解为普通索引
- 分区表相关易错点：
- 有分区用不到： 在实际项目中，要在应用中把分区的特性使用上。
- 分区索引失效：
--对分区索引truncate，会导致全局索引失效 select index_name,status from user_indexes where index_name in ('IDX_MONTH','IDX_CODE_PT'); INDEX_NAME STATUS ------------------------------ -------- IDX_CODE_PT N/A IDX_MONTH VALID select index_name,partition_name,status from user_ind_partitions where index_name in ('IDX_CODE_PT');查分区状态 select index_name,partition_name,status from user_ind_subpartitions where index_name in ('IDX_CODE_PT');-- 查子分区状态 如果我truncate一个已经被truncate的分区，没有事情会发生。如果有数据的分区，全局分区失效，但是子分区全部有效。 SQL> alter table range_list truncate partition p1; Table truncated. Elapsed: 00:00:00.36 SQL> select index_name,status from user_indexes where index_name in ('IDX_MONTH','IDX_CODE_PT'); INDEX_NAME STATUS ------------------------------ -------- IDX_MONTH UNUSABLE IDX_CODE_PT N/A SQL> select index_name,partition_name,status from user_ind_subpartitions where index_name in ('IDX_CODE_PT'); INDEX_NAME PARTITION_NAME STATUS ------------------------------ -------------------- -------- IDX_CODE_PT P1 USABLE --索引重建： SQL> alter index idx_month rebuild; Index altered. Elapsed: 00:00:00.31 SQL> select index_name,status from user_indexes where index_name in ('IDX_MONTH','IDX_CODE_PT'); INDEX_NAME STATUS ------------------------------ -------- IDX_MONTH VALID IDX_CODE_PT N/A Elapsed: 00:00:00.09 --另外一个方法： SQL> alter table range_list truncate partition p3 update global indexes; --其它索引转移，切割，合并，增删也类似。 Table truncated. Elapsed: 00:00:00.56 SQL> select index_name,status from user_indexes where index_name in ('IDX_MONTH','IDX_CODE_PT'); INDEX_NAME STATUS ------------------------------ -------- IDX_MONTH VALID IDX_CODE_PT N/A
- 分区索引效率低下： 因为分区索引高度比较高，所以如果是查询where =的单条记录时，分区索引速度要慢，而正常索引速度要快。
- 缺点
- 语法复杂
- 分区过多对系统有一定影响
索引组织表
- 优点：
- 表就是索引，避免回表
SQL> create table h_t (id int, name varchar2(400),primary key (id)); Table created. Elapsed: 00:00:00.18 SQL> create table i_t (id int, name varchar2(400),primary key (id)) organization index; Table created. Elapsed: 00:00:00.04 SQL> insert into h_t select rownum,rpad('1',300,'*') from dual connect by rownum<=10000; 10000 rows created. Elapsed: 00:00:00.19 SQL> insert into i_t select rownum,rpad('1',300,'*') from dual connect by rownum<=10000; 10000 rows created. Elapsed: 00:00:00.42 SQL> commit; Commit complete. Elapsed: 00:00:00.02 SQL> set autotrace traceonly SQL> select * from h_t where id=8888; Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 549006027 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 215 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| H_T | 1 | 215 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | SYS_C0010820 | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets ………
SQL> select * from i_t where id=8888; --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 215 | 1 (0)| 00:00:01 | |* 1 | INDEX UNIQUE SCAN| SYS_IOT_TOP_74156 | 1 | 215 | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 2 consistent gets
- 表就是索引，避免回表
- 缺点：
- 语法复杂
- 更新开销大
簇表: 对于经常进行join查询的表，他们共同的列可以做成cluster，放在同一块里以提高性能。如果该列提前排好序，则排序查询可以避免排序。
- 优点：
- 可以减少或避免排序
SQL> create cluster clu(cid int,time timestamp sort)hashkeys 10000 hash is cid size 8192; Cluster created. Elapsed: 00:00:08.05 SQL> create table clu_t(cid int,time timestamp sort,name varchar2(400))cluster clu(cid,time); Table created. Elapsed: 00:00:00.04 SQL> set autotrace traceonly explain SQL> variable x int Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) | VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) | NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ] SQL> variable x number SQL> select cid,time,name from clu_t where cid=:x order by time; Elapsed: 00:00:00.02 Execution Plan ---------------------------------------------------------- Plan hash value: 2104460764 ---------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ---------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 228 | 0 (0)| |* 1 | TABLE ACCESS HASH| CLU_T | 1 | 228 | | ---------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("CID"=TO_NUMBER(:X)) Note ----- - dynamic sampling used for this statement (level=2)
- 可以减少或避免排序
- 缺点：
- 语法复杂
- 表更新开销大
