Re: O9i: How to create a matching index to a given query

On Mon, 02 Apr 2007 21:24:45 +0200, Andreas Mosmann <mosmann_at_expires-30-04-2007.news-group.org> wrote:

>Sorry, it is me again. I do not understand the following:
>
>select
>-- B.cbemerkung,
> B.cvnk
>from
> tbbaeume B
>where
> B.cidnebenanlage <> '0000000000'
>
>Table tbbaeume has 500 000 rows, only 65 of them has another
>cidnebenanlage than '0000000000'.
>
>There are some indexes, cvnk and cidnebenanlage are part of most of
>them, cbemerkung never.
>The statement above has the following explain plan:
>
>
>-----------------------------------------------------------------------------------------------
>| Id | Operation | Name | Rows |
>| Bytes | Cost |
>-----------------------------------------------------------------------------------------------
>| 0 | SELECT STATEMENT | | 297K|
>| 5819K| 79 |
>| 1 | BITMAP CONVERSION TO ROWIDS | | |
>| | |
>| 2 | BITMAP INDEX FAST FULL SCAN| XTBBAEUME_SMOU_NA_STR_VNK_NNK | |
>| | |
>-----------------------------------------------------------------------------------------------
>
>The query is answered within 1 millisecond.
>
>If I delete -- from the query the explain plan is
>
>
>--------------------------------------------------------------------
>| Id | Operation | Name | Rows | Bytes | Cost |
>--------------------------------------------------------------------
>| 0 | SELECT STATEMENT | | 297K| 7273K| 1365 |
>| 1 | TABLE ACCESS FULL | TBBAEUME | 297K| 7273K| 1365 |
>--------------------------------------------------------------------
>
>In that case it takes 20 seconds.
>
>I thought that an index has only effects on where/join and group by
>clauses?
>I tested to build an index on TBBAEUME(CVNK, CBEMERKUNG) and as expected
>the result was the same as 2nd try. A full table scan with very poor
>performance.
>
>Why the execution plan changes if I only change the fieldlist?
>
>Andreas Mosmann

In the first statement all columns in the select list are indexed: table doesn't need to be queried.
This is why you always should use dummy columns in exists queries.

In the second case you select a non-indexed column. Apparently the optimizer predicts this will take less I/O. This would be a case for to set event 10051.

Hth

-- 
Sybrand Bakker
Senior Oracle DBA