Hi,
You can consider using partition view if your version is 7.3.
You must put integrity constraints to your different tables.
It will react so that queries won't even scan tables not responding to the
constraint.
Shajith, T V wrote in message
<01bd36d8$eb9fdbc0$78b3448b_at_jmartintemp.bose.com>...
>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
