Re: Why Oracle does not use index?

"Alex Filonov" <afilonov_at_yahoo.com> wrote in message news:336da121.0212040836.a8d9eed_at_posting.google.com...
> frank_z_j_at_hotmail.com (Frank Jiang) wrote in message
news:<e4ffcd8f.0212031856.4012211d_at_posting.google.com>...
> > Hello
> > I have a large table which has B+ tree index on PK. When I join with
> > another smaller table, I check the execution plan, it use a nested
> > join loop. To my supprise, smaller table is access by index but large
> > table is access by a full scan.
> >
> > Can anybody give me reason?
> >
> Execution plan might also depend on sizes of tuples. Try to force
> plan you want with hints and compare execution time with the
> current plan. Sometimes things which seem to be obvious aren't.
>
> > accounts_btr(4000 tuples)
> > company_btr(200,000 tuples)
> > Query:
> > SELECT A.account_id, C.company_id, C.name, C.state, C.city
> > FROM accounts_btr A, company_btr C
> > WHERE A.company_id = C.company_id
> > AND C.state = 'CA'
> > AND C.city = 'Lakewood';
> >
> > EXCUTION_PLAN
> >
> > SELECT STATEMENT
> > TABLE ACCESS BY INDEX ROWID ACCOUNTS_BTR
> > NESTED LOOPS
> > TABLE ACCESS FULL COMPANY_BTR
> > INDEX RANGE SCAN ACCOUNTS_BTR_CID
What happens if you put an /*+ ORDERED */ hint in your select?

Regards,
Paul Received on Thu Dec 05 2002 - 17:15:52 CST