Re: indexing

From: mohamed houri <mohamed.houri_at_gmail.com>
Date: Wed, 20 Feb 2013 15:55:04 +0100
Message-ID: <CAJu8R6i9RVJipEpSaxvzH=QnsD_xDbYzG581v9WOn+BRoNYSBg_at_mail.gmail.com>

Jonathan Lewis has a very nice article about *over indexing* where interesting comments have been done.

http://jonathanlewis.wordpress.com/?s=over+indexing

What It might be interested to do is to search ‘’*redundant*” indexes i.e. indexes that are contained in other indexes for example ind1(a,b) is contained in ind2(a,b,c). Once those indexes isolated, you can make them invisible provided you are running 11g release and then monitor the performance of your application. If you noticed no performance degradation then it might be safe to drop those isolated indexes. But dropping indexes in a production environment is not a task that one has to accomplish without risks. This is why it is largely preferred to start thinking of not over indexing at design time. And one of the most popular reasons generating over indexing is the rule of indexing foreign key to avoid the deadlock threat when deleting, updating or merging the parent table. We don’t really have to systematically index a foreign key if there is an existing B-tree (or even a function based) index *starting *by the foreign key columns in any given order.

Best regards

Mohamed Houri

www.hourim.wordpress.com

2013/2/20 Wolfgang Breitling <breitliw_at_centrexcc.com>

> There is another little known problem with index monitoring. Until 11.2
> just gathering statistics on an index counted as "usage".
>
> On 2013-02-19, at 12:32 PM, Wolfgang Breitling wrote:
>
> > A word of caution: index usage monitoring is not foolproof.
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Bien Respectueusement
Mohamed Houri

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Feb 20 2013 - 15:55:04 CET