Таблица поиска для декодирования оракула?

Вместо соединения вы можете использовать подзапрос, т.е.

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

Нет, другого пути, кроме как присоединения ко второй таблице, нет. Конечно, вы можете написать скалярный подзапрос в предложении select или написать свою собственную функцию, но это будет неэффективная практика.

Если вам нужны данные из таблицы, вам нужно выбрать из нее.

EDIT: я должен уточнить свое предыдущее заявление о неэффективной практике.

При использовании скалярного подзапроса в вашем списке выбора вы ожидаете, что вы форсируете план, похожий на вложенный цикл, где скалярный подзапрос выполняется для каждой строки state_table. По крайней мере, я этого ожидал :-).

Однако в Oracle реализовано скалярное кэширование подзапросов, что приводит к действительно хорошей оптимизации. Он выполняет подзапрос только 3 раза. Есть отличная статья о скалярных подзапросах, где видно, что на поведение этой оптимизации влияет больше факторов: http://www.oratechinfo.co.uk/scalar_subqueries.html#scalar3

Вот мой собственный тест, чтобы увидеть это на работе. Для моделирования ваших таблиц я использовал этот скрипт:

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)

Затем выполните запрос и посмотрите на реальный план выполнения:

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.

Теперь сделайте то же самое для варианта скалярного подзапроса:

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.

И посмотрите на столбец Starts шагов 1 и 2: всего 3!

Будет ли эта оптимизация всегда полезной в вашей ситуации, зависит от многих факторов. Вы можете обратиться к ранее упомянутой статье, чтобы увидеть эффект некоторых из них.

В вашей ситуации только с тремя состояниями, похоже, вы не ошибетесь со скалярным вариантом подзапроса.

С уважением, Роб.