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
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.
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!