Skip to main content

Differences between Delete, Drop and Truncate Statements

DELETE, DROP and TRUNCATE statements differ in their nature itself.

  • DELETE statement is a DML (DATA MANIPULATION LANGUAGE) statement.
  • DROP AND TRUNCATE statements are DDL (DATA definition language) statements.

A DML action can be rolled back if the data is not committed, but DDL actions cannot be rolled back until Oracle 9i. With Oracle 9i FLASH BACK enabled even some of the DDL statements can be rolled back.

Delete

A delete statement deletes the data from a table. A delete statement can have a “where” clause, which need to be satisfied for the data to be deleted from the table.

Actions of Delete statement

  • Deletes the data from the current table space.
  • Modifies the undo table space with the delete records.
  • Executes all the before / after statement and row level triggers.
  • Updates the indexes (makes the index empty if the where clause is omitted).
  • Constraint checks are performed before deleting the rows

Syntax of a delete Statement:

DELETE FROM table [WHERE condition]

Truncate

Truncate drops all the records in a table. But as it is a DDL statement data cannot be retrieved. Usually truncate is faster than delete statement because there is no need to change or update the UNDO table space with the deleted records. Truncate is an implicit commit Statement. Truncate statement deal locates the space.

Actions of Truncate Statement

  • Removes all the records from the current table space.
  • Updates the indexes.
  • High watermark of the truncated table is reset.
  • Integrity Constraint checks are performed

Syntax of a Truncate Statement:

TRUNCATE TABLE table_name;

DROP STATEMENT

A drop statement removes the table object from the database. Structure and the data will be removed from the database. Action cannot be rolled back (but only by FLASH BACK from Oracle 9i).

Actions of a Drop Statement:

  • Constraint checks are performed before dropping.
  • All the data and structure of the table will be removed.
  • Updates the corresponding data dictionary views like dba_objects, dba_tables, user_tables e.t.c.
  • Updates the Status as “INVALID” for all the dependent objects.
  • Deallocates the Space

Syntax of a Drop Statement:

DROP TABLE table_name;
DROP TABLE table_name cascade constraints;

If “cascade constraints” is specified all foreign keys that reference the table are dropped and then table is dropped.

Comments

Popular posts from this blog

Drools - An overview

For Java based applications the most challenging part has always been the business logic maintenance, and pick any applications which you find complex and if we ask ourself how complex it would be moving forward, the answer will always be nX times.

What do we do ? Drools comes for Rescue as a Rule Engine.

Drools provides mechanism:

a. To write business logic in simple english language
b. Easy to maintain and very simple to extend
c. Reusability of logic by defining keywords in a DSL file and using them in DSLR file.

But be careful nothing comes free, everything takes cost in terms of memory and time space.

Use Drools if you really have :

a. Business logic which you think is getting cluttered with multiple if conditions because of variety of scenarios
b. You will have growing demand of increase in the complexity
c. The business logic changes would be frequent (1 - 2 times a year would also be frequent)
d. Your server's have enough of memory as it is a memory hungary tool, it provi…

Java 8 streams performance on mathematical calculations

Java 8 Streams API supports many parallel operations to process the data, it abstracts low level multithreading logic. To test performance did following simple test to calculate factorial of first X number starting with N.

Following program calculates factorial of first 1000 numbers.

package com.java.examples; import java.math.BigInteger; import java.time.Duration; import java.time.Instant; import java.util.ArrayList; import java.util.List; publicclassMathCalculation { publicstaticvoid main(String[] args) { List<Runnable> runnables = generateRunnables(1, 1000); Instant start= Instant.now(); // Comment one of the lines below to test parallel or sequential streams runnables.parallelStream().forEach(r -> r.run()); // runnables.stream().forEach(r -> r.run()); Instant end = Instant.now(); System.out.println("Calculated in "+ Duration.between(start, end)); } privatestaticvoid factorial(int number) { int i; BigInteger fact = BigInteger.valueOf(1); for (i =1; i <= numb…

MQTT : Android step by step guide using Eclipse Paho

For MQTT integration, recently explored Paho Android project, very simple to use, here are the steps:

Intialize a client, set required options and connect.

    MqttAndroidClient mqttClient = new MqttAndroidClient(BaseApplication.getAppContext(), broker, MQTT_CLIENT_ID);
    //Set call back class
    mqttClient.setCallback(new MqttCallbackHandler(BaseApplication.getAppContext()));
    MqttConnectOptions connOpts = new MqttConnectOptions();
    IMqttToken token = mqttClient.connect(connOpts);


Subscribe to a topic.

    token.setActionCallback(new IMqttActionListener() {
      @Override
      public void onSuccess(IMqttToken arg0) {
           mqttClient.subscribe("TOPIC_NAME" + userId, 2, null, new IMqttActionListener() {
                @Override
                public void onSuccess(IMqttToken asyncActionToken) {
                    Log.d(LOG_TAG, "Successfully subscribed to topic.");
                }

                @Override
                public void onFailure…