Why is this unrelated table scanned?

Question

I have a query which is crossing two tables

select count(*) from ingenium.empevt, ingenium.evt where empevt_evtfk = evt_pk

it takes quite a long time to run and in trying to figure out why I looked at the plan

    Operation   Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE   634 K   75981                           
  HASH JOIN  634 K 180 M 75981                           
    HASH JOIN    845 K 234 M 8930                           
      HASH JOIN  465 K 53 M 3118                           
        NESTED LOOPS     620 K 15 M 1618                           
          NESTED LOOPS   1   20   2                           
            TABLE ACCESS BY INDEX ROWID INGENIUM.USR 1   10   1                           
              INDEX UNIQUE SCAN INGENIUM.XAK1USR 1                               
            TABLE ACCESS BY INDEX ROWID INGENIUM.USR 1   10   1                           
              INDEX UNIQUE SCAN INGENIUM.XAK1USR 1                               
          INDEX RANGE SCAN  INGENIUM.SECUSREMP_USREMP 89 M 600 M 1616                           
        TABLE ACCESS FULL   INGENIUM.TBLEMP 620 K 55 M 1150                           
      TABLE ACCESS FULL INGENIUM.TBLEMPEVT 1 M 182 M 4000                           
    TABLE ACCESS FULL   INGENIUM.SECUSREMP 89 M 600 M 27013

What I don't like is the table access full on INGENIUM.SECUSREMP_USREMP which has a huge number of rows. I don't know why it is scanning that table since it wasn't in the query. INGENIUM.SECUSREMP_USREMP has a foreign key which references into ingenium.emp.emp_pk, however I don't see why that would matter.

Is there some way to rewrite this query such that it doesn't scan a seemingly unrelated table? Why is it scanning that table in the first place?

Answer 1

Presumably both empevt and evt are views, not tables? Your query plan shows (only) these tables being accessed:

• USR
• TBLEMP
• TBLEMPEVT
• SECUSREMP

What are the definitions of these views? Unless of course you are looking at the wrong plan!