Re: How many is too many
Date: Thu, 11 Aug 2011 23:18:14 -0500
Message-ID: <CAJOkrQY2R74G73HDn7-+naFHMsH3y3NnxomWTkVhgKPpE62i5w_at_mail.gmail.com>
You don't have to just wonder whether you have the *best* indexes to optimize all your queries *and *your inserts, updates, deletes and merges. You can actually figure it out. Two words: Tapio Lahdenmäki<http://www.amazon.com/gp/entity/Tapio-Lahdenmaki/B001ITX914#?ie=UTF8&tag=methodrcom-20&linkCode=ur2&camp=1789&creative=390957> .
Cary Millsap
Method R Corporation
http://method-r.com
On Thu, Aug 11, 2011 at 10:17 PM, Chitale, Hemant Krishnarao < Hemant.Chitale_at_sc.com> wrote:
> A simple calculation would be :****
>
> ** **
>
> If the INSERT/DELETE overhead caused by the presence of 12 indexes is
> 12x, then the INSERT/DELETE overhead caused by the presence of 13 indexes
> is 13x.****
>
> Thus, the “increase” in overhead is 1/12th !****
>
> (UPDATE overhead is a function of which columns are updated relative to
> which indexes hold those columns).****
>
> ** **
>
> [By that calculation, the addition of a second index to a 1-index table is
> a 100% increase in Index overhead. But, since the Index overhead [value
> ‘x’] itself is very low for a single index, the 100% increase is not
> noticeable !]****
>
> ** **
>
> However, this is an opportunity to review **existing** indexes.****
>
> ** **
>
> ** **
>
> Hemant K Chitale****
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Ram Raman
> *Sent:* Friday, August 12, 2011 4:45 AM
> *To:* ORACLE-L
> *Subject:* How many is too many****
>
> ** **
>
> Listers,****
>
> ****
>
> I am looking at a table in our system and it has 12 indexes, we are
> planning on adding another one. I am aware of the effects of having too many
> indexes, but in this case adding an extra index helps a certain query that
> runs slow. Other queries and most other operations against the table
> are acceptable too. I see a few tables like this; is there a number above
> which is considered a no-no when it comes to adding more indexes. ****
>
> ****
>
> PS. The tables and queries are structured in a way that seem to require
> several indexes - it is a third party product. ****
>
> ****
>
> TIA,****
>
> Ram.****
>
> This email and any attachments are confidential and may also be privileged.
> If you are not the addressee, do not disclose, copy, circulate or in any
> other way use or rely on the information contained in this email or any
> attachments. If received in error, notify the sender immediately and delete
> this email and any attachments from your system. Emails cannot be guaranteed
> to be secure or error free as the message and any attachments could be
> intercepted, corrupted, lost, delayed, incomplete or amended. Standard
> Chartered PLC and its subsidiaries do not accept liability for damage caused
> by this email or any attachments and may monitor email traffic.
>
> Standard Chartered PLC is incorporated in England with limited liability
> under company number 966425 and has its registered office at 1 Aldermanbury
> Square, London, EC2V 7SB.
>
> Standard Chartered Bank ("SCB") is incorporated in England with limited
> liability by Royal Charter 1853, under reference ZC18. The Principal Office
> of SCB is situated in England at 1 Aldermanbury Square, London EC2V 7SB. In
> the United Kingdom, SCB is authorised and regulated by the Financial
> Services Authority under FSA register number 114276.
>
> If you are receiving this email from SCB outside the UK, please click
> http://www.standardchartered.com/global/email_disclaimer.html to refer to
> the information on other jurisdictions.
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Aug 11 2011 - 23:18:14 CDT