DDL Statements

DDL (stands for Data Definition Language) statements are used for objects creation. I might write a book on this topic because there are many different objects in a database and every object has its own specifics. Not only there is a specific definition per object but also various ways how to define the object. I will not describe every single possibility and elaborate on every option you have for each object. Instead, I will focus on basic definition with some elementary recommendations and I will point out basic mistakes I noticed people tend to do.

All DDL commands do NOT support transactions! That means they are auto-committed and every change is permanently written.

I listed all DDL commands in the very first article about DBMS and other relational models and I will now pick the most commonly used. Regarding the rest, I might write DDL statements II and familiarize you with them there 🙂

Here, in this article I will focus on the following list of commands:

• Create
• Alter / Rename
• Drop
• Truncate

I mentioned that Oracle has many different objects. I will limit this article to the most commonly used objects by analysts and developers to:

• Table
• View
• Index

I will briefly but still sufficiently describe each DDL with regard to the list of objects mentioned above. The rest of objects will have its own dedicated articles.

Create

CREATE command will help you create all objects. Let me show you several examples of usage.

Table

You can create an empty table structure as well as an empty structure that will be automatically populated with data.

-- Empty structure creation
CREATE TABLE my_table (
  id INT NOT NULL,
  name VARCHAR2(50),
  desc VARCHAR2(255),
  dtime_inserted DATE DEFAULT sysdate NOT NULL  
);

There are many other options you can use to create the table properly or to enforce data integrity. You can see them in the article about Constraints.

-- CTAS (Create Table As Select)
CREATE TABLE my_table AS
SELECT id
, name
, desc
, sysdate
FROM source_table;

This way is really effective and as long as you already have the data inside the database I prefer using this approach. You don’t have to define any data types and everything is automatically created. Besides, it does not generate UNDO and therefore is way faster and resource friendly, compared to CREATE and INSERT combination. You can define your constraints and do the other configuration tasks rights after. If you still prefer to do that right away, you can easily do that this way

-- CTAS (Create Table As Select)
CREATE TABLE my_table (id, name, desc NOT NULL, dtime_inserted DEFAULT sysdate NOT NULL) AS
SELECT id
, name
, desc
, sysdate
FROM source_table;

View

-- view creation
CREATE OR REPLACE VIEW v_data_set
AS
SELECT *
FROM my_source_table
;

Index

CREATE INDEX idx_name ON my_table(column_name);

Alter

ALTER command will help you change the structure of your objects.

Table

-- add column
ALTER TABLE my_table ADD col1 DATE;

-- add column with constraints
ALTER TABLE my_table ADD col1 DATE DEFAULT sysdate;

-- add multiple columns
ALTER TABLE my_table ADD ( col1 DATE,
                           col2 VARCHAR2(50) DEFAULT 'XNA',
                           col3 INT);

-- change column definition (col1 originaly varchar2(50))
-- you might get an error if you wanna apply incompatible data types or use constraint that can't be enforced (already have NULL values, etc)
-- you have to first fix the data inside the table
-- you can as well modify multiple columns as well the same way as you can add multiple columns
ALTER TABLE my_table MODIFY col1  varchar2(100) NOT NULL;

-- drop column
ALTER TABLE my_table DROP col1;

-- rename column
ALTER TABLE my_table RENAME COLUMN col1 TO col_1;

-- rename table
ALTER TABLE my_table RENAME TO my_table_new;

-- but it's better to use the RENAME command 
RENAME my_table TO my_table_new; -- I prefer this one

Drop

Drop command removes the object from the database.

-- general template
DROP <object_type> <object_name>

-- table
DROP TABLE my_table;

-- view
DROP VIEW my_view;

-- index
DROP INDEX my_index;

Truncate

The truncate command deletes the content of a table. See the difference between truncate and delete commands.

DELETE	TRUNCATE
DML command	DDL command (no triggers can be fired – non SYS; SYS triggers are fired)
Can be rolled back (undone)	Cannot be rolled back
Can use WHERE clause	Cannot use WHERE clause
Requires commit	Does not require commit (all changes are automatically written)
Worse performance	Minimal logging in a transcation log (better performance)
Anyone can perform (if granted)	Only owner can perform
Uses row-lock	Uses table-lock
Identity key is not reset	Identity key is reset to the initial defined values
Does not reset HWM	Resets HWM