Re: tuning a query on union_all view..

don't my tables qualify as identical?

On Fri, 7 Dec 2001 17:37:34 -0000, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>
>There are some extremely strict rules about
>Oracle's ability to push join predicates into
>union all views. These are based around the
>original implementation of Oracle 7's
>"partitioned views", but are begin relaxed
>from version to version.
>
>However, if you are running rule based optimization,
>you are probably restricted to cases where
> a) The tables have identical columns in the same order
> b) The indexes on the tables are structurally identical
> c) The view definition is effectively
> select * from table1
> union all
> select * from table2
>
>Purely for the sake of experiment, you could try the CBO
>to see how this affects the situation. If you do,
>there is a hidden parameter named something like
> _push_union_join_view
>and another called something like
> _push_join_view_pred
>which default to FALSE on 8.1 and TRUE on 9.0
>(I think) - so you could play with that and see what
>happens.
>
>on
>
>--
>Jonathan Lewis
>http://www.jlcomp.demon.co.uk
>
>Host to The Co-Operative Oracle Users' FAQ
>http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>Author of:
>Practical Oracle 8i: Building Efficient Databases
>
>Screen saver or Life saver: http://www.ud.com
>Use spare CPU to assist in cancer research.
>
>NetComrade wrote in message <3c10e29e.594334728_at_news.globix.com>...
>>we have an union_all view (don't ask why)
>>
>>SELECT
>> order_id
>> ,'REGULAR'
>> ,id
>> ,product_id
>> ,discount_code_id
>> ,price
>> ,quantity
>> ,fulfilled_state
>> ,subscription_renewal_code
>> ,create_ts
>> ,update_ts
>>FROM order_items
>>UNION ALL
>>SELECT
>> order_id
>> ,'GIFT'
>> ,id
>> ,product_id
>> ,discount_code_id
>> ,price
>> ,quantity
>> ,fulfilled_state
>> ,subscription_renewal_code
>> ,create_ts
>> ,update_ts
>>FROM gift_order_items
>>
>>the tables are pretty much identical
>>so r the indexes..
>>as you can see the following query uses both indices
>>select * from test_all_order_items where order_id=:var
>>0 SELECT STATEMENTCost=
>>1 VIEW TEST_ALL_ORDER_ITEMS
>>2 UNION-ALL
>>3 TABLE ACCESS ORDER_ITEMS
>>4 INDEX ORDER_ITEMS_ORDERS
>>5 TABLE ACCESS GIFT_ORDER_ITEMS
>>6 INDEX GIFT_ITEMS_ORDERS
>>
>> 2* select id from orders where member_id=1
>>
>>0 SELECT STATEMENTCost=
>>1 TABLE ACCESS ORDERS
>>2 INDEX ORDERS_MEMBERS
>>
>>while this query fails to utilize the needed indices... why?
>>select * from test_all_order_items t, orders o
>>where t.order_id = o.id and o.member_id = 2682
>>
>>0 SELECT STATEMENTCost=
>>1 NESTED LOOPS
>>2 VIEW TEST_ALL_ORDER_ITEMS
>>3 UNION-ALL
>>4 TABLE ACCESS ORDER_ITEMS
>>5 TABLE ACCESS GIFT_ORDER_ITEMS
>>6 TABLE ACCESS ORDERS
>>7 INDEX ORDERS_PK
>>
>>We are using rule-based optimizer.. this is 8.1.7
>>
>>thnx.
>>.......
>>We use Oracle 8.1.6-8.1.7 on Solaris 2.6, 2.7 boxes
>>Andrey Dmitriev eFax: (978) 383-5892 Daytime: (917) 750-3630
>>AOL: NetComrade ICQ: 11340726 remove NSPAM to email
>
>

.......
We use Oracle 8.1.6-8.1.7 on Solaris 2.6, 2.7 boxes

Andrey Dmitriev	 eFax: (978) 383-5892  Daytime: (917) 750-3630
AOL: NetComrade	 ICQ: 11340726 remove NSPAM to email