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
Received on Wed Dec 04 2002 - 10:36:14 CST
