On 3 Dec 2002 18:56:02 -0800, Frank Jiang wrote:
>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?
>
>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
Which explain plan did you expect ?
Received on Wed Dec 04 2002 - 01:00:23 CST
