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

From: Andreas Mosmann <mosmann_at_expires-30-04-2007.news-group.org>
Date: Mon, 02 Apr 2007 21:24:45 +0200
Message-ID: <1175541885.04@user.newsoffice.de>

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

| 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

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de

Received on Mon Apr 02 2007 - 14:24:45 CDT