Lookup table for oracle decodes?

Question

It might be a newbie question, but still..

We are all familiar with Oracle's decodes and cases, e.g.

select
  decode (state,
          0, 'initial',
          1, 'current',
          2, 'finnal',
          state)
from states_table

Or the same sort of thing using CASE's.

Now let's say I have a table with these same values:

state_num | state_desc
        0 | 'initial'
        1 | 'current'
        2 | 'finnal'

is there a way I could do that same query using this table as a resource for the decode? Please note that I do not want to joint the table to access the data from the other table... i just want to know if there's something I could use to do a sort of decode(myField, usingThisLookupTable, thisValueForDefault).

Answer 1

Instead of a join, you could use a subquery, i.e.

select nvl(
   (select state_desc 
   from lookup 
   where state_num=state),to_char(state)) 
from states_table;

Answer 2

No, there is not any another way, besides using a join to your second table. Sure, you could write a scalar subquery in your select clause, or you could write your own function, but that would be inefficient practise.

If you need the data from the table, you need to select from it.

EDIT: I have to refine my earlier statement about the inefficient practise.

When using a scalar subquery in your select list, you'd expect that you are forcing a nested-loop look-a-like plan, where the scalar subquery gets executed for each row of the states_table. At least I expected that :-).

However, Oracle has implemented scalar subquery caching, which leads to a really nice optimization. It only executes the subquery 3 times. There is an excellent article about scalar subqueries where you can see that more factors play a role in how this optimization behaves: http://www.oratechinfo.co.uk/scalar_subqueries.html#scalar3

Here is my own test to see this at work. For a simulation of your tables, I used this script:

create table states_table (id,state,filler)
as
 select level
      , floor(dbms_random.value(0,3))
      , lpad('*',1000,'*')
   from dual
connect by level <= 100000
/
alter table states_table add primary key (id)
/
create table lookup_table (state_num,state_desc)
as
select 0, 'initial' from dual union all
select 1, 'current' from dual union all
select 2, 'final' from dual
/
alter table lookup_table add primary key (state_num)
/
alter table states_table add foreign key (state) references lookup_table(state_num)
/
exec dbms_stats.gather_table_stats(user,'states_table',cascade=>true)
exec dbms_stats.gather_table_stats(user,'lookup_table',cascade=>true)

Then execute the query and have a look at the real execution plan:

SQL> select /*+ gather_plan_statistics */
  2         s.id
  3       , s.state
  4       , l.state_desc
  5    from states_table s
  6         join lookup_table l on s.state = l.state_num
  7  /

        ID      STATE STATE_D
---------- ---------- -------
         1          2 final
...
    100000          0 initial

100000 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
  2  /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  f6p6ku8g8k95w, child number 0
-------------------------------------
select /*+ gather_plan_statistics */        s.id      , s.state      , l.state_desc   from states_table s        join
lookup_table l on s.state = l.state_num

Plan hash value: 1348290364

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |              |      1 |  99614 |    100K|00:00:00.50 |   20015 |   7478 |  1179K|  1179K|  578K (0)|
|   2 |   TABLE ACCESS FULL| LOOKUP_TABLE |      1 |      3 |      3 |00:00:00.01 |       3 |      0 |       |       |          |
|   3 |   TABLE ACCESS FULL| STATES_TABLE |      1 |  99614 |    100K|00:00:00.30 |   20012 |   7478 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("S"."STATE"="L"."STATE_NUM")


20 rows selected.

Now do the same for the scalar subquery variant:

SQL> select /*+ gather_plan_statistics */
  2         s.id
  3       , s.state
  4       , ( select l.state_desc
  5             from lookup_table l
  6            where l.state_num = s.state
  7         )
  8    from states_table s
  9  /

        ID      STATE (SELECT
---------- ---------- -------
         1          2 final
...
    100000          0 initial

100000 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
  2  /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  22y3dxukrqysh, child number 0
-------------------------------------
select /*+ gather_plan_statistics */        s.id      , s.state      , ( select l.state_desc
 from lookup_table l           where l.state_num = s.state        )   from states_table s

Plan hash value: 2600781440

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| LOOKUP_TABLE |      3 |      1 |      3 |00:00:00.01 |       5 |      0 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C0040786 |      3 |      1 |      3 |00:00:00.01 |       2 |      0 |
|   3 |  TABLE ACCESS FULL          | STATES_TABLE |      1 |  99614 |    100K|00:00:00.30 |   20012 |   9367 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("L"."STATE_NUM"=:B1)


20 rows selected.

And look at the Starts column of step 1 and 2: only 3!

Whether this optimization is always a good thing in your situation, depends on many factors. You can refer to the earlier mentioned article to see the effect of some.

In your situation with only three states, it looks like you can't go wrong with the scalar subquery variant.

Regards, Rob.