Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 111 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 161 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 475 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
insert and delete
just a DBA
Member Posts: 132 Blue Ribbon
I would like to clarify the situation.
1. create table t1 as select * from t2.
2. delete from t1.
Why second statement is several times slower than insert if it just moves the same amount of data into undo segments ?
1. create table t1 as select * from t2.
2. delete from t1.
Why second statement is several times slower than insert if it just moves the same amount of data into undo segments ?
Best Answer
-
just a DBA wrote:There's an optimisation that Oracle can use on the insert that it doesn't use on the delete.Dave Rabone wrote:Sorry, not clear. For example, 'Insert' inserts 100 Mbytes to data segments. 'Delete' just moves this data into undo. Why redo is so different?
... and that at first glance is counterintuitive because the reverse argument should apply to redo, until you recognize that undo is also protected by redo.
Imagine you insert 80,000 rows of 100 bytes each - for a total of about 80MB raw data, with 80 rows per block, 1,000 blocks.
On the insert Oracle can optimise the undo and redo sizes by creating "array based" undo records and redo vectors, so the overheads amount to about 200 or so bytes per block in the table.
On the delete this optimisation doesn't apply - so the overheads amount to about 200 or so bytes per row.
If you check your figures you'll probably see that the difference in redo is about 200 to 250 bytes per row inserted.
Regards
Jonathan Lewis
Answers
-
What data do you need to undo an insert? No data, just the rowid that was created.
What data do you need to undo a delete? The complete row. -
SQL> truncate table q; SQL> insert into q select * from dba_objects ; Statistics --------------------------------------------------- 62 recursive calls 9017 db block gets 2716 consistent gets 3 physical reads 7636776 redo size 64851 rows processed SQL> delete from q; Statistics --------------------------------------------------- 22 recursive calls 72934 db block gets 1072 consistent gets 0 physical reads 25324064 redo size 64851 rows processedSee difference in redo size. -
What data do you need to undo an insert? No data, just the rowid that was created.But "complete row" is inserted also to data tablespace.
What data do you need to undo a delete? The complete row. -
... and that at first glance is counterintuitive because the reverse argument should apply to redo, until you recognize that undo is also protected by redo.
-
Dave Rabone wrote:Sorry, not clear. For example, 'Insert' inserts 100 Mbytes to data segments. 'Delete' just moves this data into undo. Why redo is so different?
... and that at first glance is counterintuitive because the reverse argument should apply to redo, until you recognize that undo is also protected by redo. -
Because the operations that change both the undo segments and data segments that are also recorded in redo.
Go back and reread the concepts guide (several times) and understand the beautifully elegant way that the undo mechanism provides read consistency, rollback and statement level flashback and the redo mechanism gives recovery capabilities against software and hardware failure.
The key thing is that data and undo segments use the same recovery mechanism, the redo. -
'Delete' just moves this data into undo
No, both, Redo and undo are generated with insert and delete. Redo and Undo, are blocks and works equally.
The undo generated will be enough information to make the data "go away", the redo generated will be enough information to make the insert "happen again".
In your example, imagine that you create the new table inserting data from other table. Your table haven't anything "before" so undo is less than redo. When you delete the table, you have info "before" and "data to save" for "re-write" the table.
The delete makes more undo information because it has "before" data. That's why it need more time to finish.
from:
http://www.google.es/url?sa=t&rct=j&q=insert%20redo&source=web&cd=2&cad=rja&ved=0CD4QFjAB&url=http%3A%2F%2Fasktom.oracle.com%2Fpls%2Fapex%2Fz%3Fp_url%3DASKTOM.download_file%253Fp_file%253D1435150530862588762%26p_cat%3Dundo_redo.pdf%26p_company%3D822925097021874&ei=45wsUcDpKIuJhQfYroDQDQ&usg=AFQjCNFtDLW6XvP2aDN8zDCPki92UcK95A&bvm=bv.42965579,d.ZG4 -
Good document, but it doesn't explain in detail.
For example, 'insert' inserts 100Mb into data blocks. Undo size for this is small, assume, 1Mb. So total redo will be about 101Mb.
'Delete' just copies these 100Mb blocks into undo segments and marks data blocks as free. So total redo should be also about 100Mb ?! But it is several times more! Why? -
For example, 'insert' inserts 100Mb into data blocks. Undo size for this is small, assume, 1Mb. So total redo will be about 101Mb.1.When you insert rows, database populates data blocks. In this case size of redo will be about size of inserted blocks plus some small amount of undo blocks.
'Delete' just copies these 100Mb blocks into undo segments and marks data blocks as free. So total redo should be also about 100Mb ?! But it is several times more! Why?
2.When you delete rows, database does not just copy original rows to undo, but creates undo records. Undo record for delete contains before images for all columns and is larger in size that usual data record. Therefore total amount of undo blocks will be more than total data blocks.
3.When you update rows, redo will include undo records and new data. The more columns are updated, the larger size of undo record and more new data. Therefore update operation can even generate more redo than delete. Updating all columns will generate almost the same redo as for delete + insert. -
just a DBA wrote:There's an optimisation that Oracle can use on the insert that it doesn't use on the delete.Dave Rabone wrote:Sorry, not clear. For example, 'Insert' inserts 100 Mbytes to data segments. 'Delete' just moves this data into undo. Why redo is so different?
... and that at first glance is counterintuitive because the reverse argument should apply to redo, until you recognize that undo is also protected by redo.
Imagine you insert 80,000 rows of 100 bytes each - for a total of about 80MB raw data, with 80 rows per block, 1,000 blocks.
On the insert Oracle can optimise the undo and redo sizes by creating "array based" undo records and redo vectors, so the overheads amount to about 200 or so bytes per block in the table.
On the delete this optimisation doesn't apply - so the overheads amount to about 200 or so bytes per row.
If you check your figures you'll probably see that the difference in redo is about 200 to 250 bytes per row inserted.
Regards
Jonathan Lewis
This discussion has been closed.