[SQL] How to union table without union statement?

[calendarw]

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

Re: [SQL] How to union table without union statement?

[calendarw]

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

Re: [SQL] How to union table without union statement?

[calendarw]

I think the tables should contain 50k rows of record and it should be insert
7k rows per month.  And the condition of the database is running, so it
should not change the tables to partitioning right now.

I am looking for some JOIN statement but still doesn't understand how to use
JOIN to replace UNION, so dose anyone can give me direction?

Thanks.

On 3/1/07, Shane Ambler <[EMAIL PROTECTED]> wrote:


Have you done an EXPLAIN on the query?
Is there an index on the tagname columns?
If so does the EXPLAIN show them being used?

How many rows do you have in each table (roughly)?

Have you considered other structure options like partitioning?
Is there a real need to have these tables separate? or could you have
them all in one table with an column to identify the source of the log
entry?



--
Jr. P
calendarw