What are
DDL commands ?
✔ DDL changes are
auto-committed (cannot be rolled back).
✔ Primarily affects schema and structure,
not the data itself.
✔ Used by database administrators (DBAs) and
developers to manage databases.
Common DDL Commands:
- CREATE
→ Creates new database objects like tables, indexes, views, etc.
CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Salary DECIMAL(10,2)
);
- ALTER
→ Modifies an existing database object (e.g., adding a column).
ALTER TABLE Employees
ADD COLUMN
Department VARCHAR(50);
- DROP
→ Deletes a database object (like a table or index) permanently.
DROP TABLE Employees;
- TRUNCATE
→ Removes all records from a table but keeps its structure.
(Drops the table and re-create
it from backend)
TRUNCATE TABLE Employees;
- RENAME
→ Renames a database object.
RENAME TABLE Employees TO
Staff;
- COMMENT
→ Adds comments to the database schema for documentation purposes.
COMMENT ON TABLE Employees IS
'Stores employee details';
TRNCATE v/s DELETE :
| Feature | TRUNCATE | DELETE |
|---|---|---|
| Definition | Removes all rows from a table quickly. | Removes specific rows or all rows using a WHERE condition. |
| Speed | Faster (DDL operation). | Slower (DML operation). |
| Logging | Minimal logging in Redo Logs (only metadata changes). | Fully logged in Redo & Undo Logs (row-by-row changes). |
| WHERE Clause Support | ❌ Not Allowed – Always removes all rows. | ✅ Allowed – Can delete specific rows. |
| Rollback (Undo) | ❌ Cannot be rolled back (Auto-commit). | ✅ Can be rolled back using ROLLBACK. |
| Triggers | ❌ Does not fire DELETE triggers. | ✅ Fires DELETE triggers. |
| Table Structure | ✅ Preserves the table structure, indexes, constraints, etc. | ✅ Preserves the table structure. |
| Auto-increment Counters | ✅ Resets the counter (in some databases, not Oracle). | ❌ Does not reset sequence values. |
| Foreign Key Restriction | ❌ Cannot truncate if a table has foreign key references. | ✅ Can delete referenced rows if ON DELETE CASCADE is enabled. |
Execute till Delete and check the size
CREATE TABLE TEST_EMP (
emp_id INT,
emp_name VARCHAR2(50)
);
BEGIN
FOR i IN 1..1000000 LOOP
INSERT INTO TEST_EMP (emp_id, emp_name)
VALUES (i, 'Employee_' || i);
END LOOP;
COMMIT;
END;
/
SELECT
segment_name AS table_name,
ROUND(SUM(bytes) / 1024 / 1024, 2) AS size_in_mb
FROM dba_segments
WHERE segment_name = 'TEST_EMP'
drop table test_emp;
CREATE TABLE TEST_EMP (
emp_id INT,
emp_name VARCHAR2(50)
);
BEGIN
FOR i IN 1..1000000 LOOP
INSERT INTO TEST_EMP (emp_id, emp_name)
VALUES (i, 'Employee_' || i);
END LOOP;
COMMIT;
END;
/
truncate table TEST_EMP;
Now Check Size
No comments:
Post a Comment