More efficient query to extract same data (same schema) from different databases

Analytics
Enthusiast

More efficient query to extract same data (same schema) from different databases

Below is the query example.

 

SELECT a.col1, a.col2, a.col3
    FROM   db_1.tab1 a
    WHERE a.col1='111'

    UNION

    SELECT a.col1, a.col2, a.col3
    FROM   db_2.tab1 a
    WHERE a.col1='111'

    UNION

    SELECT a.col1, a.col2, a.col3
    FROM   db_3.tab1 a
    WHERE a.col1='111'

If the SELECT, JOIN and WHERE clauses are the same across different databases with the same naming schema, is there a more efficient way of writing this query and improve performance? thanks

2 REPLIES
Senior Apprentice

Re: More efficient query to extract same data (same schema) from different databases

Not really. You might look at using 'UNION ALL' instead of 'UNION' - but that changes the functionality and may/may not be valid in your situation.

 

One other possibility (and it is only a possibility): if you know that one of those tables cannot contain data that meets the WHERE clause then define a check constraint as appropriate. With that in place the optimiser will typically not access the table at all.

   This is most often the case when the three tables have data from different date ranges (let's says different years) and the query only needs data from one or two of those years. With check constraints (or possibly PPI) defined on each table you will often find that only 2 out of 3 tables are accessed.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: More efficient query to extract same data (same schema) from different databases

Thank you Dave,

 

It works for me, change to 'UNION All' can speed up the extraction duration, and add in CHECK Clause can also enhance the performance!