Home Combine queries to multiple tables having the same columns in to one query
Reply: 1

Combine queries to multiple tables having the same columns in to one query

Marko Taht
1#
Marko Taht Published in 2017-12-05 11:46:15Z

I have 6 tables. All of them can have millions and millions of lines of data. All of the tables have object_id, changer_id, date_created columns.

I need to make a query on all of the tables

SELECT object_id
FROM table#
WHERE changer_id=someId
AND date_created > dateA
AND dateCreated < dateB

or its grails equivalent

table#.createQuery().list{
        projections{property('object_id')}
        eq('changer_id', someId)
        ge('dateCreated' dateA)
        le('dateCreated', dateB)
}

Same query will be made on all of the 6 tables, and then the results are combined and all duplicates are removed.

Im not using between because dateB might not always exist so it would be ommited.

Is there a way to combine this query into single query over 6 tables or any other way to make it faster than 6 separate queryes?

Jasen
2#
Jasen Reply to 2017-12-08 04:53:35Z

SQL inheritance:

assuming your tables are table1, table2, etc,

setup: do this once:

create table combined ( like table1 );

alter table table1 inherits combined;
alter table table2 inherits combined;
alter table table3 inherits combined;
alter table table4 inherits combined;
alter table table5 inherits combined;
alter table table6 inherits combined;

the query, do this many times as needed:

SELECT object_id
FROM combined
WHERE changer_id=someId
AND date_created > dateA
AND dateCreated < dateB

postgresql will internally generate the unions needed.

I'm assuming that all the tables have the same columns, if not exclude any columns that are missing from some tables when you create the combined table. (you don't have to use like... , you can use the more common list-of-columns syntax)

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.306458 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO