Re: Why not using index in execution plan?

Frank Jiang wrote:
> Hello,
> This is for my database class. Assignment due in 3 days. Please help
> me.
>
> table COMPANY_BTR (200,000 rows) B+ tree index on Company_ID(PK)
> table ACCOUNTS_BTR (4,000 rows) B+ tree index on Company_ID,
> Account_ID(PK)
>
> 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
>
> My question is why Oracle does a full table scan on big table without
> using index. I believe Oracle must have a reason to do this.
>
> Another question is that is the full table scan step the inner loop?
>
> Thank you.
>
> Frank Jiang

There are many reasons for choosing a full table scan over an index scan (yes, the 'table access full' means a full table scan). If your query would return a large portion of the rows (only the optimizer knows what that means), it would be a waste of time to read most of the index plus most of the table. It must have decided that a lot of companies are located in Lakewood, CA.

Also, if you build a small test database to try this out ... without all 200,000 rows in COMPANY_BTR ... Oracle might decide it can fit all (most of) the rows in memory buffers. But you'd get a different execution plan on the production database.

Then again, there could be a hundred other reasons. Received on Thu Dec 05 2002 - 10:22:49 CST