选择行两次，修改一列

Question

我有table和sql (在Oracle上运行)：

T(这只是一个例子，表格很大)

a  b  c
-------
1  4  7
2  5  5
3  6  8

sql：

SELECT a, b, c
FROM t

union all

SELECT 'R',b,c
FROM t
WHERE b = c AND (condition to another tables, etc)

它返回：

1  4  7
2  5  5
3  6  8
R  5  5

是否可以在这里避免UNION (并且不添加JOIN)？换句话说，有没有可能优化查询以避免Oracle两次查看表T？

Answer 1

这将读取您的表一次。连接是通过一个只包含两个值的辅助表完成的(当然，这是在内存中-没有I/O)

with t as(
  select '1' a,  '4' b,  '7' c from dual union all
  select '2',  '5',  '5' from dual union all
  select '3',  '6',  '8' from dual
)
select decode(aux.col,1,t.a,'R'), t.b, t.c 
from t
join (select '1' col from dual union all select '2' from dual) aux
on (aux.col='1' or t.b=t.c);

查询不依赖于“%1”和“%2”。它可以是：

select decode(aux.col, 'bla', t.a,'R'), t.b, t.c 
from t
join (select 'bla' col from dual union all select 'otherbla' from dual) aux
on (aux.col='bla' or t.b=t.c);

UPDATE：另外，如果b=c记录的数量很少，您可以加快实际查询创建索引的速度：

 create index fbi on t (b-c);

，然后用WHERE b - c = 0替换查询WHERE b = c中的

UPDATE2只是为了了解这些查询是如何执行的：

create table t(a varchar2(10), b varchar2(10), c varchar2(10));

insert into t 
select mod(dbms_random.random(),1000),
  mod(dbms_random.random(),1000),
  mod(dbms_random.random(),1000)
from dual
connect by level < 1000000;

exec DBMS_STATS.GATHER_TABLE_STATS('DEV','T');

--1
SELECT a, b, c
FROM t;
---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   999K|    11M|   700   (3)|
|   1 |  TABLE ACCESS FULL| T    |   999K|    11M|   700   (3)|
---------------------------------------------------------------

--2
SELECT a, b, c
FROM t
union all
SELECT 'R',b,c
FROM t
WHERE b = c;
----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1009K|    11M|  1426  (53)|
|   1 |  UNION-ALL         |      |       |       |            |
|   2 |   TABLE ACCESS FULL| T    |   999K|    11M|   700   (3)|
|   3 |   TABLE ACCESS FULL| T    | 10000 |    97K|   726   (7)|
---------------------------------------------------------------- 


--3
select decode(aux.col, 'bla', t.a,'R'), t.b, t.c 
from t
join (select 'bla' col from dual union all select 'otherbla' from dual) aux
on (aux.col='bla' or t.b=t.c);


----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 20990 |   368K|  1402   (3)|
|   1 |  NESTED LOOPS      |      | 20990 |   368K|  1402   (3)|
|   2 |   VIEW             |      |     2 |    12 |     4   (0)|
|   3 |    UNION-ALL       |      |       |       |            |
|   4 |     FAST DUAL      |      |     1 |       |     2   (0)|
|   5 |     FAST DUAL      |      |     1 |       |     2   (0)|
|   6 |   TABLE ACCESS FULL| T    | 10495 |   122K|   699   (3)|
----------------------------------------------------------------
--if the leading table is dual, can be used an /*+ordered*/ hint 
--after select clause


--4
create index fbi on t (b-c);
SELECT a, b, c
FROM t
union all
SELECT 'R',b,c
FROM t
WHERE b - c = 0;
--------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |  1009K|    11M|  1384  (51)|
|   1 |  UNION-ALL                   |      |       |       |            |
|   2 |   TABLE ACCESS FULL          | T    |   999K|    11M|   700   (3)|
|   3 |   TABLE ACCESS BY INDEX ROWID| T    | 10000 |   117K|   683   (1)|
|   4 |    INDEX RANGE SCAN          | FBI  |  4000 |       |     3   (0)|
--------------------------------------------------------------------------

请记住，Oracle不知道如何很好地预测on或join子句，因此最好强制执行所需的执行路径。您应该测试在2和3和4之间进行选择(带有索引成本的背面)。