Re: tuning a query on union_all view..

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

1. The tables have identical columns in the same order
2. The indexes on the tables are structurally identical
3. 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