od
) cpt_error
where
total.deptnum = error.deptnum(+) and
total.accounting_period = error.accounting_period(+) and
total.ledger = error.ledger(+) and
total.deptnum = cpt_error.deptnum(+) and
total.accounting_period = cpt_error.accounting_period(+) and
total.ledger = cpt_error.ledger(+)
order by
total.deptnum,
total.accounting_period,
total.ledger
外层查询where子句中的“(+)”是Oracle 特有的语法,代表外连接(outer join)。换言之:
select whatever
from ta,
tb
where ta.id = tb.id (+)
相当于:
select whatever
from ta
outer join tb
on tb.id = ta.id
下列SQL*Plus输出显示了该查询的执行计划:
10:16:57 SQL> set autotrace traceonly
10:17:02 SQL> /
37 rows selected.
Elapsed: 00:30:00.06
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
(Cost=1779554 Card=154 Bytes=16170)
1 0 MERGE JOIN (OUTER) (Cost=1779554 Card=154 Bytes=16170)
2 1 MERGE JOIN (OUTER) (Cost=1185645 Card=154 Bytes=10780)
3 2 VIEW (Cost=591736 Card=154 Bytes=5390)
4 3 SORT (GROUP BY) (Cost=591736 Card=154 Bytes=3388)
5 4 TABLE ACCESS (FULL) OF 'GLREPORT'
(Cost=582346 Card=4370894 Bytes=96159668)
6 2 SORT (JOIN) (Cost=593910 Card=154 Bytes=5390)
7 6 VIEW (Cost=593908 Card=154 Bytes=5390)
8 7 SORT (GROUP BY) (Cost=593908 Card=154 Bytes=4004