Perform Query and count rows on multiple identical table

Question

I have multiple tables created for each date to store some information for each date. For example History3108,History0109..etc All of these tables share same schema. Some time i need to query multiple tables and get the rows and count of records. What is the faster way of doing this in oracle and SQL Server?

Currently i am doing like this...

When i need count of multiple tables: Select count(*) for each table and add
When i need records of multiple tables: select * from table1, select * from table2 (Basically select * for each table.)

Would this give better performance if we include all of the queries in one transaction?

@mxix probably to archive data each day to different tables (by looking at the example names) — yamny
– yamny, Commented Sep 1, 2014 at 14:11
Are you using unions? I guess 3108 and 0109 are dates (31st August and 01st September?). By the look of it you might have a large number of tables? And the number of tables are growing? Please add this information to the question. — sampathsris
– sampathsris, Commented Sep 1, 2014 at 15:11

yamny · Accepted Answer · 2014-09-01 14:30:43Z

1

With UNION you can get records from multiple tables that shares the same datatype group and column names. For example, if you want to see all records from multiple tables:

(select * from history3108)
union all
(select * from history0109)
union all
(select * from history0209)
/* [...] and so on */

and if you want to count all records from these tables:

select count(*) from (
  (select * from history3108)
  union all
  (select * from history0109)
  union all
  (select * from history0209)
  /* [...] and so on */
);

Oracle Docs - The UNION [ALL], INTERSECT, MINUS Operators

edited Sep 1, 2014 at 14:30

answered Sep 1, 2014 at 14:20

yamny

6604 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nasir Over a year ago

Does this give better performance compared to selecting tables individually?

Collectives™ on Stack Overflow

Perform Query and count rows on multiple identical table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related