Re: Performance impact using a VIEW UNION ALL

>Subject: Performance impact using a VIEW UNION ALL
>From: rcalvane.nospamme_at_writeme.com (Ron C.)
>Date: 2/11/98 4:13PM GMT
>Message-id: <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
>
>
>
>
>
>
>

If the indexesare only on business_name then FTS will be employed since a function has been applied to it. If there are indexes on STATE and you have analyzed the tables in the view, then the optimiser may choose to use such indexes if you are using Cost Based Optimisation AND the optimiser thinks it will retrieve fewer rows using it - an idea may be to build the view using hints or to only analyze the index columns that you are interested in. HTH
 Rob Received on Thu Feb 12 1998 - 00:00:00 CST