Union All Views

[sumi_ocp]

I have two tables in different schemas..
scott.emp and ford.emp

I have another third schema called turner..
Created a union all view in turner schema as follows

create view turner.emp as
select empno,ename from scott.emp
union all
select empno,ename from ford.emp;


Now whenever I run any large queries against this union all view (turner.emp) , it does not use the indexes..It does a full table scan on the underlying tables (scott.emp and turner.emp)..

For the same query if I use the table instead of the view indexes are used..

Each table has about 2 million of rows..

Please help

thanks

sami

[jmodic]

can you post the query and some information about the indexes and data distribution in those two tables?

[sumi_ocp]

select rtrim(cl_claim.last_name)||', '||rtrim(cl_claim.first_name),
cl_claim.injury_dt, cl_claim.location, locdescr (cl_claim.policy_no, cl_claim.policy_seq, cl_claim.location),
cl_claim.department, deptdescr (cl_claim.policy_no, cl_claim.policy_seq, cl_claim.location, cl_claim.department),
cl_claim.acc_part, accpart(cl_claim.acc_part), cl_claim.acc_type, acctype(cl_claim.acc_type),
cl_claim.claim_id, cl_claim.clm_status, cl_claim.claim_no, to_char(cl_claim.policy_seq, '09'),
decode(substr(cl_claim.clm_status,1,1), 'O', 1, 'R', 2, 'X', 3, 'C', 4, 5),
po_policy.policy_no, get_insured_name(cl_claim.policy_no, cl_claim.policy_seq)
from
cl_claim, --This is the union all view does a full --
--table scan on both underlying tables
po_policy, po_mica
where ((cl_claim.policy_no = po_policy.policy_no)
and (cl_claim.policy_seq = po_policy.policy_seq)
and (po_policy.account_no = po_mica.account_no(+)))
and ((cl_claim.clm_status in ('O', 'C', 'R', 'X'))
and (cl_claim.clm_type in ('NR', 'R', 'X', 'RO' ))
and (po_policy.account_no = '2002012')
and (not (po_policy.cancel_dt = po_policy.inception_dt
and po_policy.reinst_dt = '01-jan-1901')))
order by 16 asc, 1 asc


Indexes are same on both underlying tables in the view..

cl_claim_pk(claim_no) Primary key

cl_claim_pk2(claim_id)
cl_claim_policy_idx(policy_no,policy_seq)

Thanks