nologging对delete以及update效果不大
来源:互联网 发布:linux上新建weblogic域 编辑:程序博客网 时间:2024/06/05 06:21
Table created.
SQL> begin for i in 1..10000 loop
2 insert into a select i from dual;
3 end loop;
4 commit;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> insert into a select * from a;
10000 rows created.
SQL> desc a;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(38)
SQL> begin for i in 1..10000 loop
2 insert into a select i from dual;
3 end loop;
4 commit;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select count(*) from a;
COUNT(*)
----------
10000
SQL>
SQL> set autotrace traceonly;
SQL>
----------
--insert
SQL>insert into a select * from a;
10000 rows created.
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT ptimizer=ALL_ROWS (Cost=8 Card=20000 Bytes
=260000)
1 0 TABLE ACCESS (FULL) OF 'A' (TABLE) (Cost=8 Card=20000 Byte
s=260000)
Statistics
----------------------------------------------------------
58 recursive calls
10238 db block gets
3284 consistent gets
0 physical reads
2373796 redo size
643 bytes sent via SQL*Net to client
538 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10000 rows processed
SQL>insert into a nologging select * from a;
10000 rows created.
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT ptimizer=ALL_ROWS (Cost=8 Card=20000 Bytes
=260000)
1 0 TABLE ACCESS (FULL) OF 'A' (TABLE) (Cost=8 Card=20000 Byte
s=260000)
Statistics
----------------------------------------------------------
5 recursive calls
10193 db block gets
10101 consistent gets
0 physical reads
2370584 redo size
643 bytes sent via SQL*Net to client
548 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10000 rows processed
SQL> rollback;
Rollback complete.
SQL>insert /*+ append */ into a nologging select * from a;
10000 rows created.
Execution Plan
----------------------------------------------------------
Statistics
----------------------------------------------------------
93 recursive calls
45 db block gets
94 consistent gets
0 physical reads
136144 redo size
627 bytes sent via SQL*Net to client
562 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10000 rows processed
SQL>
SQL>
SQL> rollback;
---------------
--delete
SQL>delete from a;
10000 rows deleted.
Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT ptimizer=ALL_ROWS (Cost=1 Card=1 Bytes=3)
1 0 DELETE OF 'A'
2 1 INDEX (FULL SCAN) OF 'SYS_C005064' (INDEX (UNIQUE)) (Cos
t=1 Card=1 Bytes=3)
Statistics
----------------------------------------------------------
9 recursive calls
10450 db block gets
24 consistent gets
0 physical reads
2540712 redo size
642 bytes sent via SQL*Net to client
522 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10000 rows processed
SQL> rollback;
Rollback complete.
SQL>delete from a nologging;
10000 rows deleted.
Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT ptimizer=ALL_ROWS (Cost=1 Card=1 Bytes=3)
1 0 DELETE OF 'A'
2 1 INDEX (FULL SCAN) OF 'SYS_C005064' (INDEX (UNIQUE)) (Cos
t=1 Card=1 Bytes=3)
Statistics
----------------------------------------------------------
1 recursive calls
10437 db block gets
19 consistent gets
0 physical reads
2534564 redo size
642 bytes sent via SQL*Net to client
532 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10000 rows processed
结论:在非force logging情况下,delete、update的命令使用nologging效果不大,insert的时候配合append效果明显
- nologging对delete以及update效果不大
- nologging 对于delete update insert 是无效的
- MySQL数据库INSERT、UPDATE、DELETE以及REPLACE语句
- alter和update以及drop和delete区别
- jdbctemplate 的insert、update、delete以及select操作
- nologging
- nologging
- CTAS、Nologging以及数据库运行模式
- Nologging操作对standby的影响
- delete update sql join
- update delete left join
- Insert Update Delete Select
- Delete,Update与Join
- Insert , Update , Delete 触发器
- select update delete
- 视图Update Delete
- Insert,Update,Delete操作
- hive update delete
- Magento中创建商品时的Recurring Profile
- 实验一,调试
- ubuntu下git使用总结
- Android实现推送方式解决方案
- linux环境变量设置
- nologging对delete以及update效果不大
- NIO入门
- 腾讯2013年实习生笔试题
- 使用R语言的BNLearn包实现贝叶斯网络
- Linux常用命令(三) - pwd
- UVA - 11401 Triangle Counting
- ostringstream及相关
- UITableView中的代理方法的含义以及它的基本属性
- R语言时间序列函数整理