oracle oracle解码的查找表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3100877/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Lookup table for oracle decodes?
提问by filippo
It might be a newbie question, but still..
这可能是一个新手问题，但仍然..
We are all familiar with Oracle's decodes and cases, e.g.
我们都熟悉Oracle的解码和案例，例如
select
decode (state,
0, 'initial',
1, 'current',
2, 'finnal',
state)
from states_table
Or the same sort of thing using CASE's.
或者使用 CASE 的相同类型的东西。
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).
有没有办法可以使用此表作为解码资源来执行相同的查询？请注意，我不想联合该表来访问另一个表中的数据......我只是想知道是否有什么我可以用来做某种decode(myField, usingThisLookupTable, thisValueForDefault).
采纳答案by Erich Kitzmueller
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;
回答by Rob van Wijk
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.
不，除了使用连接到您的第二个表之外，没有其他任何方法。当然，您可以在 select 子句中编写一个标量子查询，或者您可以编写自己的函数，但这将是低效的做法。
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 :-).
在选择列表中使用标量子查询时，您希望强制执行一个嵌套循环的类似计划，其中为 states_table 的每一行执行标量子查询。至少我预料到了:-)。
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
然而，Oracle 已经实现了标量子查询缓存，这导致了非常好的优化。它只执行 3 次子查询。有一篇关于标量子查询的优秀文章，您可以看到更多因素在此优化的行为中起作用：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!
并查看第 1 步和第 2 步的“开始”列：只有 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.
问候，罗伯。
