Re: Performance impact using a VIEW UNION ALL

In 7..3 you can set up a partitioned view. Each physical table must have the exact same indexes, constraints, etc... Ensure that each table has a check constraint limiting it to the "business type" included. When you create the partition view and go to access the view based upon some value and <required> the business type, the cost-based optimizer will filter out the tables that don't include that business type and go straight to the table that does. So, if you know the business type associated with the name you are looking for, you are all set. Even if you don't know the business type, as long as there is an index on business_name, then the view will use that index against each table to find what you are looking for. We had 30 months of data partitioned by date accessed by a partition view, and it worked pretty well. Each month had 30 - 40 million rows. I hope you have at least 7.3 !!
Alan V

Ron C. wrote in message <34e1c877.6481380_at_news.voyager.net>...
>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