From: calendarw <calendarw@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: How to union table without union statement?
Date: Thu, 1 Mar 2007 09:52:40 +0800
Message-ID: <9d91f7aa0702281752o31382827kb33b20848a507938@mail.gmail.com> (raw)
In-Reply-To: <A6C93EF9812C8346A8E8BD6AE573E52D07F52BB7@ARG-EXVS01.corp.argushealth.com>
References: <9d91f7aa0702280233r7c91dedav1ddfaffa92877280@mail.gmail.com>
<A6C93EF9812C8346A8E8BD6AE573E52D07F52BB7@ARG-EXVS01.corp.argushealth.com>
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;
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
> calendarw
>
> PRIVILEGED 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: calendarw@gmail.com
Subject: Re: How to union table without union statement?
In-Reply-To: <9d91f7aa0702281752o31382827kb33b20848a507938@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox