Re: tuning a query on union_all view..

Can you provide a listing of table descriptions and a listing of indexes? E. g.:

describe order_items
describe gift_order_items

column owner new_value usr
select user as usr from dual;
define table = ORDER_ITEMS

select	distinct
	decode (i.owner, null, null, decode (i.owner, user, null, '"' || i.owner ||
'".')) || '"' || i.index_name || '"' as index_name
	, '"' || c.column_name || '"' as column_name
	, i.uniqueness
	, c.column_position
	, i.partitioned
from	all_tables t
	, all_indexes i
	, all_ind_columns c
where	1 = 1
and	t.table_name in ('&&table.', upper ('&&table.'))
and	t.owner in ('&&own.', upper ('&&own.'))
and	t.table_name = i.table_name
and	t.owner = i.table_owner
and	i.index_name = c.index_name
and	i.owner = c.index_owner

--
order by index_name, column_position
/
define table = GIFT_ORDER_ITEMS
/

Regards,

Martin




NetComrade wrote:


> 

> 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