Union All Views
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Union All Views

  1. #1
    Join Date
    Mar 2002
    Posts
    60
    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

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    can you post the query and some information about the indexes and data distribution in those two tables?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Mar 2002
    Posts
    60
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width