Re: Performance impact using a VIEW UNION ALL

I had a database designed similar to this. If I remember right, with version 7.1.4 I got full table scans. When I upgraded to 7.2.3, the indexes were used. If you want to check, you can turn on tracing and use Explain Plan to see if the indexes are being used.

HTH,
Jim Anderson

Ron C. wrote:

> 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 Fri Feb 13 1998 - 00:00:00 CST