sql - Select row twice with one column modification -

i have table , sql (running on oracle):

t (it's example, table huge)

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

sql:

select a, b, c t  union  select 'r',b,c t b = c , (condition tables, etc) 

it returns:

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

is possible avoid union here (and don't add join)? in other words - possible optimize query avoid oracle table t twice?

this read table once. join done auxiliary table contains 2 values(of course in in memory - no i/o)

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

the query not depend on '1' , '2'. can be:

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

update: also, if number of b=c records small, can speed actual query creating index:

 create index fbi on t (b-c); 

and replace in query where b = c where b - c = 0

update2 ideea on how these queries executed:

create table t(a varchar2(10), b varchar2(10), c varchar2(10));  insert t  select mod(dbms_random.random(),1000),   mod(dbms_random.random(),1000),   mod(dbms_random.random(),1000) dual connect level < 1000000;  exec dbms_stats.gather_table_stats('dev','t');  --1 select a, b, c 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 t union select 'r',b,c t 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  t join (select 'bla' col dual union select 'otherbla' 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 leading table dual, can used /*+ordered*/ hint  --after select clause   --4 create index fbi on t (b-c); select a, b, c t union select 'r',b,c t 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 index rowid| t    | 10000 |   117k|   683   (1)| |   4 |    index range scan          | fbi  |  4000 |       |     3   (0)| -------------------------------------------------------------------------- 

keep in mind oracle not know predict on or join clauses in 3, better force desired execution path. should test choose between 2 , 3 , 4(with of cost of index).