Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 6AABB9FB667 for ; Wed, 28 Feb 2007 21:53:32 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 32680-01-3 for ; Wed, 28 Feb 2007 21:53:05 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.190]) by postgresql.org (Postfix) with ESMTP id 938319FB491 for ; Wed, 28 Feb 2007 21:52:41 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id x4so764337nfb for ; Wed, 28 Feb 2007 17:52:40 -0800 (PST) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=DMw8tqeybb20x4hHxlYG+NbeV9GxVqOqXuDyeOasLqUlL4Uu2+STht5NEtueQYGdJPZL9eNdnIt8wof8zvLq18aBgkFHoFYbS9sfCeF+oKOMvf5lWnpruwbkz/ApGDOEHCVTnvy2UlIzmA8pkKd58E1a84Q2SK0F4qXAUvpYgac= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=dFmjCJlbil2E0pMJrYE9hShJQUEuXe2iGI4tOG+FPHu0A2B5Un/5x/DAd8Q+gfpBiniwUawlMxmvFTMRNGzR+BFMbTJNv5s8XFqtu1GvRxKhUzhOBuQF4mfMVqozSz8KxjtlcYQZgxJGkIF228AZpabdclnXG7i4URAHmLL7qjE= Received: by 10.48.202.11 with SMTP id z11mr4869189nff.1172713960299; Wed, 28 Feb 2007 17:52:40 -0800 (PST) Received: by 10.49.36.19 with HTTP; Wed, 28 Feb 2007 17:52:40 -0800 (PST) Message-ID: <9d91f7aa0702281752o31382827kb33b20848a507938@mail.gmail.com> Date: Thu, 1 Mar 2007 09:52:40 +0800 From: calendarw To: pgsql-sql@postgresql.org Subject: Re: How to union table without union statement? In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_37305_13419124.1172713960252" References: <9d91f7aa0702280233r7c91dedav1ddfaffa92877280@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/304 X-Sequence-Number: 27638 ------=_Part_37305_13419124.1172713960252 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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 ------=_Part_37305_13419124.1172713960252 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 ------=_Part_37305_13419124.1172713960252--