Re: SQL going after two date ranges and outer join

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 Tue Apr 09 2002 - 15:18:22 CDT