SQL going after two date ranges and outer join

I have a select that looks something like:

select

   arh.artrxhdridx,
   ash.fromnameidx
from

     ar_trx_header_ship ash,
     ar_trx_header arh
where


      (arh.shipdatetime between '01-JAN-02' and '31-JAN-02' or

       arh.shipdatetime between '01-JAN-01' and '31-JAN-01') and
       arh.artrxhdridx = ash.artrxhdridx

The plan for this statement looks good:

SELECT STATEMENT Optimizer=RULE
  CONCATENATION
    NESTED LOOPS

      TABLE ACCESS (BY INDEX ROWID) OF AR_TRX_HEADER
        INDEX (RANGE SCAN) OF XIE4AR_TRX_HEADER (NON-UNIQUE)
      TABLE ACCESS (BY INDEX ROWID) OF AR_TRX_HEADER_SHIP
        INDEX (RANGE SCAN) OF XIF6902AR_TRX_HEADER_SHIP (NON-UNIQUE)
    NESTED LOOPS
      TABLE ACCESS (BY INDEX ROWID) OF AR_TRX_HEADER
        INDEX (RANGE SCAN) OF XIE4AR_TRX_HEADER (NON-UNIQUE)
      TABLE ACCESS (BY INDEX ROWID) OF AR_TRX_HEADER_SHIP
        INDEX (RANGE SCAN) OF XIF6902AR_TRX_HEADER_SHIP (NON-UNIQUE)

But if I introduce an outer join on AR_TRX_HEADER_SHIP, the optimizer insists on doing a
full table scan of AR_TRX_HEADER:

select

   arh.artrxhdridx,
   ash.fromnameidx
from

     ar_trx_header_ship ash,
     ar_trx_header arh
where


      (arh.shipdatetime between '01-JAN-02' and '31-JAN-02' or

       arh.shipdatetime between '01-JAN-01' and '31-JAN-01') and
       arh.artrxhdridx = ash.artrxhdridx (+)

The resulting plan is:

SELECT STATEMENT Optimizer=RULE
  NESTED LOOPS (OUTER)
    TABLE ACCESS (FULL) OF AR_TRX_HEADER     TABLE ACCESS (BY INDEX ROWID) OF AR_TRX_HEADER_SHIP       INDEX (RANGE SCAN) OF XIF6902AR_TRX_HEADER_SHIP (NON-UNIQUE) Can anybody tell me what's going on here?

Thanks in advance.

--steve Received on Tue Apr 09 2002 - 11:37:46 CDT