Retrieving data from base table using where clause in Union All views

Database

Retrieving data from base table using where clause in Union All views

Hello everyone,

We are building Enterprise data warehouse and as a part of architecture it has been to maintain the source specific transaction tables Eg : table A, table B, Table C  and on top of it we create one common view which pertains the global standards and it performs union all with list of values in where condition which uniquely identifies the source table (A or b or c). Eg :-

Replace view Combine as locking row for access

select {list of columns} from A where company in (x,f,d,e,b,etc)

UNION ALL

select {list of columns} from B where company in (c,r,q,s,v)

UNION ALL

Select {list of columns} from C where company in (T,u,w,d,y)

We created this union all view using list of values in where clause and it is able to select data only from the base table using company which we passed in the where condition. 

Problem : The above solution works well if we have minimal list of values in the where clause. The moment values in the where clause increases then it is going for a full table scan and not able to find the exact table eventhough we pass the company relevant to the base table.

Can someone suggest the right approach to overcome the full table scan issue for the above example??? Please let me know if any additional questions.

Thanks,

Sudharsan