Re: index not in use

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 28 Feb 2007 06:44:38 -0800
Message-ID: <1172673878.929238.230120@q2g2000cwa.googlegroups.com>

On Feb 28, 8:35 am, "Steve Robin" <ocma..._at_gmail.com> wrote:
> Even Simple query is also not using this index(It's Bitmap Index).
>
> SQL> explain plan for
> 2 select * from MCC_USER.SC_ORGANIZATION where
> ORGANIZATIONTYPE='3';
>
> Explained.
>
> SQL> @?\rdbms\admin\utlxplp.sql
>
> PLAN_TABLE_OUTPUT
> ---------------------------------------------------------------------------­-------------------------
>
> -------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes |
> Cost |
> -------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 89518 | 24M|
> 1040 |
> |* 1 | TABLE ACCESS FULL | SC_ORGANIZATION | 89518 | 24M|
> 1040 |
> -------------------------------------------------------------------------
>
> But I give hint.
>
> SQL> explain plan for
> 2 select /*+ INDEX(SC_ORGANIZATION IND_SC_ORG_ORG_TYPE) */* from
> MCC_USER.SC_ORGANIZATION where ORGANIZATIONTYPE=3;
>
> Explained.
>
> SQL> @?\rdbms\admin\utlxplp.sql
>
> PLAN_TABLE_OUTPUT
> ---------------------------------------------------------------------------­-------------------------
>
> ---------------------------------------------------------------------------­----------
> | Id | Operation | Name | Rows |
> Bytes | Cost |
> ---------------------------------------------------------------------------­----------
> | 0 | SELECT STATEMENT | | 89518
> | 24M| 14108 |
> | 1 | TABLE ACCESS BY INDEX ROWID | SC_ORGANIZATION | 89518
> | 24M| 14108 |
> | 2 | BITMAP CONVERSION TO ROWIDS| |
> | | |
> |* 3 | BITMAP INDEX FULL SCAN | IND_SC_ORG_ORG_TYPE |
> | | |
> ---------------------------------------------------------------------------­----------
>
> I donn't understand BITMAP CONVERSION TO ROWIDS.
>
> SQL> select count(distinct ORGANIZATIONTYPE) from
> MCC_USER.SC_ORGANIZATION;
>
> COUNT(DISTINCTORGANIZATIONTYPE)
> -------------------------------
> 3
>
> So I think bitmap index is more helpful.

Oracle is predicting that the cost of retrieving the data by a full tablescan is 1040, while it is predicting that the access by index will have a cost of 14108. The simple answer is: 1040 < 14108

Plan with the cost of 1040 is predicted to be more efficient than the plan with a predicted cost of 14108, so the more expensive plan is discarded. A 10053 trace will show you this decision making process in action, but it takes a while to learn how to read such a trace file.

A very simple run through, assuming (this is a little different than your situation):

* 89,518 rows in the table occupying 24MB
* 8KB block size
* db file multiblock read count = 8
* B*Tree index exists (numbers will likely be different for a bitmap

index)
* every block read results in a physical read

24,000,000 / 89,518 = 268 bytes per row
8,192 / 268 = Roughly 30 rows per 8KB block

Assuming db file multiblock read count = 8: 30 * 8 = 240 rows read per physical I/O

For a B*Tree index:
At least 3 I/Os to read the index + 1 I/O to read the row: 3 + 1 = 4 IOs per row = 1/4 row per physical I/O

In this example, performing a full table scan is theoretically 960 times more efficient than an index lookup, if 99.99% of the rows need to be returned. This theoretical calculation has enough errors in it to be the basis for a long article. The point is simply that table access by index lookup is not always the most efficient method to retrieve data.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Wed Feb 28 2007 - 08:44:38 CST