Re: Optimizing Queries Against UNIONed View

On 22 Jan 2007 12:51:57 -0800, artmt_at_hotmail.com wrote:

>I have two tables - CODE_TYPE1 and CODE_TYPE2 - containing CODE_NAME
>and CODE_DESC columns.
>
>I create the following view:
>CREATE OR REPLACE VIEW CODE_LOOKUP
>(CODE_TYPE, CODE_NAME, CODE_DESC)
>AS
>SELECT 'CODE_TYPE1' CODE_TYPE, CODE_NAME, CODE_DESC FROM CODE_TYPE1
>UNION ALL
>SELECT 'CODE_TYPE2' CODE_TYPE, CODE_NAME, CODE_DESC FROM CODE_TYPE2;
>
>When I issue the following query:
>SELECT * FROM CODE_LOOKUP WHERE CODE_TYPE = 'CODE_TYPE2';
>
>the optimizer chooses to scan both tables.
>
>Is there a way (other than a meterialized view) to make sure it goes
>directly to the CODE_TYPE2 table?
>Please see the complete scripts and explain plan below.
>
>Thanks
>Art

The tables are identical, so make one table with two list partitions on CODE_TYPE, one with CODE_TYPE = 'CODE_TYPE1', and one with CODE_TYPE = 'CODE_TYPE2'. Your query will not scan the partition that has the wrong value for CODE_TYPE.

Jaap. Received on Wed Jan 24 2007 - 10:33:51 CST