Re: Help:Index performance issue

Comments embedded

Also: please stop crossposting

Regards
Sybrand Bakker, Oracle DBA

<u518615722_at_spawnkill.ip-mobilphone.net> wrote in message news:l.990554346.1418609619@[198.138.198.252]...
> We have a table with 13 indexes built
> on it.

This is clearly outrageous and will kill insert and update performances

We have one sql statement runing
> against it.

So why 13 indexes?
>
> When we run explain plan for it, there is
> no full table scan, which means the sql
> does not have any problem, right?

This is one the most frequent misunderstandings about Oracle. A FTS is not necessarily bad, if the FTS consumes less resources than any index scan. So, without further info, I can't answer this question

>
> Where else could we do to improve the
> performance? I am thinking keep some
> index in the keep_pool, but how do you
> select which index to keep in the keep_pool?
> The first one that is used?

It is just useless to try to pin indexes. Indexes are indexes: lookup mechanisms, and you will never be able to pin complete indexes. Also unless you *force* *every* sql statement you have on that table to use the same index (so the question: why 13 indexes pops up? again) there is no guarantee the optimizer wouldn't choose different indexes. (You have 13 of them, haven't you?)

This all clearly looks like an issue of bad design.

>
> Any suggestions? Thanks
>
>
>
>
> --
> Sent by dbadba62 from hotmail within area com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com/cgi/content/new
Received on Sat Jul 21 2001 - 16:42:17 CDT