本文通过logminer分析了在Oracle数据库中，使用truncate和delete删除大量数据时的效率差异。实验结果显示，truncate操作仅产生一条日志记录，而delete操作会为每条删除的数据产生日志，因此truncate在清空表数据时的效率显著高于delete。建议在需要高效删除表数据时使用truncate。
首先打开数据库的最小日志附加模式:
alter database add supplemental log data;
准备两张数据表:
create table t03 as select * from dba_objects;
create table t04 as select * from dba_objects;
下面我们通过logminer来分析truncate与delete在删除数据方面的效率(产生的日志越小，删除数据的效率越高)。
现在对表t03执行truncate操作，对表t04执行delete操作，然后通过logminer分析两种操作产生日志的大小：
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS
SQL> execute dbms_logmnr.add_logfile(LogFileName => '/u02/oradata/C2155/redo01.log',Options => dbms_logmnr.NEW);
PL/SQL procedure successfully completed
SQL> execute dbms_logmnr.add_logfile(LogFileName => '/u02/oradata/C2155/redo02.log',Options => dbms_logmnr.ADDFILE);
PL/SQL procedure successfully completed
SQL> execute dbms_logmnr.add_logfile(LogFileName => '/u02/oradata/C2155/redo03.log',Options => dbms_logmnr.ADDFILE);
PL/SQL procedure successfully completed
SQL> execute dbms_logmnr.start_logmnr(Options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed
SQL> select * from v$logmnr_contents where table_name='T03';
SCN CSCN TIMESTAMP COMMIT_TIMESTAMP THREAD# LOG_ID XIDUSN XIDSLT XIDSQN PXIDUSN PXIDSLT PXIDSQN RBASQN RBABLK RBABYTE UBAFIL UBABLK UBAREC UBASQN ABS_FILE# REL_FILE# DATA_BLK# DATA_OBJ# DATA_OBJD# SEG_OWNER SEG_NAME TABLE_NAME SEG_TYPE SEG_TYPE_NAME TABLE_SPACE ROW_ID SESSION# SERIAL# USERNAME SESSION_INFO TX_NAME ROLLBACK OPERATION OPERATION_CODE SQL_REDO SQL_UNDO RS_ID SEQUENCE# SSN CSF INFO STATUS REDO_VALUE UNDO_VALUE SQL_COLUMN_TYPE SQL_COLUMN_NAME REDO_LENGTH REDO_OFFSET UNDO_LENGTH UNDO_OFFSET DATA_OBJV# SAFE_RESUME_SCN XID PXID AUDIT_SESSIONID
---------- ---------- ----------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------------------- -------------------------------------------------------------------------------- -------------------------------- ---------- -------------------------------- -------------------------------- ------------------ ---------- ---------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- -------------------------------- -------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------- ---------- ---------- ---------- -------------------------------- ---------- ---------- ---------- ------------------------------ ------------------------------ ----------- ----------- ----------- ----------- ---------- --------------- ---------------- ---------------- ---------------
376280 2012-10-22 1 49 4 23 229 4 23 229 49 161 16 2 0 0 0 0 0 0 51183 0 DOLPHIN T03 T03 2 TABLE AAAAAAAAAAAAAAAAAB 1076 66 0 DDL 5 truncate table t03 0x000031.000000a1.0010 1 0 0 USER DDL (PlSql=0 RecDep=0) 0 160 161 0 0 0 0 1 04001700E5000000 04001700E5000000 84
此时我们可以发现执行truncate语句，仅产生一条日志记录。
SQL> select * from v$logmnr_contents where table_name='T04';
SCN CSCN TIMESTAMP COMMIT_TIMESTAMP THREAD# LOG_ID XIDUSN XIDSLT XIDSQN PXIDUSN PXIDSLT PXIDSQN RBASQN RBABLK RBABYTE UBAFIL UBABLK UBAREC UBASQN ABS_FILE# REL_FILE# DATA_BLK# DATA_OBJ# DATA_OBJD# SEG_OWNER SEG_NAME TABLE_NAME SEG_TYPE SEG_TYPE_NAME TABLE_SPACE ROW_ID SESSION# SERIAL# USERNAME SESSION_INFO TX_NAME ROLLBACK OPERATION OPERATION_CODE SQL_REDO SQL_UNDO RS_ID SEQUENCE# SSN CSF INFO STATUS REDO_VALUE UNDO_VALUE SQL_COLUMN_TYPE SQL_COLUMN_NAME REDO_LENGTH REDO_OFFSET UNDO_LENGTH UNDO_OFFSET DATA_OBJV# SAFE_RESUME_SCN XID PXID AUDIT_SESSIONID
---------- ---------- ----------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------------------- -------------------------------------------------------------------------------- -------------------------------- ---------- -------------------------------- -------------------------------- ------------------ ---------- ---------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- -------------------------------- -------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------- ---------- ---------- ---------- -------------------------------- ---------- ---------- ---------- ------------------------------ ------------------------------ ----------- ----------- ----------- ----------- ---------- --------------- ---------------- ---------------- ---------------
376357 2012-10-22 1 49 6 16 214 6 16 214 49 264 16 2 0 0 0 2 5 12 51184 51184 DOLPHIN T04 T04 2 TABLE CHENT AAAMfwAAFAAAAAMAAA 1076 66 0 DELETE 2 delete from "DOLPHIN"."T04" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'ICOL$' an insert into "DOLPHIN"."T04"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID"," 0x000031.00000108.0010 1 0 0 2 100272 100273 0 0 0 0 1 06001000D6000000 06001000D6000000 84
376357 2012-10-22 1 49 6 16 214 6 16 214 49 265 96 2 8412134 29 76 2 5 12 51184 51184 DOLPHIN T04 T04 2 TABLE CHENT AAAMfwAAFAAAAAMAAB 1076 66 0 DELETE 2 delete from "DOLPHIN"."T04" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'I_USER1' insert into "DOLPHIN"."T04"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID"," 0x000031.00000109.0060 1 0 0 2 100274 100275 0 0 0 0 1 06001000D6000000 06001000D6000000 84
376357 2012-10-22 1 49 6 16 214 6 16 214 49 265 468 2 8412134 30 76 2 5 12 51184 51184 DOLPHIN T04 T04 2 TABLE CHENT AAAMfwAAFAAAAAMAAC 1076 66 0 DELETE 2 delete from "DOLPHIN"."T04" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'CON$' and insert into "DOLPHIN"."T04"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID"," 0x000031.00000109.01d4 1 0 0 2 100276 100277 0 0 0 0 1 06001000D6000000 06001000D6000000 84
376357 2012-10-22 1 49 6 16 214 6 16 214 49 266 340 2 8412134 31 76 2 5 12 51184 51184 DOLPHIN T04 T04 2 TABLE CHENT AAAMfwAAFAAAAAMAAD 1076 66 0 DELETE 2 delete from "DOLPHIN"."T04" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'UNDO$' an insert into "DOLPHIN"."T04"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID"," 0x000031.0000010a.0154 1 0 0 2 100278 100279 0 0 0 0 1 06001000D6000000 06001000D6000000 84
376357 2012-10-22 1 49 6 16 214 6 16 214 49 267 216 2 8412134 32 76 2 5 12 51184 51184 DOLPHIN T04 T04 2 TABLE CHENT AAAMfwAAFAAAAAMAAE 1076 66 0 DELETE 2 delete from "DOLPHIN"."T04" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'C_COBJ#' insert into "DOLPHIN"."T04"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID"," 0x000031.0000010b.00d8 1 0 0 2 100280 100281 0 0 0 0 1 06001000D6000000 06001000D6000000 84
376357 2012-10-22 1 49 6 16 214 6 16 214 49 268 92 2 8412134 33 76 2 5 12 51184 51184 DOLPHIN T04 T04 2 TABLE CHENT AAAMfwAAFAAAAAMAAF 1076 66 0 DELETE 2 delete from "DOLPHIN"."T04" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'I_OBJ#' a insert into "DOLPHIN"."T04"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID"," 0x000031.0000010c.005c 1 0 0 2 100282 100283 0 0 0 0 1 06001000D6000000 06001000D6000000 84
376357 2012-10-22 1 49 6 16 214 6 16 214 49 268 464 2 8412134 34 76 2 5 12 51184 51184 DOLPHIN T04 T04 2 TABLE CHENT AAAMfwAAFAAAAAMAAG 1076 66 0 DELETE 2 delete from "DOLPHIN"."T04" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'PROXY_ROL insert into "DOLPHIN"."T04"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID"," 0x000031.0000010c.01d0 1 0 0 2 100284 100285 0 0 0 0 1 06001000D6000000 06001000D6000000 84
376357 2012-10-22 1 49 6 16 214 6 16 214 49 269 348 2 8412134 35 76 2 5 12 51184 51184 DOLPHIN T04 T04 2 TABLE CHENT AAAMfwAAFAAAAAMAAH 1076 66 0 DELETE 2 delete from "DOLPHIN"."T04" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'I_IND1' a insert into "DOLPHIN"."T04"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID"," 0x000031.0000010d.015c 1 0 0 2 100286 100287 0 0 0 0 1 06001000D6000000 06001000D6000000 84
376357 2012-10-22 1 49 6 16 214 6 16 214 49 270 224 2 8412134 36 76 2 5 12 51184 51184 DOLPHIN T04 T04 2 TABLE CHENT AAAMfwAAFAAAAAMAAI 1076 66 0 DELETE 2 delete from "DOLPHIN"."T04" where "OWNER" &#
alter database add supplemental log data;
准备两张数据表:
create table t03 as select * from dba_objects;
create table t04 as select * from dba_objects;
下面我们通过logminer来分析truncate与delete在删除数据方面的效率(产生的日志越小，删除数据的效率越高)。
现在对表t03执行truncate操作，对表t04执行delete操作，然后通过logminer分析两种操作产生日志的大小：
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS
SQL> execute dbms_logmnr.add_logfile(LogFileName => '/u02/oradata/C2155/redo01.log',Options => dbms_logmnr.NEW);
PL/SQL procedure successfully completed
SQL> execute dbms_logmnr.add_logfile(LogFileName => '/u02/oradata/C2155/redo02.log',Options => dbms_logmnr.ADDFILE);
PL/SQL procedure successfully completed
SQL> execute dbms_logmnr.add_logfile(LogFileName => '/u02/oradata/C2155/redo03.log',Options => dbms_logmnr.ADDFILE);
PL/SQL procedure successfully completed
SQL> execute dbms_logmnr.start_logmnr(Options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed
SQL> select * from v$logmnr_contents where table_name='T03';
SCN CSCN TIMESTAMP COMMIT_TIMESTAMP THREAD# LOG_ID XIDUSN XIDSLT XIDSQN PXIDUSN PXIDSLT PXIDSQN RBASQN RBABLK RBABYTE UBAFIL UBABLK UBAREC UBASQN ABS_FILE# REL_FILE# DATA_BLK# DATA_OBJ# DATA_OBJD# SEG_OWNER SEG_NAME TABLE_NAME SEG_TYPE SEG_TYPE_NAME TABLE_SPACE ROW_ID SESSION# SERIAL# USERNAME SESSION_INFO TX_NAME ROLLBACK OPERATION OPERATION_CODE SQL_REDO SQL_UNDO RS_ID SEQUENCE# SSN CSF INFO STATUS REDO_VALUE UNDO_VALUE SQL_COLUMN_TYPE SQL_COLUMN_NAME REDO_LENGTH REDO_OFFSET UNDO_LENGTH UNDO_OFFSET DATA_OBJV# SAFE_RESUME_SCN XID PXID AUDIT_SESSIONID
---------- ---------- ----------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------------------- -------------------------------------------------------------------------------- -------------------------------- ---------- -------------------------------- -------------------------------- ------------------ ---------- ---------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- -------------------------------- -------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------- ---------- ---------- ---------- -------------------------------- ---------- ---------- ---------- ------------------------------ ------------------------------ ----------- ----------- ----------- ----------- ---------- --------------- ---------------- ---------------- ---------------
376280 2012-10-22 1 49 4 23 229 4 23 229 49 161 16 2 0 0 0 0 0 0 51183 0 DOLPHIN T03 T03 2 TABLE AAAAAAAAAAAAAAAAAB 1076 66 0 DDL 5 truncate table t03 0x000031.000000a1.0010 1 0 0 USER DDL (PlSql=0 RecDep=0) 0 160 161 0 0 0 0 1 04001700E5000000 04001700E5000000 84
此时我们可以发现执行truncate语句，仅产生一条日志记录。
SQL> select * from v$logmnr_contents where table_name='T04';
SCN CSCN TIMESTAMP COMMIT_TIMESTAMP THREAD# LOG_ID XIDUSN XIDSLT XIDSQN PXIDUSN PXIDSLT PXIDSQN RBASQN RBABLK RBABYTE UBAFIL UBABLK UBAREC UBASQN ABS_FILE# REL_FILE# DATA_BLK# DATA_OBJ# DATA_OBJD# SEG_OWNER SEG_NAME TABLE_NAME SEG_TYPE SEG_TYPE_NAME TABLE_SPACE ROW_ID SESSION# SERIAL# USERNAME SESSION_INFO TX_NAME ROLLBACK OPERATION OPERATION_CODE SQL_REDO SQL_UNDO RS_ID SEQUENCE# SSN CSF INFO STATUS REDO_VALUE UNDO_VALUE SQL_COLUMN_TYPE SQL_COLUMN_NAME REDO_LENGTH REDO_OFFSET UNDO_LENGTH UNDO_OFFSET DATA_OBJV# SAFE_RESUME_SCN XID PXID AUDIT_SESSIONID
---------- ---------- ----------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------------------- -------------------------------------------------------------------------------- -------------------------------- ---------- -------------------------------- -------------------------------- ------------------ ---------- ---------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- -------------------------------- -------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------- ---------- ---------- ---------- -------------------------------- ---------- ---------- ---------- ------------------------------ ------------------------------ ----------- ----------- ----------- ----------- ---------- --------------- ---------------- ---------------- ---------------
376357 2012-10-22 1 49 6 16 214 6 16 214 49 264 16 2 0 0 0 2 5 12 51184 51184 DOLPHIN T04 T04 2 TABLE CHENT AAAMfwAAFAAAAAMAAA 1076 66 0 DELETE 2 delete from "DOLPHIN"."T04" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'ICOL$' an insert into "DOLPHIN"."T04"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID"," 0x000031.00000108.0010 1 0 0 2 100272 100273 0 0 0 0 1 06001000D6000000 06001000D6000000 84
376357 2012-10-22 1 49 6 16 214 6 16 214 49 265 96 2 8412134 29 76 2 5 12 51184 51184 DOLPHIN T04 T04 2 TABLE CHENT AAAMfwAAFAAAAAMAAB 1076 66 0 DELETE 2 delete from "DOLPHIN"."T04" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'I_USER1' insert into "DOLPHIN"."T04"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID"," 0x000031.00000109.0060 1 0 0 2 100274 100275 0 0 0 0 1 06001000D6000000 06001000D6000000 84
376357 2012-10-22 1 49 6 16 214 6 16 214 49 265 468 2 8412134 30 76 2 5 12 51184 51184 DOLPHIN T04 T04 2 TABLE CHENT AAAMfwAAFAAAAAMAAC 1076 66 0 DELETE 2 delete from "DOLPHIN"."T04" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'CON$' and insert into "DOLPHIN"."T04"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID"," 0x000031.00000109.01d4 1 0 0 2 100276 100277 0 0 0 0 1 06001000D6000000 06001000D6000000 84
376357 2012-10-22 1 49 6 16 214 6 16 214 49 266 340 2 8412134 31 76 2 5 12 51184 51184 DOLPHIN T04 T04 2 TABLE CHENT AAAMfwAAFAAAAAMAAD 1076 66 0 DELETE 2 delete from "DOLPHIN"."T04" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'UNDO$' an insert into "DOLPHIN"."T04"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID"," 0x000031.0000010a.0154 1 0 0 2 100278 100279 0 0 0 0 1 06001000D6000000 06001000D6000000 84
376357 2012-10-22 1 49 6 16 214 6 16 214 49 267 216 2 8412134 32 76 2 5 12 51184 51184 DOLPHIN T04 T04 2 TABLE CHENT AAAMfwAAFAAAAAMAAE 1076 66 0 DELETE 2 delete from "DOLPHIN"."T04" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'C_COBJ#' insert into "DOLPHIN"."T04"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID"," 0x000031.0000010b.00d8 1 0 0 2 100280 100281 0 0 0 0 1 06001000D6000000 06001000D6000000 84
376357 2012-10-22 1 49 6 16 214 6 16 214 49 268 92 2 8412134 33 76 2 5 12 51184 51184 DOLPHIN T04 T04 2 TABLE CHENT AAAMfwAAFAAAAAMAAF 1076 66 0 DELETE 2 delete from "DOLPHIN"."T04" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'I_OBJ#' a insert into "DOLPHIN"."T04"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID"," 0x000031.0000010c.005c 1 0 0 2 100282 100283 0 0 0 0 1 06001000D6000000 06001000D6000000 84
376357 2012-10-22 1 49 6 16 214 6 16 214 49 268 464 2 8412134 34 76 2 5 12 51184 51184 DOLPHIN T04 T04 2 TABLE CHENT AAAMfwAAFAAAAAMAAG 1076 66 0 DELETE 2 delete from "DOLPHIN"."T04" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'PROXY_ROL insert into "DOLPHIN"."T04"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID"," 0x000031.0000010c.01d0 1 0 0 2 100284 100285 0 0 0 0 1 06001000D6000000 06001000D6000000 84
376357 2012-10-22 1 49 6 16 214 6 16 214 49 269 348 2 8412134 35 76 2 5 12 51184 51184 DOLPHIN T04 T04 2 TABLE CHENT AAAMfwAAFAAAAAMAAH 1076 66 0 DELETE 2 delete from "DOLPHIN"."T04" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'I_IND1' a insert into "DOLPHIN"."T04"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID"," 0x000031.0000010d.015c 1 0 0 2 100286 100287 0 0 0 0 1 06001000D6000000 06001000D6000000 84
376357 2012-10-22 1 49 6 16 214 6 16 214 49 270 224 2 8412134 36 76 2 5 12 51184 51184 DOLPHIN T04 T04 2 TABLE CHENT AAAMfwAAFAAAAAMAAI 1076 66 0 DELETE 2 delete from "DOLPHIN"."T04" where "OWNER" &#
最低0.47元/天 解锁文章
1895
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
