what is the table definition for t and the index?
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"rsegovia" <rafasegovia_at_yahoo.com> wrote in message
news:65fb86bd.0305300400.57f4c33d_at_posting.google.com...
> Hi there,
>
> I am executing this select statement:
>
> SELECT ...
> FROM t, c
> WHERE t.location_code = c.location_code (+)
> AND t.case_num = c.case_num (+)
> AND t.debtor_id = c.debtor_id (+)
> AND c.debtor_id IS NULL
> AND t.location_code = 'value'
> AND t.case_num = 'value';
>
> The explain plan is:
>
> SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=37585 Bytes=1315475)
> SEQUENCE OF D_SQ
> FILTER
> NESTED LOOPS (OUTER)
> TABLE ACCESS (FULL) OF T (Cost=28 Card=37585 Bytes=676530)
> INDEX (UNIQUE SCAN) OF XPKC (UNIQUE)
>
> But if I remove the outer Join:
>
> SELECT ...
> FROM t, c
> WHERE t.location_code = c.location_code
> AND t.case_num = c.case_num
> AND t.debtor_id = c.debtor_id
> AND c.debtor_id IS NULL
> AND t.location_code = 'value'
> AND t.case_num = 'value';
>
> the explain plan is:
>
> SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=35)
> SEQUENCE OF DEBTOR_EVENT_SQ
> NESTED LOOPS (Cost=2 Card=1 Bytes=35)
> INDEX (RANGE SCAN) OF XIE1T (NON-UNIQUE)(Cost=1 Card=1 Bytes=17)
> TABLE ACCESS (BY INDEX ROWID) OF T (Cost=1 Card=37585
> Bytes=676530)
> INDEX (UNIQUE SCAN) OF XPKT (UNIQUE)
>
>
> Any reason for this? How can I hint Oracle to use the index in T? I am
> using Oracle 8.1.
>
> Thanks,
>
> Rafael
Received on Fri May 30 2003 - 08:41:04 CDT
