Decode Vs Case

Everything DECODE can do, CASE can. There is a lot more that you can do with CASE, though, which DECODE cannot.
Differences between DECODE and CASE are listed below:

---

1. DECODE performs an equality check only but CASE can work with logical operators other than "=".
2. DECODE works with expressions that are scalar values only. CASE can work with predicates and subqueries in searchable form.
3. CASE is a statement while DECODE is a function.
4. DECODE and CASE treats NULL differently. Normally, including CASE, NULL = NULL results in NULL, however when DECODE compares NULL with NULL result is TRUE.
5. CASE expects datatype consistency, DECODE does not.
6. CASE complies with ANSI SQL. DECODE is proprietary to Oracle.

Example 1: DECODE and CASE treats NULL differently

SELECT  DECODE(NULL,NULL,-1) decode,

            CASE NULL WHEN NULL THEN -1 END case

 FROM  DUAL;
/

Output:

  DECODE       CASE
---------- ----------

        -1

Example 2: CASE expects datatype consistency, DECODE does not

SELECT  CASE 2 WHEN 1 THEN 1 WHEN to_number('2') THEN to_number('2') END case
FROM  DUAL
/
Output:
      CASE
----------
         2

SELECT  DECODE(2,1,1,'2','2') decode
FROM  DUAL
/

DECODE
-------
     2

SELECT  CASE 2 WHEN 1 THEN 1 WHEN '2' THEN '2'  END case
FROM  DUAL
/
SELECT  CASE 2 WHEN 1 THEN 1 WHEN '2' THEN '2'  END case
                                  *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

Relational Vs Nested Table

What is the difference between relational table and nested table?

• A relational table can have virtual column, whereas nested table can not have virtual column.
• Primary key, foreign keys are allowed on a relational table, whereas constraints are not allowed on nested table.

Truncate Vs Delete

What is the difference between truncate and delete?

1.  Truncate deletes all records at once unconditionally, whereas delete can delete the records conditionally or unconditionally.
2. Deleted data by truncate command can not be rolled back, whereas deleted data by delete command can be rolled back.
3. Memory will be released after deletion of record by truncate command whereas memory will not be released after deletion of record by deleted command.
4. Truncate is a DDL command, whereas delete is DML command.
5. Trigger does not get fired in case of  TRUNCATE whereas triggers get fired in case of a DELETE command.
6. We can not TRUNCATE a table if table have any foreign key constraint. We need to remove the constraint  then truncate the table and recreate the constraint.

Long Vs Lob Data Type

What is difference between LONG and LOB data types?

Differences between LONG and LOB data type are listed below:

---

LOB

 1) The maximum size is 4GB.

 2) LOBs (except NCLOB) can be attributes of an object type.

 3) LOBs support random access to data.

 4) Multiple LOB columns per table or LOB attributes in an object type.

LONG

 1) The maximum size is 2GB.

 2) LONGs cannot.

 3) LONGs support only sequential access.

 4) Only one LONG column was allowed in a table