Re: indexing
- From: mohamed houri <mohamed.houri@xxxxxxxxx>
- To: breitliw@xxxxxxxxxxxxx
- Date: Wed, 20 Feb 2013 15:55:04 +0100
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
dont 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@xxxxxxxxxxxxx>
> 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.
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Bien Respectueusement
Mohamed Houri
--
//www.freelists.org/webpage/oracle-l
Other related posts:
