how to make SQL having many union faster for high speed

Question

I have a query which takes quite a long time for selecting data. Let me share my data schema and do you have any idea for high speed instead of my SQL?

---------------------------------------------------
time                | statA | statB | statC | ... |
---------------------------------------------------
2018-01-01 00:00:00 |   1   |   2   |   1   | ... | 
---------------------------------------------------
2018-01-01 00:00:01 |   2   |   3   |   5   | ... |
---------------------------------------------------
2018-01-01 00:00:02 |   1   |   4   |   3   | ... |
---------------------------------------------------
...

What I'd like to get using the query looks like:

---------------------------------------
time                |  stat  |  value  |
---------------------------------------
2018-01-01 00:00:00 | statA  |    1    |
---------------------------------------
2018-01-01 00:00:01 | statA  |    2    |
---------------------------------------
2018-01-01 00:00:02 | statA  |    1    |
---------------------------------------
...
...
---------------------------------------
time                |  stat  |  value  |
---------------------------------------
2018-01-01 00:00:00 | statB  |    2    |
---------------------------------------
2018-01-01 00:00:01 | statB  |    3    |
---------------------------------------
2018-01-01 00:00:02 | statB  |    4    |
---------------------------------------
...
...

So, my query is now

(select time, statA as stat, statA as value
from table)
union (
select time, statB as stat, stastB as value
from table)
union (
select time, statC as stat, stastC as value
from table)

Actually, I'm feeling like I'm so stupid because I have to write same SQLs which are only different with stat id repeatedly.

Any recommendation and ways to develop my SQL?

@jarlh Instead of using UNION and UNION ALL, is there any better way? Anyways, let me do it. — sunsets, Dec 03 '18 at 08:51
@juergen d It can't cuz actually I have way bigger table which looks like the second table. — sunsets, Dec 03 '18 at 08:51
You already labeled your question with *UNPIVOT*, so why don't you use it? https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1#unpivot — ibre5041, Dec 03 '18 at 09:13

score 3 · Answer 1 · answered Dec 03 '18 at 09:29

You can use UNPIVOT as it was suggested by @ibre5041

CREATE TABLE table_ (
  time_ DATE, 
  statA NUMBER, 
  statB NUMBER, 
  statC NUMBER
);

INSERT INTO table_ VALUES (TRUNC(SYSDATE)+ 1/24/60/60*0, 1, 2, 1);
INSERT INTO table_ VALUES (TRUNC(SYSDATE)+ 1/24/60/60*1, 2, 3, 5);
INSERT INTO table_ VALUES (TRUNC(SYSDATE)+ 1/24/60/60*2, 1, 4, 3);

COMMIT;


SELECT TO_CHAR(time_, 'YYYY-MM-DD hh24:mi:ss'), stat, value_
FROM   table_
UNPIVOT (value_ FOR stat IN (statA AS 'statA', statB AS 'statB', statC AS 'statC'))
ORDER BY time_;

It works completely and one more tip for ppl: where should be put after unpivot. Thanks. — sunsets, Dec 05 '18 at 01:23

score 1 · Answer 2 · answered Dec 03 '18 at 09:20

1

Performance issues with UNION can sometimes be solved using UNION ALL.

The difference being that UNION filters out the duplicates whereas UNION ALL doesn't (the performance gain comes from not having to create a worktable to filter out the duplicates).

answered Dec 03 '18 at 09:20

Rob

21
4

I still need union so I've replace `union` with `union all` – sunsets Dec 05 '18 at 01:23

score 0 · Answer 3 · answered Dec 03 '18 at 12:55

unpivot or a lateral join is the best method. However your method would be much faster with union all instead of union:

select time, statA as stat, statA as value
from table
union all
select time, statB as stat, stastB as value
from table
union all
select time, statC as stat, stastC as value
from table;

union incurs overhead for removing duplicates. This version still requires scanning the table three times, but duplicate removal is likely to be much more expensive.

I'm going to try later join too. I tried other ways already ! Thanks — sunsets, Dec 05 '18 at 01:24

how to make SQL having many union faster for high speed

3 Answers3