Re: Why Oracle does not use index?

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Wed, 04 Dec 2002 07:47:16 +0200
Message-ID: <ask50l$jvs$1@ctb-nnrp2.saix.net>

Frank Jiang wrote:

> 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?

Statistics or to be more precise, the lack there of, is often a cause for the CBO (Cost Based Optimiser) having to guess the most effective join method.

Also, your assumption that because it has a PK, that the PK must be used, is incorrect.

I assume that your PK on the COMPANY table is in COMPANY_ID?

> 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
The reason for the full table scan of the COMPANY table is likely because there are no indexes that can be used to access the table via the STATE and CITY columns.

Because there are no usable indexes, it needs to perform a full table scan of table C. (how can it use the PK to find the criteria for STATE and CITY?) As the company ID has been indexed in the ACCOUNTS table, Oracle can use that index to join a row it finds from table C.

So the processing is something like this: for all rows in C
  read row
  check STATE and CITY criteria
  if true then // do the join

     read table A 
      using index ACCOUNTS_BTR_CID
      where A.company_id = C.current_row.company_id

  end-if
loop

If you have a SI (secondary index) on STATE and CITY, then that would have been used instead of the full table scan.

However - given the number of STATE values, this index is likely a candidate for a bitmap index instead.

--
Billy

Received on Tue Dec 03 2002 - 23:47:16 CST