【Oracle SQL】把某表列值借助码值表由代号转换成名称的SQL，子查询效率未必低

有一张emp722表结构和数据如下：

create table emp722(
    id number(10),
    f1 nvarchar2(20),
    f2 nvarchar2(20),
    f3 nvarchar2(20),
    f4 nvarchar2(20),
    primary key(id)
);

insert into emp722(id,f1,f2,f3,f4) values(1,'1','2','3','4');
insert into emp722(id,f1,f2,f3,f4) values(2,'4','3','2','1');

还有一张辅助的表dic722结构及数据如下：

create table dic722(
    code nvarchar2(20),
    name nvarchar2(20),
    primary key(code)
);

insert into dic722(code,name) values('1','A');
insert into dic722(code,name) values('2','B');
insert into dic722(code,name) values('3','C');
insert into dic722(code,name) values('4','D');

 

【需求】

将emp722中1，2，3，4通过dic722表置换成A，B，C，D的形式。

 

【子查询解决方案】

SQL：

select
    e.id,
    (select name from dic722 where code=e.f1) as f1,
    (select name from dic722 where code=e.f2) as f2,
    (select name from dic722 where code=e.f3) as f3,
    (select name from dic722 where code=e.f4) as f4
from emp722 e

运行：

luna@ORCL>select
  2      e.id,
  3  (select name from dic722 where code=e.f1) as f1,
  4  (select name from dic722 where code=e.f2) as f2,
  5  (select name from dic722 where code=e.f3) as f3,
  6  (select name from dic722 where code=e.f4) as f4
  7  from emp722 e;

        ID F1         F2         F3         F4
---------- ---------- ---------- ---------- ----------
         1 A          B          C          D
         2 D          C          B          A

已选择2行。

解释计划：

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2973185055

--------------------------------------------------------------------------------
------------

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)
| Time     |

--------------------------------------------------------------------------------
------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     2 |   202 |     3   (0)
| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| DIC722       |     1 |    44 |     1   (0)
| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | SYS_C0011561 |     1 |       |     1   (0)
| 00:00:01 |

|   3 |  TABLE ACCESS BY INDEX ROWID| DIC722       |     1 |    44 |     1   (0)
| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|*  4 |   INDEX UNIQUE SCAN         | SYS_C0011561 |     1 |       |     1   (0)
| 00:00:01 |

|   5 |  TABLE ACCESS BY INDEX ROWID| DIC722       |     1 |    44 |     1   (0)
| 00:00:01 |

|*  6 |   INDEX UNIQUE SCAN         | SYS_C0011561 |     1 |       |     1   (0)
| 00:00:01 |

|   7 |  TABLE ACCESS BY INDEX ROWID| DIC722       |     1 |    44 |     1   (0)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 00:00:01 |

|*  8 |   INDEX UNIQUE SCAN         | SYS_C0011561 |     1 |       |     1   (0)
| 00:00:01 |

|   9 |  TABLE ACCESS FULL          | EMP722       |     2 |   202 |     3   (0)
| 00:00:01 |

--------------------------------------------------------------------------------
------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

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

   2 - access("CODE"=:B1)
   4 - access("CODE"=:B1)
   6 - access("CODE"=:B1)
   8 - access("CODE"=:B1)

Note
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - dynamic sampling used for this statement (level=2)

已选择28行。

可以看到其cost是3.

 

【表连接查询解决方案】

SQL：

select
   a.id,
   listagg(a.f1,',') within group (order by id) as f1,
   listagg(a.f2,',') within group (order by id) as f2,
   listagg(a.f3,',') within group (order by id) as f3,
   listagg(a.f4,',') within group (order by id) as f4
from
(
    select
        e.id,
        decode(e.f1,d.code,d.name,'') as f1,
        decode(e.f2,d.code,d.name,'') as f2,
        decode(e.f3,d.code,d.name,'') as f3,
        decode(e.f4,d.code,d.name,'') as f4
    from emp722 e,dic722 d
)  a
group by a.id

执行：

luna@ORCL>select
  2     a.id,
  3     listagg(a.f1,',') within group (order by id) as f1,
  4     listagg(a.f2,',') within group (order by id) as f2,
  5     listagg(a.f3,',') within group (order by id) as f3,
  6     listagg(a.f4,',') within group (order by id) as f4
  7  from
  8  (
  9  select
 10      e.id,
 11  decode(e.f1,d.code,d.name,'') as f1,
 12  decode(e.f2,d.code,d.name,'') as f2,
 13  decode(e.f3,d.code,d.name,'') as f3,
 14  decode(e.f4,d.code,d.name,'') as f4
 15  from emp722 e,dic722 d
 16  )  a
 17  group by a.id;

        ID F1         F2         F3         F4
---------- ---------- ---------- ---------- ----------
         1  A          B          C          D
         2  D          C          B          A

已选择2行。

解释计划：

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     8 |  1160 |     8  (13)| 00:00:01 |
|   1 |  SORT GROUP BY        |        |     8 |  1160 |     8  (13)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|        |     8 |  1160 |     7   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | EMP722 |     2 |   202 |     3   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |        |     4 |   176 |     5  (20)| 00:00:01 |
|   5 |     TABLE ACCESS FULL | DIC722 |     4 |   176 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

已选择16行。

Cost为8，不如上面的3，说明这种方案效率还不如子查询方案。

 

END