Справочная таблица для оракула декодирует?
Это мог бы быть вопрос о новичке, но все еще..
Мы все знакомы с декодированиями и случаями Oracle, например.
select
decode (state,
0, 'initial',
1, 'current',
2, 'finnal',
state)
from states_table
Или тот же вид вещи с помощью СЛУЧАЯ.
Теперь скажем, у меня есть таблица с этими теми же значениями:
state_num | state_desc
0 | 'initial'
1 | 'current'
2 | 'finnal'
существует ли способ, которым я мог сделать тот же самый запрос с помощью этой таблицы в качестве ресурса для декодирования? Обратите внимание на то, что я не хочу соединять таблицу для доступа к данным из другой таблицы... я просто хочу знать, существует ли что-то, что я мог бы использовать, чтобы сделать своего рода decode(myField, usingThisLookupTable, thisValueForDefault).
2 ответа
Вместо объединения можно использовать подзапрос, т.е.
select nvl(
(select state_desc
from lookup
where state_num=state),to_char(state))
from states_table;
Нет, нет другого способа, кроме использования соединения со второй таблицей. Конечно, вы могли бы написать скалярный подзапрос в своем предложении select или написать свою собственную функцию, но это было бы неэффективной практикой.
Если вам нужны данные из таблицы, вам нужно выбрать из нее.
РЕДАКТИРОВАТЬ: Я должен уточнить свое предыдущее утверждение о неэффективной практике.
При использовании скалярного подзапроса в списке выбора можно ожидать, что вы принудительно используете план, похожий на вложенный цикл, в котором скалярный подзапрос выполняется для каждой строки таблицы состояний. По крайней мере, я этого ожидал :-).
Однако в 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.
И посмотрите на начало столбец шага 1 и 2: только 3!
Всегда ли такая оптимизация хороша в вашей ситуации, зависит от многих факторов. Вы можете обратиться к ранее упомянутой статье, чтобы увидеть эффект от некоторых из них.
В вашей ситуации только с тремя состояниями, похоже, вы не ошибетесь, выбрав вариант скалярного подзапроса.
С уважением, Роб.