Re: How to union table without union statement? - Mailing list pgsql-sql
| From | calendarw |
|---|---|
| Subject | Re: How to union table without union statement? |
| Date | |
| Msg-id | 9d91f7aa0702281752o31382827kb33b20848a507938@mail.gmail.com Whole thread Raw |
| In response to | How to union table without union statement? (calendarw <calendarw@gmail.com>) |
| Responses |
Re: How to union table without union statement?
(Shane Ambler <pgsql@Sheeky.Biz>)
|
| List | pgsql-sql |
Hi,
I am using the following query now, but the time is too slow. could anyone can help me?
CREATE OR REPLACE VIEW alllogview AS
((((((( SELECT alarmdtl.tagname, a_alarmtbl.occurtime, a_alarmtbl.restoretime, a_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc
FROM a_alarmtbl, alarmdtl
WHERE a_alarmtbl.tagname::text = alarmdtl.tagname::text
UNION ALL
SELECT alarmdtl.tagname, b_alarmtbl.occurtime, b_alarmtbl.restoretime, b_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc
FROM b_alarmtbl, alarmdtl
WHERE b_alarmtbl.tagname::text = alarmdtl.tagname::text)
UNION ALL
SELECT alarmdtl.tagname, c_alarmtbl.occurtime, c_alarmtbl.restoretime, c_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc
FROM c_alarmtbl, alarmdtl
WHERE c_alarmtbl.tagname::text = alarmdtl.tagname::text)
UNION ALL
SELECT alarmdtl.tagname, d_alarmtbl.occurtime, d_alarmtbl.restoretime, d_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc
FROM d_alarmtbl, alarmdtl
WHERE d_alarmtbl.tagname::text = alarmdtl.tagname::text)
UNION ALL
SELECT alarmdtl.tagname, e_alarmtbl.occurtime, e_alarmtbl.restoretime, e_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc
FROM e_alarmtbl, alarmdtl
WHERE e_alarmtbl.tagname::text = alarmdtl.tagname::text)
UNION ALL
SELECT alarmdtl.tagname, f_alarmtbl.occurtime, f_alarmtbl.restoretime, f_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc
FROM f_alarmtbl, alarmdtl
WHERE f_alarmtbl.tagname::text = alarmdtl.tagname::text)
UNION ALL
SELECT alarmdtl.tagname, g_alarmtbl.occurtime, g_alarmtbl.restoretime, g_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc
FROM g_alarmtbl, alarmdtl
WHERE g_alarmtbl.tagname::text = alarmdtl.tagname::text)
UNION ALL
SELECT alarmdtl.tagname, h_alarmtbl.occurtime, h_alarmtbl.restoretime, h_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc
FROM h_alarmtbl, alarmdtl
WHERE h_alarmtbl.tagname::text = alarmdtl.tagname::text)
UNION ALL
SELECT alarmdtl.tagname, i_alarmtbl.occurtime, i_alarmtbl.restoretime, i_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc
FROM i_alarmtbl, alarmdtl
WHERE i_alarmtbl.tagname::text = alarmdtl.tagname::text
ORDER BY 1;
--
Jr. P
calendarw
I am using the following query now, but the time is too slow. could anyone can help me?
CREATE OR REPLACE VIEW alllogview AS
((((((( SELECT alarmdtl.tagname, a_alarmtbl.occurtime, a_alarmtbl.restoretime, a_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc
FROM a_alarmtbl, alarmdtl
WHERE a_alarmtbl.tagname::text = alarmdtl.tagname::text
UNION ALL
SELECT alarmdtl.tagname, b_alarmtbl.occurtime, b_alarmtbl.restoretime, b_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc
FROM b_alarmtbl, alarmdtl
WHERE b_alarmtbl.tagname::text = alarmdtl.tagname::text)
UNION ALL
SELECT alarmdtl.tagname, c_alarmtbl.occurtime, c_alarmtbl.restoretime, c_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc
FROM c_alarmtbl, alarmdtl
WHERE c_alarmtbl.tagname::text = alarmdtl.tagname::text)
UNION ALL
SELECT alarmdtl.tagname, d_alarmtbl.occurtime, d_alarmtbl.restoretime, d_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc
FROM d_alarmtbl, alarmdtl
WHERE d_alarmtbl.tagname::text = alarmdtl.tagname::text)
UNION ALL
SELECT alarmdtl.tagname, e_alarmtbl.occurtime, e_alarmtbl.restoretime, e_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc
FROM e_alarmtbl, alarmdtl
WHERE e_alarmtbl.tagname::text = alarmdtl.tagname::text)
UNION ALL
SELECT alarmdtl.tagname, f_alarmtbl.occurtime, f_alarmtbl.restoretime, f_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc
FROM f_alarmtbl, alarmdtl
WHERE f_alarmtbl.tagname::text = alarmdtl.tagname::text)
UNION ALL
SELECT alarmdtl.tagname, g_alarmtbl.occurtime, g_alarmtbl.restoretime, g_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc
FROM g_alarmtbl, alarmdtl
WHERE g_alarmtbl.tagname::text = alarmdtl.tagname::text)
UNION ALL
SELECT alarmdtl.tagname, h_alarmtbl.occurtime, h_alarmtbl.restoretime, h_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc
FROM h_alarmtbl, alarmdtl
WHERE h_alarmtbl.tagname::text = alarmdtl.tagname::text)
UNION ALL
SELECT alarmdtl.tagname, i_alarmtbl.occurtime, i_alarmtbl.restoretime, i_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc
FROM i_alarmtbl, alarmdtl
WHERE i_alarmtbl.tagname::text = alarmdtl.tagname::text
ORDER BY 1;
On 2/28/07, Hiltibidal, Robert <Robert.Hiltibidal@argushealth.com> wrote:
Can you provide a schema?
From: pgsql-sql-owner@postgresql.org [mailto: pgsql-sql-owner@postgresql.org] On Behalf Of calendarw
Sent: Wednesday, February 28, 2007 4:33 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] How to union table without union statement?
Hi,
I need to combine 10 tables which contain same table structure and join an "other table" to show the latest 200 record, I am join the "other table" first and using union statement to select all record now but the collection time is super slow, how can I improve the collection speed?
Thanks.
--
Jr. P
calendarwPRIVILEGED AND CONFIDENTIAL
This email transmission contains privileged and confidential information intended only for the use of the individual or entity named above. If the reader of the email is not the intended recipient or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any use, dissemination or copying of this email transmission is strictly prohibited by the sender. If you have received this transmission in error, please delete the email and immediately notify the sender via the email return address or mailto: postmaster@argushealth.com. Thank you.
--
Jr. P
calendarw