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 <[EMAIL PROTECTED]> wrote:
Can you provide a schema? ------------------------------ *From:* [EMAIL PROTECTED] [mailto: [EMAIL PROTECTED] *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:[EMAIL PROTECTED] Thank you.
-- Jr. P calendarw
