Data Definition Language & little more...

Data Definition Language (DDL): Data Definition Language statement defines structurally change and drop of schema objects. For example, DDL statements enable you to Create, Alter and Drop schema's objects and other database structure including the database and users. Most DDL statements starts with the keywords CREATE, ALTER, DROP and TRUNCATE.

DDL enables you to alter attributes of an object without altering the application that accesses the object. For example, you can add/drop a column to a table accessed by a human resources application without rewriting the application. You can also use DDL to alter the structure of objects while database users are performing work in the database.

Below, DDL statements creates the plants table and then uses DML statements  to insert two records into the table. The example then uses DDL to alter the table structure and then drops the table.

    ( plant_id    NUMBER PRIMARY KEY,
      common_name VARCHAR2(15) );

INSERT INTO plants VALUES (1, ‘African Violet’); # DML statement
INSERT INTO plants VALUES (2, ‘Amaryllis’); # DML statement

ALTER TABLE plants ADD  ( latin_name VARCHAR2(40) ); # DDL

DROP TABLE plants; # DDL

Note: An implicit COMMIT occurs immediately before the database executes a DDL statement and a COMMIT or ROLLBACK occurs immediately afterward. In the above example, the two INSERT statements are followed by ALTER TABLE statements, so that database commits the two INSERT statements. If the ALTER TABLE statements succeed, then the database commits this statement; otherwise the database rolls back statement

& More ....

TRUNCATE: Deletes all the data from a schema objects without removing the structure of these objects. Truncate generates no undo logs, therefore it is faster than a delete but the transaction can not be rolled back. Also, TRUNCATE does not invoke delete triggers.

Delete Vs Truncate
DELETE: Deletes row from a table and  the change should be committed after completion to make the delete permanent. Delete can be roll backed because it generates undo logs. TRUNCATE deletes all the rows from a table without creating undo files and it cannot be rolled back (at least in Oracle db). Truncate is much faster than a delete. The change is permanent for TRUNCATE.  Drop and Truncate are DDL statements and cannot be rolled back.

Drop Vs Purge Vs. Cascade:
DROP: drops the data along with the structure of a table. This also removes all the index and privileges to the objects. Drop will  move the table to recycle bin. Garbage collector will  later remove it from a bin, the dropped space will not be available right a way.

PURGE: Dropping table with PURGE will drop the table from the system. The table can’t be recovered from recycle bin because the dropped object is not moved to recycle bin. Be  extra careful while using PURGE.

CASCADE: Deletes all foreign keys and then drops the table. This is another command that I have to use it.

Knowing the difference of these keywords help you make an informed decision on when to use TRUNCATE/DROP/DELETE/PURGE. This also saves you from being in a mess by grinding a wrong DDL statements.
Interested in working with me? I can be reached at pbaniya04[at] for any questions, consulting opportunities or you may drop a line to say HELLO. Thank your again for visiting my blog and looking forward to serving you more.

Have a Database-ious Day!

No comments

Powered by Blogger.