nologging對delete以及update效果不大
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效果明顯
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/13177610/viewspace-684562/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Hive學習筆記 ---- 支援Update和Delete以及MergeHive筆記delete
- update,delete與INNER JOIN 以及刪除重複資料delete
- Ask Hoegh(3)——對delete操作設定NOLOGGING能否加快速度?delete
- java-Mybatis XML 對映器(select,insert, update 和 delete)JavaMyBatisXMLdelete
- Default Locking for INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATE (351)delete
- myisam對於update,insert,delete關於auto_incremant的影響deleteREM
- innodb對於update,insert,delete關於auto_incremant的影響deleteREM
- mysql 5.5 中自連線update and deleteMySqldelete
- 34、VIEW可以insert,delete,update.Viewdelete
- 檢視insert,delete,update對基表的影響(檢視初識)delete
- 利用Open Interface表對BOM、Substitute、Reference Designater 進行Create、Update、Delete操作delete
- Sqlserver update\delete用inner join關聯,會update\delete關鍵字後面的表關聯到的行SQLServerdelete
- MySQL資料災難挽救之Delete\UpdateMySqldelete
- mysql innodb新建索引堵塞update ,insert,deleteMySql索引delete
- mssql sqlserver update delete表別名用法簡介SQLServerdelete
- SQLite語句(二):INSERT DELETE UPDATE SELECTSQLitedelete
- sql server 帶有OUTPUT的INSERT,DELETE,UPDATESQLServerdelete
- MongoDB入門系列(二):Insert、Update、Delete、DropMongoDBdelete
- 利用insert,update和delete注入獲取資料delete
- 比CRUD多一點兒(三):UPDATE、DELETE語句delete
- mysql 在delete、insert、update 時,page的變化MySqldelete
- sqlserver update或者Delete誤操作恢復方法---【驗證】SQLServerdelete
- MySQL之資料的insert-delete-update操作MySqldelete
- 【MySQL】ERROR 1175 安全模式UPDATE/DELETE操作失敗MySqlError模式delete
- MERGE新特性(UPDATE WHERE,DELETE WHERE,INSERT WHERE)delete
- 模擬insert,update和delete造成阻塞的示例delete
- LINQ to SQL語句之Insert/Update/Delete操作SQLdelete
- Oracle資料庫中Insert、Update、Delete操作速度Oracle資料庫delete
- Thinkphp 3.2.3 parseWhere設計缺陷導致update/delete注入 分析PHPdelete
- KunlunDB功能之insert/update/delete...returning語句delete
- 索引是否也能提高UPDATE,DELETE,INSERT速度 解釋索引delete
- 表連線時update與delete操作需注意的地方delete
- 恢復update,delete表資料錯誤的語句delete
- 輕量ORM-SqlRepoEx (四)INSERT、UPDATE、DELETE 語句ORMSQLdelete
- InnoDB事務鎖之行鎖-delete流程update階段加鎖delete
- Delete,insert,update與undo的關係[轉載TOM文章]delete
- nologging對備庫的影響
- 單表的更新UPDATE和刪除記錄DELETE(二十六)delete