Performace using VIEW UNION ALL

We have an application (I didn't design this) that has 3 million business listings spread across 40 Oracle tables based upon business type (repeat, I didn't design this).

I want to create a VIEW to allow a query to search all 40 tables using UNION ALL such as:
select...
UNION ALL
select...
etc.

With a query such as:

SELECT business_name, city, state
from MY_40_TABLE_VIEW
where upper(business_name) like upper('%bar and grill%') and state = 'NY';

Will this result in full table scans or, if the columns in each table are indexed, will the indexes be used? Will this query take forever??

Please remove the 'nospamme' inserted by my newreader if you reply by email.
Thanks,
Ron Received on Wed Feb 11 1998 - 00:00:00 CST