tuning a query on union_all view..

From: NetComrade <andreyNSPAM_at_bookexchange.net>
Date: Fri, 07 Dec 2001 17:00:36 GMT
Message-ID: <3c10e29e.594334728@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

Received on Fri Dec 07 2001 - 11:00:36 CST