Performance impact using a VIEW UNION ALL

A current application (not designed by me) breaks up a logical business listing table into 40 physical Oracle tables based upon business type ( repeat, I did not design this)

I want to create a VIEW that will allow me to search for a business name in all 40 tables with a single query. There are about 3 million rows in total across all 40 tables.

I would create the view as:  

select....
UNION ALL
select...
etc.

Does anyone know if a query such as the following with result in full table scans even if there are indexes on the columns of each table? Or will the individual indexes from each table be used? Will this kind of query perform ok?

SELECT business_name, city, state
FROM MY_40_TABLE_VIEW
WHERE upper(business_name) LIKE upper('%Bar and Grill%') AND STATE = 'NY'; Please remove the 'nospamme' inserted that may be inserted by my newgroup reader if you reply by email.
Thanks,
Ron Received on Wed Feb 11 1998 - 00:00:00 CST