Re: index not in use

On Feb 28, 7:44 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

Thanks a lot. That helps me a lot.
But still I don't understand one thing.
SQL> select count(1) from MCC_USER.SC_ORGANIZATION where   2 ORGANIZATIONTYPE='3';   COUNT(1)

       797

Why in explain plan, this query retrieves 89518, when it has 797 rows. Received on Wed Feb 28 2007 - 20:45:38 CST