代码之家 › 专栏 › 技术社区 › filippo

oracle解码的查找表?

decode oracle sql

filippo · 技术社区 · 16 年前

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

或者用CASE的。

现在假设我有一个具有相同值的表:

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

请注意,我不想连接表以访问另一个表中的数据。。。我只是想知道有没有什么东西我可以用来 decode(myField, usingThisLookupTable, thisValueForDefault) .

2 回复 | 直到 11 年前

Erich Kitzmueller 9 年前

您可以使用子查询而不是连接,即。

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

Rob van Wijk 16 年前

不,除了使用连接到第二个表之外,没有其他方法。当然,可以在select子句中编写标量子查询,也可以编写自己的函数,但这样做效率很低。

编辑:

在选择列表中使用标量子查询时,您可能希望强制执行一个嵌套的类似于循环的计划,其中标量子查询针对states\u表的每一行执行。至少我预料到了:-)。

然而,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个!

在您的情况下,这种优化是否总是一件好事,取决于许多因素。你可以参考前面提到的文章来看看一些效果。

当做,