Joining views of Union All, Ora 7.3

From: T. Zuschlag <ted_zuschlag_at_ous.edu>
Date: Fri, 3 Dec 1999 15:01:57 -0800
Message-ID: <829jv1$2k5$1@news.NERO.NET>

This is a question about how far I can go with Union All views-- selecting works, but what about joining?

I have a Union All view of two Employee tables (50K rows). It is very convenient for my data warehouse; the response time on 'selects' is just fine! Likewise, for a Union All view of two Person tables (50K rows). Complexity leaps up when I try to join the two (Union All) views! Any suggestions for Oracle 7.3 ?

[pasted from office intranet]
To understand what defaults the Optimizer takes when joining views of unions, I created the views, Emp_eval and Per_eval (unions of Employee and Person rows). Then I ran 'explain plan' on the JOIN of views, Emp_eval and Per_eval.
It is shown below, after an analogous 'explain plan' for the JOIN of tables, Employee and Person.

One conclusion is not surprising- - it is faster to join tables than Union-Views! Sticking with the Union-views raises the question though, "How can I tell the Optimizer to do better than a merge join and full-table scans ?"

codw> explain plan
2 set statement_id = 'TZ'
3 into plan_table for [ The table join ] 4 select e.ssn, gender from employee e, person p where e.pidm = p.pidm 5 ;

Explained.

OPERATION                OPTIONS              OBJECT_NAME      POSN


------------------------ -------------------- ---------------- ------


SELECT STATEMENT
  NESTED LOOPS                                                 1
    TABLE ACCESS         FULL                 PERSON           1
    TABLE ACCESS         BY ROWID             EMPLOYEE         2
      INDEX              RANGE SCAN           EMPL_PIDM_IDX    1

5 rows selected.

codw> explain plan
  2 set statement_id = 'TZ'
  3 into plan_table for [ The join of Unions ]   4 select e.ssn, gender from emp_eval e, per_eval p where e.pidm = p.pidm
  5 ;

Explained.

OPERATION                OPTIONS              OBJECT_NAME      POSN


------------------------ -------------------- ---------------- ------


SELECT STATEMENT
  MERGE JOIN                                                   1
    SORT                 JOIN                                  1
      VIEW                                    PER_EVAL         1
        UNION-ALL                                              1
          TABLE ACCESS   FULL                 PERSON           1
          TABLE ACCESS   FULL                 PERSON           2
    SORT                 JOIN                                  2
      VIEW                                    EMP_EVAL         1
        UNION-ALL                                              1
          TABLE ACCESS   FULL                 EMPLOYEE         1
          TABLE ACCESS   FULL                 EMPLOYEE         2

12 rows selected.

Thank you,
T. Zuschlag Received on Fri Dec 03 1999 - 17:01:57 CST