Difference between View and Materialized View

What is View in database
Views are logical virtual table created by “select query” but the result is not stored anywhere in the disk and every time we need to fire the query when we need data, so always we get updated or latest data from original tables. Performance of the view depend upon our select query. If we want to improve the performance of view we should avoid to use join statement in our query or if we need multiple joins between table always try to use index based column for joining as we know index based columns are faster than non index based column. View allow to store definition of the query in the database itself.

What is Materialized View in database
Materialized views are also logical view of our data driven by select query but the result of the query will get stored in the table or disk, also definition of the query will also store in the database .When we see the performance of Materialized view it is better than normal View because the data of materialized view will stored in table and table may be indexed so faster for joining also joining is done at the time of materialized views refresh time so no need to every time fire join statement as in case of view.


Difference between View vs Materialized View in database
Based upon on our understanding of View and Materialized View, Let’s see, some short difference between them :

1) First difference between View and materialized view is that, In Views query result is not stored in the disk or database but Materialized view allow to store query result in disk or table.

2) Another difference between View vs materialized view is that, when we create view using any table,  rowid of view is same as original table but in case of Materialized view rowid is different.

3) One more difference between View and materialized view in database is that, In case of View we always get latest data but in case of Materialized view we need to refresh the view for getting latest data.

4) Performance of View is less than Materialized view.

5) This is continuation of first difference between View and Materialized View, In case of view its only the logical view of table no separate copy of table but in case of Materialized view we get physically separate copy of table

6) Last difference between View vs Materialized View is that, In case of Materialized view we need extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in database.

When to Use View vs Materialized View in SQL
Mostly in application we use views because they are more feasible,  only logical representation of table data no extra space needed. We easily get replica of data and we can perform our operation on that data without affecting actual table data but when we see performance which is crucial for large application they use materialized view where Query Response time matters so Materialized views are used mostly with data ware housing or business intelligence application.

That’s all on difference between View and materialized View in database or SQL. I suggest always prepare this question in good detail and if you can get some hands on practice like creating Views, getting data from Views then try that as well.

Brief:
Views evaluate the data in the tables underlying the view definition at the time the view is queried. It is a logical view of your tables, with no data stored anywhere else. The upside of a view is that it will always return the latest data to you. The downside of a view is that its performance depends on how good a select statement the view is based on. If the select statement used by the view joins many tables, or uses joins based on non-indexed columns, the view could perform poorly.

Materialized views are similar to regular views, in that they are a logical view of your data (based on a select statement), however, the underlying query resultset has been saved to a table. The upside of this is that when you query a materialized view, you are querying a table, which may also be indexed. In addition, because all the joins have been resolved at materialized view refresh time, you pay the price of the join once (or as often as you refresh your materialized view), rather than each time you select from the materialized view. In addition, with query rewrite enabled, Oracle can optimize a query that selects from the source of your materialized view in such a way that it instead reads from your materialized view. In situations where you create materialized views as forms of aggregate tables, or as copies of frequently executed queries, this can greatly speed up the response time of your end user application. The downside though is that the data you get back from the materialized view is only as up to date as the last time the materialized view has been refreshed.

Materialized views can be set to refresh manually, on a set schedule, or based on the database detecting a change in data from one of the underlying tables. Materialized views can be incrementally updated by combining them with materialized view logs, which act as change data capture sources on the underlying tables.

Materialized views are most often used in data warehousing / business intelligence applications where querying large fact tables with thousands of millions of rows would result in query response times that resulted in an unusable application.

Working with Collections

 An Oracle PL/SQL collection is a single-dimensional array; it consists of one or more elements accessible through an index value. A collection groups elements of the same type in a specified order. Each element has a unique subscript that determines its position in the collection.

PL/SQL has three kinds of collections:

• Associative arrays (formerly called "PL/SQL tables" or "index-by tables")
• Nested tables
• Variable-size arrays (varrays)

Associative arrays, also known as index-by tables, let you look up elements using arbitrary numbers and strings for subscript values. These are similar to hash tables in other programming languages.

Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.

Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

To use collections in an application, you define one or more PL/SQL types, then define variables of those types. You can define collection types in a procedure, function, or package. You can pass collection variables as parameters to stored subprograms.

To look up data that is more complex than single values, you can store PL/SQL records or SQL object types in collections. Nested tables and varrays can also be attributes of object types.

Note:

This topic applies to collection types that you define inside a PL/SQL block or package, which are different from standalone stored collection types that you create with the CREATE TYPE Statement.

In a PL/SQL block or package, you can define all three collection types. With the CREATE TYPE statement, you can create nested table types and varray types, but not associative array types.

Syntax

collection_type_definition ::=
TYPE type_name IS 
{ assoc_array_type_def
| nested_table_type_def
| varray_type_def
} ;

assoc_array_type_def ::=
TABLE OF element_type [ NOT NULL ]
[ INDEX BY { PLS_INTEGER | BINARY_INTEGER | VARCHAR2 ( v_size ) } ]

nested_table_type_def ::=
TABLE OF element_type [ NOT NULL ]

varray_type_def ::=
{ VARRAY | VARYING ARRAY } ( size_limit )
OF element_type [ NOT NULL ]

collection_variable_dec ::=
collection_name type_name ;

element_type ::=
{ cursor_name%ROWTYPE
| db_table_name{%ROWTYPE | .column_name%TYPE}
| object_name%TYPE
| [REF] object_type_name
| scalar_datatype_name
| record_name[.field_name]%TYPE
| record_type_name
| variable_name%TYPE
}

Keyword and Parameter Description

collection_name

The name that you give to the variable of the collection type that you defined.

element_type

The data type of the collection element (any PL/SQL data type except REF CURSOR).

For a nested table:

• If element_type is an object type, then the nested table type describes a table whose columns match the name and attributes of the object type.
• If element_type is a scalar type, then the nested table type describes a table with a single, scalar type column called COLUMN_VALUE.
• You cannot specify NCLOB for element_type. However, you can specify CLOB or BLOB.

INDEX BY

For an associative array, the data type of its indexes—PLS_INTEGER, BINARY_INTGER, or VARCHAR2.

NOT NULL

Specifies that no element of the collection can have the value NULL.

type_name

The name that you give to the collection type that you are defining.

v_size

For an associative array, the length of the VARCHAR2 key by which it is indexed.

The type definition of a nested table or varray can appear either in the declarative part of a block, subprogram, package specification, or package body (in which case it is local to the block, subprogram, or package) or in the CREATE TYPE Statement (in which case it is a standalone stored type).

Understanding Associative Arrays (Index-By Tables)
============================================
Associative arrays are sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be an integer or a string.

Assigning a value using a key for the first time adds that key to the associative array. Subsequent assignments using the same key update the same entry. It is important to choose a key that is unique. For example, key values might come from the primary key of a database table, from a numeric hash function, or from concatenating strings to form a unique string value.

Declaring Index-By-Table Collection Type:
DECLARE  TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
  country_population population_type;
  continent_population population_type;
  howmany NUMBER;
  which VARCHAR2(64);
BEGIN
  country_population('Greenland') := 100000; -- Creates new entry
  country_population('Iceland') := 750000;   -- Creates new entry
-- Looks up value associated with a string
  howmany := country_population('Greenland');
  continent_population('Australia') := 30000000;
  continent_population('Antarctica') := 1000; -- Creates new entry
  continent_population('Antarctica') := 1001; -- Replaces previous value 
-- Returns 'Antarctica' as that comes first alphabetically.
  which := continent_population.FIRST;
-- Returns 'Australia' as that comes last alphabetically.  which := continent_population.LAST;
-- Returns the value corresponding to the last key, in this
-- case the population of Australia.
  howmany := continent_population(continent_population.LAST);
END;
/

Associative arrays help you represent data sets of arbitrary size, with fast lookup for an individual element without knowing its position within the array and without having to loop through all the array elements. It is like a simple version of a SQL table where you can retrieve values based on the primary key. For simple temporary storage of lookup data, associative arrays let you avoid using the disk space and network operations required for SQL tables.

Because associative arrays are intended for temporary data rather than storing persistent data, you cannot use them with SQL statements such as INSERT and SELECT INTO. You can make them persistent for the life of a database session by declaring the type in a package and assigning the values in a package body.

Understanding Nested Tables

=========================

PL/SQL nested tables represent sets of values. You can think of them as one-dimensional arrays with no declared number of elements. You can model multi-dimensional arrays by creating nested tables whose elements are also nested tables.
Within the database, nested tables are column types that hold sets of values. Oracle stores the rows of a nested table in no particular order. When you retrieve a nested table from the database into a PL/SQL variable, the rows are given consecutive subscripts starting at 1. That gives you array-like access to individual rows.

Declaring Nested-Table Collection Type:

1  DECLARE

  2     TYPE list_of_names_t IS TABLE OF VARCHAR2 (100);
3

    happyfamily   list_of_names_t := list_of_names_t ();
  5
4
      children      list_of_names_t := list_of_names_t ();

;
  7  BEGIN
  8     happyfamily.EXTEND (4);
  9     happyfa
6     parents       list_of_names_t := list_of_names_t (
)mily (1) := ‘Veva’;
 10     happyfamily (2) := ‘Chris’;
 11     happyfamily (3) := ‘Eli’;

n.LAST) := ‘Chris’;
 16     children
12     happyfamily (4) := ‘Steven’;
 13
 14     children.EXTEND;
 15     children (childr
e.EXTEND;
 17     children (children.LAST) := ‘Eli’;
 18
 19     parents := happyfamily MULTISET EXCEPT children;
 20

OP;
 25  END;
21     FOR l_row IN 1 .. parents.COUNT
 22     LOOP
 23        DBMS_OUTPUT.put_line (parents (l_row));
 24     END L

O

Nested tables differ from arrays in two important ways:

Nested tables do not have a declared number of elements, while arrays have a predefined number as illustrated in Figure below. The size of a nested table can increase dynamically; however, a maximum limit is imposed. 
Nested tables might not have consecutive subscripts, while arrays are always dense (have consecutive subscripts). Initially, nested tables are dense, but they can become sparse (have nonconsecutive subscripts). You can delete elements from a nested table using the built-in procedure DELETE. The built-in function NEXT lets you iterate over all the subscripts of a nested table, even if the sequence has gaps.

Understanding Varrays

====================

Items of type VARRAY are called varrays. They let you reference individual elements for array operations, or manipulate the collection as a whole. To reference an element, you use standard subscripting syntax (see Figure below). For example, Grade(3) references the third element in varray Grades.
A varray has a maximum size, which you specify in its type definition. Its index has a fixed lower bound of 1 and an extensible upper bound. For example, the current upper bound for varray Grades is 7, but you can increase its upper bound to maximum of 10. A varray can contain a varying number of elements, from zero (when empty) to the maximum specified in its type definition.

Declare a varray of numbers:

TYPE numbers_vat IS VARRAY(10) 

OF NUMBER; 

Declare a varray of Records:

DECLARE TYPE name_rec

  IS RECORD ( first_name VARCHAR2(20), last_name VARCHAR2(25));

   TYPE names IS VARRAY(250) OF name_rec;

BEGIN

   NULL;

END;

/

Initializes a varray using a constructor, which looks like a function with the same name as the collection type:

DECLARE

-- In the varray, we put an upper limit on the number of elements

   TYPE dnames_var IS VARRAY(20) OF VARCHAR2(30);

   dept_names dnames_var;

BEGIN

-- Because dnames is declared as VARRAY(20), we can put up to 10-- elements in the constructor

   dept_names := dnames_var('Shipping','Sales','Finance','Payroll');

END;

/

Choosing Between Nested Tables and Associative Arrays

Nested tables and associative arrays differ in persistence and ease of parameter passing.

A nested table can be stored in a database column; therefore, you can use a nested table to simplify SQL operations in which you join a single-column table with a larger table. An associative array cannot be stored in the database.

An associative array is appropriate for the following:

• A relatively small lookup table, where the collection can be constructed in memory each time a subprogram is invoked or a package is initialized
• Passing collections to and from the database server
  PL/SQL automatically converts between host arrays and associative arrays that use numeric key values. The most efficient way to pass collections to and from the database server is to set up data values in associative arrays, and then use those associative arrays with bulk constructs (the FORALLstatement or BULK COLLECT clause).

Choosing Between Nested Tables and Varrays

Varrays are a good choice when:

• The number of elements is known in advance.
• The elements are usually accessed sequentially.

When stored in the database, varrays keep their ordering and subscripts.

A varray is stored as a single object. If a varray is less than 4 KB, it is stored inside the table of which it is a column; otherwise, it is stored outside the table but in the same tablespace.

You must store or retrieve all elements of a varray at the same time, which is appropriate when operating on all the elements at once. However, this might be impractical for large numbers of elements.

Nested tables are a good choice when:

• Index values are not consecutive.
• There is no set number of index values.
• You must delete or update some elements, but not all elements at once.
• You would create a separate lookup table, with multiple entries for each row of the main table, and access it through join queries.

Nested table data is stored in a separate store table, a system-generated database table. When you access a nested table, the database joins the nested table with its store table. This makes nested tables suitable for queries and updates that only affect some elements of the collection.

You cannot rely on the order and subscripts of a nested table remaining stable as the nested table is stored in and retrieved from the database, because the order and subscripts are not preserved in the database.

Associative Array(Index by Table) Vs Nested Table Vs VARRAY

A collection is a list of elements of the same type. Each element in the list has a unique position number or label, called the "subscript".

To give a logical analogy, consider a list of colors = {red, blue, green, yellow}. This list has four elements, all names of colors. There is a unique position of each element in the list. If we number the positions sequentially starting from one, we could say:
color[1] = red, color[2] = blue, and so on.

Here, color is the name of the collection, and the numbers within [] are the subscripts.

PL/SQL has three collection types. In this article, we’ll look at a chart for comparing the three, their advantages and limitations, and which one to use for your needs.

To introduce the three collection types:

• Index by tables: Also called associative arrays.
• Nested tables
• Varrays: Also called variable arrays

The chart below lists the properties of the three collection types on a set of parameters such as size, ease of modification, persistence, etc.

	Index By Tables	Nested Tables	Varrays
Size	Unbounded i.e. the number of elements it can hold is not pre-defined	Unbounded i.e. the number of elements it can hold is not pre-defined	Bounded i.e. holds a declared number of elements, though this number can be changed at runtime
Subscript Characteristics	Can be arbitrary numbers or strings. Need not be sequential.	Sequential numbers, starting from one	Sequential numbers, starting from one
Database Storage	Index by tables can be used in PL/SQL programs only, cannot be stored in the database.	Can be stored in the database using equivalent SQL types, and manipulated through SQL.	Can be stored in the database using equivalent SQL types, and manipulated through SQL (but with less ease than nested tables)
Referencing and lookups	Works as key-value pairs. e.g. Salaries of employees can be stored with unique employee numbers used as subscripts sal(102) := 2000;	Similar to one-column database tables. Oracle stores the  nested table data in no particular order. But when you retrieve the nested table into a PL/SQL variable, the rows are given consecutive subscripts starting at 1.	Standard subscripting syntax e.g.  color(3) is the 3rd color in varray color
Flexibility to changes	Most flexible. Size can increase/ decrease dynamically.   Elements can be added to any position in the list and deleted from any position.	Almost like index-by tables, except that subscript values are not as flexible. Deletions are possible from non-contiguous positions.	Not very flexible. You must retrieve and update all the elements of the varray at the same time.
Mapping with other programming languages	Hash tables	Sets and bags	Arrays

Which Collection Type To Use?

You have all the details about index by tables, nested tables and varrays now. Given a situation, will one should you use for your list data?

Here are some guidelines.

Use index by tables when:

• Your program needs small lookups
• The collection can be made at runtime in the memory when the package/ procedure is initialized
• The data volume is unknown beforehand
• The subscript values are flexible (e.g. strings, negative numbers, non-sequential)
• You do not need to store the collection in the database

Use nested tables when:

• The data needs to be stored in the database
• The number of elements in the collection is not known in advance
• The elements of the collection may need to be retrieved out of sequence
• Updates and deletions affect only some elements, at arbitrary locations
• Your program does not expect to rely on the subscript remaining stable, as their order may change when nested tables are stored in the database.

Use varrays when:

• The data needs to be stored in the database
• The number of elements of the varray is known in advance
• The data from the varray is accessed in sequence
• Updates and deletions happen on the varray as a whole and not on arbitrarily located elements in the varray

Example Declaring Nested Tables, Varrays, and Associative Arrays

DECLARE
   TYPE nested_type IS TABLE OF VARCHAR2(30);
   TYPE varray_type IS VARRAY(5) OF INTEGER;
   TYPE assoc_array_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
   TYPE assoc_array_str_type IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;
   TYPE assoc_array_str_type2 IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);
   v1 nested_type;
   v2 varray_type;
   v3 assoc_array_num_type;
   v4 assoc_array_str_type;
   v5 assoc_array_str_type2;
BEGIN
-- an arbitrary number of strings can be inserted v1
   v1 := nested_type('Shipping','Sales','Finance','Payroll'); 
   v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers
   v3(99) := 10; -- Just start assigning to elements
   v3(7) := 100; -- Subscripts can be any integer values
   v4(42) := 'Smith'; -- Just start assigning to elements
   v4(54) := 'Jones'; -- Subscripts can be any integer values
   v5('Canada') := 'North America'; -- Just start assigning to elements
   v5('Greece') := 'Europe';        -- Subscripts can be string values
END;
/

Simple CASE vs. Searched CASE

The CASE construct in Oracle has two variants – the simple CASE and the searched CASE. We saw examples of both kinds in the topic The Difference Between DECODE and CASE. 

Let’s have a closer look to compare them in structure and functionality.  

Structural Differences

The simple CASE has the following structure:

case n

          when 1 then Action1

          when 2 then Action2

          else        ActionOther

        end case; 

The searched CASE has the following structure:

case

  when n = 1               then Action1;

  when n = 2               then Action2;

   when ( n > 2 and n < 6) then Action3through5;

  else                          ActionOther;

end case;

Functional Differences

The simple CASE performs a simple equality check of "n" against each of the "when" options.

The searched CASE evaluates the conditions independently under each of the "when" options. With this structure, far more complex conditions can be implemented with a searched CASE than a simple CASE.

A searched CASE can combine multiple tests using several columns, comparisons and AND/OR operators.

Note that in both simple and searched CASE constructs, the conditions are evaluated sequentially from top to bottom, and execution exits after the first match is found. So, suppose more than one condition is true, only the first action is considered.

Example using the searched case:

case

  when empno in ('KING', 'CLARK') and sal > 5000

              then process_highpaid(empno);

  when empno in ('CLARK', 'SMITH') 

              then process_manager(empno);

  else    process_general(empno);

end case;

In the above, though employee CLARK may match two conditions, only process_highpaid(empno) will be executed.

Difference Between CASE and DECODE in SQL

DECODE and CASE statements in Oracle both provide a conditional construct, of this form:

if A = n1 then A1

else if A = n2 then A2

else X

Databases before Oracle 8.1.6 had only the DECODE function. CASE was introduced in Oracle 8.1.6 as a standard, more meaningful and more powerful function.

Everything DECODE can do, CASE can. There is a lot else CASE can do though, which DECODE cannot. We’ll go through detailed examples in this article.

1. CASE can work with logical operators other than ‘=’

DECODE performs an equality check only. CASE is capable of other logical comparisons such as < > etc. It takes some complex coding – forcing ranges of data into discrete form – to achieve the same effect with DECODE.

An example of putting employees in grade brackets based on their salaries. This can be done elegantly with CASE.

SQL> select ename

  2       , case

  3           when sal < 1000

  4                then 'Grade I'

  5           when (sal >=1000 and sal < 2000)

  6                then 'Grade II'

  7           when (sal >= 2000 and sal < 3000)

  8                then 'Grade III'

  9           else 'Grade IV'

 10         end sal_grade

 11  from emp

 12  where rownum < 4;

ENAME      SAL_GRADE

---------- ---------

SMITH      Grade I

ALLEN      Grade II

WARD       Grade II

2. CASE can work with predicates and searchable subqueries

DECODE works with expressions that are scalar values only. CASE can work with predicates and subqueries in searchable form.

An example of categorizing employees based on reporting relationship, showing these two uses of CASE.

SQL> select e.ename,

  2         case

  3           -- predicate with "in"

  4           -- mark the category based on ename list

  5           when e.ename in ('KING','SMITH','WARD')

  6                then 'Top Bosses'

  7           -- searchable subquery

  8           -- identify if this emp has a reportee

  9           when exists (select 1 from emp emp1

 10                        where emp1.mgr = e.empno)

 11                then 'Managers'

 12           else

 13               'General Employees'

 14         end emp_category

 15  from emp e

 16  where rownum < 5;

ENAME      EMP_CATEGORY

---------- -----------------

SMITH      Top Bosses

ALLEN      General Employees

WARD       Top Bosses

JONES      Managers

3. CASE can work as a PL/SQL construct

DECODE can work as a function inside SQL only. CASE can be an efficient substitute for IF-THEN-ELSE in PL/SQL.

SQL> declare

  2    grade char(1);

  3  begin

  4    grade := 'b';

  5    case grade

  6      when 'a' then dbms_output.put_line('excellent');

  7      when 'b' then dbms_output.put_line('very good');

  8      when 'c' then dbms_output.put_line('good');

  9      when 'd' then dbms_output.put_line('fair');

 10      when 'f' then dbms_output.put_line('poor');

 11      else dbms_output.put_line('no such grade');

 12    end case;

 13  end;

 14  /

PL/SQL procedure successfully completed.

CASE can even work as a parameter to a procedure call, while DECODE cannot.  

SQL> var a varchar2(5);

SQL> exec :a := 'THREE';

PL/SQL procedure successfully completed.

SQL>

SQL> create or replace procedure proc_test (i number)

  2  as

  3  begin

  4    dbms_output.put_line('output = '||i);

  5  end;

  6  /

Procedure created.

SQL> exec proc_test(decode(:a,'THREE',3,0));

BEGIN proc_test(decode(:a,'THREE',3,0)); END;

                *

ERROR at line 1:

ORA-06550: line 1, column 17:

PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL

statement only

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

SQL> exec proc_test(case :a when 'THREE' then 3 else 0 end);

output = 3

PL/SQL procedure successfully completed.

4. Careful! CASE handles NULL differently

Check out the different results with DECODE vs NULL.

SQL> select decode(null

  2              , null, 'NULL'

  3                    , 'NOT NULL'

  4               ) null_test

  5  from dual;

NULL

----

NULL

SQL> select case null

  2         when null

  3         then 'NULL'

  4         else 'NOT NULL'

  5         end null_test

  6  from dual;

NULL_TES

--------

NOT NULL 

The “searched CASE” works as does DECODE.

SQL>  select case

  2         when null is null

  3         then 'NULL'

  4         else 'NOT NULL'

  5         end null_test

  6* from dual

SQL> /

NULL_TES

--------

NULL

5. CASE expects datatype consistency, DECODE does not

Compare the two examples below- DECODE gives you a result, CASE gives a datatype mismatch error.

SQL> select decode(2,1,1,

  2                 '2','2',

  3                 '3') t

  4  from dual; 

         T

----------

         2 

SQL> select case 2 when 1 then '1'

  2              when '2' then '2'

  3              else '3'

  4         end

  5  from dual;

  

            when '2' then '2'

                 *

ERROR at line 2:

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

6. CASE is ANSI SQL-compliant

CASE complies with ANSI SQL. DECODE is proprietary to Oracle.

7. The difference in readability

In very simple situations, DECODE is shorter and easier to understand than CASE.

SQL> -- An example where DECODE and CASE

SQL> -- can work equally well, and

SQL> -- DECODE is cleaner

SQL> select ename

  2       , decode (deptno, 10, 'Accounting',

  3                         20, 'Research',

  4                         30, 'Sales',

  5                             'Unknown') as department

  6  from   emp

  7  where rownum < 4;

ENAME      DEPARTMENT

---------- ----------

SMITH      Research

ALLEN      Sales

WARD       Sales

SQL> select ename

  2       , case deptno

  3           when 10 then 'Accounting'

  4           when 20 then 'Research'

  5           when 30 then 'Sales'

  6           else         'Unknown'

  7           end as department

  8  from emp

  9  where rownum < 4;

ENAME      DEPARTMENT

---------- ----------

SMITH      Research

ALLEN      Sales

WARD       Sales

Complicated logical comparisons in DECODE, even if technically achievable, are a recipe for messy, bug-prone code. When the same can be done more cleanly with CASE, go for CASE.