Re: SQL going after two date ranges and outer join

Agreed that the correct driving table is being chosen. The problem is it's not using the available
date index.

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 (+)

..and..

where

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

..generate two different plans. (the later using the index on shipdatetime, the former does not).

This also confuses me. My AR_TRX_HEADER table need not be directly involved in the outer
join for the plan to change. Consider the following query without an outer join, and the
next query, which contains an outer join on from ar_trx_detail to ar_trx_header_ship. In the
second query, the optimizer abandons use of the date index on the shipdatetime column of
the ar_trx_header table and does a full table access instead.

select

   count(*)
from

     ar_trx_header_ship ash,
     ar_trx_detail ard,
     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 = ard.artrxhdridx and
       ard.shiphdridx = ash.shiphdridx

..plan is:

SELECT STATEMENT Optimizer=RULE
  SORT (AGGREGATE)
    CONCATENATION

      NESTED LOOPS
        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_DETAIL
            INDEX (RANGE SCAN) OF XIF6429AR_TRX_DETAIL (NON-UNIQUE)
        INDEX (UNIQUE SCAN) OF XPKAR_TRX_HEADER_SHIP (UNIQUE)
      NESTED LOOPS
        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_DETAIL
            INDEX (RANGE SCAN) OF XIF6429AR_TRX_DETAIL (NON-UNIQUE)
        INDEX (UNIQUE SCAN) OF XPKAR_TRX_HEADER_SHIP (UNIQUE)

..whereas the same query with an outer join produces:

select

   count(*)
from

     ar_trx_header_ship ash,
     ar_trx_detail ard,
     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 = ard.artrxhdridx and
       ard.shiphdridx = ash.shiphdridx (+)

SELECT STATEMENT Optimizer=RULE
  SORT (AGGREGATE)
    NESTED LOOPS (OUTER)

      NESTED LOOPS
        TABLE ACCESS (FULL) OF AR_TRX_HEADER
        TABLE ACCESS (BY INDEX ROWID) OF AR_TRX_DETAIL
          INDEX (RANGE SCAN) OF XIF6429AR_TRX_DETAIL (NON-UNIQUE)
      INDEX (UNIQUE SCAN) OF XPKAR_TRX_HEADER_SHIP (UNIQUE)

..as for why we don't use the cost based optimizer, the answer is that I'm finding that most of
our queries run slower with the cost based optimizer than without. For example, in this case,
the above query without the outer join results in a plan such as:

SELECT STATEMENT Optimizer=HINT: CHOOSE (Cost=403 Card=1 Bytes=21)   SORT (AGGREGATE)
    HASH JOIN (Cost=403 Card=97424 Bytes=2045904)

      TABLE ACCESS (FULL) OF AR_TRX_HEADER (Cost=17 Card=2730 Bytes=30030)
      NESTED LOOPS (Cost=174 Card=173151 Bytes=1731510)
        TABLE ACCESS (FULL) OF AR_TRX_DETAIL (Cost=174 Card=633027
Bytes=3798162)
        INDEX (UNIQUE SCAN) OF XPKAR_TRX_HEADER_SHIP (UNIQUE)

Which is another question I have: why is the cost based optimizer making such bad decisions? All of
the tables involved are analyzed. There are indexes on all columns involved.

I'm trying to learn more about how the (respective) optimizers make their decisions about these queries.

Thanks in advance.

--steve

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:f1j6bus8ktvfc8604f84tr471g67aaqha1_at_4ax.com...
> On Tue, 09 Apr 2002 16:37:46 GMT, "Steve Mitchell" <stevem_at_hdcsi.com>
> wrote:
>
> >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
> >
>
> Nothing. As you are using an outer join Oracle must use the
> AR_TRX_HEADER as driving table.
> Question: why on earth are you still using the Rule Based Optimiser?
> It hasn't been improved since 1994, the end of the Stone Age.
>
>
> Regards
>
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Wed Apr 10 2002 - 10:28:25 CDT