?
Solved

redo log files

Posted on 1999-07-22
9
Medium Priority
?
1,963 Views
Last Modified: 2008-01-09
Oracle 7.3.4, archive mode is on

I have a big table and i want to remove this table with a
minimum redo log files entries.

Can you explain the difference on redo log files activity
for these statements:

drop table toto

truncate table toto

delete from toto

Thanks
0
Comment
Question by:djamal
  • 5
  • 2
  • 2
9 Comments
 
LVL 6

Accepted Solution

by:
mshaikh earned 90 total points
ID: 1087665
delete will produce redo log info. Thus it will take a long time on large table.

drop table and truncate table will not produce redo log info that can be used by the user. Thus they will be fast regardless of the size of the table. Truncate table will give the same result as delete except you can't rollback.
Truncate unlike drop ta ble will preserve the table structure and relationships etc.
0
 
LVL 6

Expert Comment

by:mshaikh
ID: 1087666
If you want more clarification on any one particular point, then just ask.
0
 
LVL 2

Expert Comment

by:avico
ID: 1087667
Drop table - (DDL) Free the blocks used by the table. Also updates the data dictionary to designate the table does not exist. This operation generates redo logs records. Generates a DDL exclusive lock on the table until it dropped.

Delete table - (DML) Deletes all rows in the table. Doesn't deallocate the storage (extents etc.). Generates much redo log records. Also locks the table in row-exclusive mode until the next commit or rollback is issued.

Truncate table - (DDL) Simply resets the number of rows in a table to 0. Doesn't require a commit (since a DDL statement always issues a commit). Frees the table's blocks and extents.Generate a very few log records.

If you need to delete all data from a table, Truncate would be the less consuming operation.

Hope it helped, Avico.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:djamal
ID: 1087668
Mr avico say the drop table table generate redo logs records
and Mr mshaikh say the drop table table does not generate
redo logs records.

I need more information

0
 
LVL 6

Expert Comment

by:mshaikh
ID: 1087669
I am sorry, I ment 'rollback' information not redo log. In my answer replace all 'redo log' with 'rollback' and then it will become what I was trying to say. So some reason, I was reading your question wrong.

'Redo logs' records will be generated for all three of these statements regardless.

I apologize.

0
 
LVL 6

Expert Comment

by:mshaikh
ID: 1087670
Also, you can't rollback 'drop table' and 'truncate table', you can rollback delete table thats why it takes so long.


0
 

Author Comment

by:djamal
ID: 1087671
where can i find more information on redo log files entries
for the previous statement?
0
 
LVL 2

Expert Comment

by:avico
ID: 1087672
As I wrote before, if you have have a big table and you  want to remove all data from this table, the least redo log consuming operation would be 'truncate table'.
However, if you would like to remove this table from the database, the only way of doing so is 'drop table'.

If you need to delete selctive rows, the only way to do so is by using the 'delete table'.



0
 
LVL 6

Expert Comment

by:mshaikh
ID: 1087673
Redo log only record all the activity done to the database, and is used to ROLL FORWARD to a certain point of time after a database recovery from a backup. All redo logs do is record changes to the database. If you drop a table it accually records the drop table command. If you truncate a table it will record that too, it you delete a table it will record the delete statment. In case you had to recover from a database crash or loss. You recover to your last backup, then you apply archived redo logs and redo logs to imitate all the change actions (SQL DDL and DML) done on the database including the uncommited changes. Redo log record are noting more than the DDL and DML statement executed on the database in the sequence they were executed.
For example: if you create a table, populate it and then drop it, Redo logs will record each of theses changes. If these redo log  were to be applied incase of crash, the redo logs will acctually create the table, then populate it and then drop it exactly immitating what was before the crash. It is not smart enough to see that the table does not need to be created beacause it was eventually dropped.

Some more info from Oracle Documnetation:

Truncating Tables

You can use the TRUNCATE command to quickly remove all rows from a table or cluster. Removing rows with the TRUNCATE command is faster than removing them with the DELETE command for the following reasons:

The TRUNCATE command is a data definition language (DDL) command and generates no rollback information.
Truncating a table does not fire the table's DELETE triggers.
The TRUNCATE command allows you to optionally deallocate the space freed by the deleted rows. The DROP STORAGE option deallocates all but the space specified by the table's MINEXTENTS parameter.

Deleting rows with the TRUNCATE command is also more convenient than dropping and re-creating a table because dropping and re-creating:

invalidates the table's dependent objects, while truncating does not
requires you to regrant object privileges on the table, while truncating does not
requires you to re-create the table's indexes, integrity constraints, and triggers and respecify its STORAGE parameters, while truncating does not

--------------------------------------------------------------------------------
Note:

When you truncate a table, the storage parameter NEXT is changed to be the size of the last extent deleted from the segment in the process of truncation.

 
--------------------------------------------------------------------------------
 
 


Restrictions
When you truncate a table, NEXT is automatically reset to the last extent deleted.

You cannot individually truncate a table that is part of a cluster. You must either truncate the cluster, delete all rows from the table, or drop and re-create the table.

You cannot truncate the parent table of an enabled referential integrity constraint. You must disable the constraint before truncating the table. (An exception is that you may truncate the table if the integrity constraint is self-referential.)

You cannot roll back a TRUNCATE statement.


0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question