一个标量子查询联想到with as改写方案

群里有朋友提到下列sql语句如何避免反复扫描t_dict表 select o.order_id, (SELECT DICT_NAME FROM T_DICT WHERE DICT_TYPEID = ‘ORDERSTAT’ AND DICT_ID = o.STAT) AS STAT, (SELECT DICT_NAME FROM T_DICT WHERE DICT_TYPEID = ‘PAYSTAT’ AND DICT_ID = o.P

群里有朋友提到下列sql语句如何避免反复扫描t_dict表
select o.order_id,
(SELECT DICT_NAME
FROM T_DICT
WHERE DICT_TYPEID = 'ORDERSTAT'
AND DICT_ID = o.STAT) AS STAT,
(SELECT DICT_NAME
FROM T_DICT
WHERE DICT_TYPEID = 'PAYSTAT'
AND DICT_ID = o.PAY_STAT) AS PAY_STAT,
(SELECT DICT_NAME
FROM T_DICT
WHERE DICT_TYPEID = 'ACTSTAT'
AND DICT_ID = o.ACT_STAT) AS ACT_STAT
FROM T_ORDER o

另一个朋友提到用下列sql语句来避免反复扫描子表t_dict
/* Formatted on 2014/6/12 17:57:18 (QP5 v5.149.1003.31008) */
WITH vts1
AS (SELECT o.order_id, d.DICT_NAME, d.DICT_TYPEID
FROM T_DICT d, T_ORDER o
WHERE ( d.DICT_ID = o.STAT
OR d.DICT_ID = o.PAY_STAT
OR d.DICT_ID = o.ACT_STAT)
AND d.DICT_TYPEID IN ('ORDERSTAT', 'PAYSTAT', 'ACTSTAT'))
SELECT order_id,
(SELECT DICT_NAME
FROM vts1
WHERE DICT_TYPEID = 'ORDERSTAT')
AS stat,
(SELECT DICT_NAME
FROM vts1
WHERE DICT_TYPEID = 'PAYSTAT')
AS stat,
(SELECT DICT_NAME
FROM vts1
WHERE DICT_TYPEID = 'ACTSTAT')
AS stat
FROM vts1;

上面的sql是一个标量子查询，而小鱼手动构造了下列表t01和t02
SQL> create table t01 as select * from dba_objects;

Table created.
SQL> create table t02 as select * from dba_objects;

Table created.
SQL> alter table t01 add id number;

Table altered.
SQL> update t01 set id=object_id;

50328 rows updated.

SQL> commit;

SQL> SELECT t01.object_id,
2 (SELECT object_name
3 FROM t02
4 WHERE t02.object_type = 'TABLE' AND t02.object_id = t01.object_id)
5 col1,
6 (SELECT object_name
7 FROM t02
8 本文来源gaodai#ma#com搞*!代#%^码网5 WHERE t02.object_type = 'INDEX'
9 AND t02.object_id = t01.data_object_id)
10 col2,
11 (SELECT object_name
12 FROM t02
13 WHERE t02.object_type = 'VIEW' AND t02.object_id = t01.id)
14 col3
15 FROM t01;

50328 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3013675264

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 46210 | 1759K| 155 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T02 | 20 | 1800 | 17 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T02 | 18 | 1620 | 18 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T02 | 33 | 2970 | 7 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T01 | 46210 | 1759K| 155 (2)| 00:00:02 |
--------------------------------------------------------------------------

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

1 - filter("T02"."OBJECT_TYPE"='TABLE' AND "T02"."OBJECT_ID"=:B1)
2 - filter("T02"."OBJECT_TYPE"='INDEX' AND "T02"."OBJECT_ID"=:B1)
3 - filter("T02"."OBJECT_TYPE"='VIEW' AND "T02"."OBJECT_ID"=:B1)

喜欢 (0)赏

[搞代码]

分享 (0)