Re: Performace using VIEW UNION ALL

rcalvane.nospamme_at_writeme.com (Ron C.) writes:

>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,

yes, it will

>if the columns in each table
>are indexed, will the indexes be used?

No, because you have upper function

>Will this query take forever??

3 million lines is not so much, so it shouldn't take forever unless lines are long (tens? or hundreds? of columns)

>Please remove the 'nospamme' inserted by my newreader if you reply by
>email.
>Thanks,
>Ron
  Received on Fri Feb 13 1998 - 00:00:00 CST