Re: Performace using VIEW UNION ALL

Creating a view doesn't effect the performance, but putting fuctions like 'upper' lead to full table scan.

Shajith

Ron C. <rcalvane.nospamme_at_writeme.com> wrote in article <34e1cc51.7467464_at_news.voyager.net>...
> 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