FAQ
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.

Search Discussions

  • Morten Egan at Aug 11, 2011 at 9:17 pm
    The number that is considered a no-no, is the number where things start to
    go unacceptably slow :)

    Joking aside, if your program does what it is supposed to do, in an
    acceptable time, then it really doesn't matter.

    2011/8/11 Ram Raman
    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.
    --
    Regards,
    Morten Egan
    http://www.dbping.com

    --
    http://www.freelists.org/webpage/oracle-l
  • Lange, Kevin G at Aug 11, 2011 at 9:22 pm
    That is a perfectly stated response. Too many is when adding them
    starts to hurt performance. I will not tell you how many one of our
    tables has because you would call me nuts.

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Morten Egan
    Sent: Thursday, August 11, 2011 4:18 PM
    To: veeeraman_at_gmail.com
    Cc: ORACLE-L
    Subject: Re: How many is too many

    The number that is considered a no-no, is the number where things start
    to go unacceptably slow :)

    Joking aside, if your program does what it is supposed to do, in an
    acceptable time, then it really doesn't matter.

    2011/8/11 Ram Raman

    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.

    --
    Regards,
    Morten Egan
    http://www.dbping.com

    This e-mail, including attachments, may include confidential and/or
    proprietary information, and may be used only by the person or entity
    to which it is addressed. If the reader of this e-mail is not the intended
    recipient or his or her authorized agent, the reader is hereby notified
    that any dissemination, distribution or copying of this e-mail is
    prohibited. If you have received this e-mail in error, please notify the
    sender by replying to this message and delete this e-mail immediately.

    --
    http://www.freelists.org/webpage/oracle-l
  • Kellyn Pot'vin at Aug 11, 2011 at 9:50 pm
    I have to add this a bit to this....

    Every index that is requested should be "justified".
    What this translates to:
    -Proof that the index is actually used must be produced, (or will be used...)  I don't know how many times I've been given DDL for an index someone thought their query would use and you pull a simple explain plan and find out otherwise.
    -The cost to support the index should be calculated-  "What will it cost to updates, inserts and deletes that are performed against this object in relation to the savings to have this index in place to the business?"
    -If it's a partitioned object, what kind of index are you requesting and if it's a global index, you had better be sacrificing a small animal to prove to me the overhead in management to my team is worthy of it... :)

    Reviewing and auditing index usage in a mature production environment is rarely a waste of time.  The task once allowed me to drop 30% of the indexes in an environment and re-allocate over 1TB in space back to the system.

    Kellyn Pot'Vin
    Sr. Database Administrator and Developer
    dbakevlar.com

    ________________________________
    From: "Lange, Kevin G"
    To: ORACLE-L
    Sent: Thursday, August 11, 2011 3:22 PM
    Subject: RE: How many is too many

    That is a perfectly stated response.  Too many is when
    adding them starts to hurt performance.  I will not tell you how many one
    of our tables has because you would call me nuts.

    ________________________________
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Morten
    Egan
    Sent: Thursday, August 11, 2011 4:18 PM
    To: veeeraman_at_gmail.com
    Cc: ORACLE-L
    Subject: Re: How many is
    too many

    The number that is considered a no-no, is the number where things
    start to go unacceptably slow :)

    Joking aside, if your program does what
    it is supposed to do, in an acceptable time, then it really doesn't
    matter.

    2011/8/11 Ram Raman

    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.
    --
    Regards,
    Morten Egan
    http://www.dbping.com

    This e-mail, including attachments, may include confidential and/or
    proprietary information, and may be used only by the person or entity
    to which it is addressed. If the reader of this e-mail is not the intended
    recipient or his or her authorized agent, the reader is hereby notified
    that any dissemination, distribution or copying of this e-mail is
    prohibited. If you have received this e-mail in error, please notify the
    sender by replying to this message and delete this e-mail immediately.

    --
    http://www.freelists.org/webpage/oracle-l
  • Howard Latham at Aug 12, 2011 at 8:25 pm
    about 12 is too many. Lathams law
    On 11 August 2011 22:50, Kellyn Pot'vin wrote:

    I have to add this a bit to this....

    Every index that is requested should be "justified".
    What this translates to:
    -Proof that the index is actually used must be produced, (or will be
    used...) I don't know how many times I've been given DDL for an index
    someone thought their query would use and you pull a simple explain plan and
    find out otherwise.
    -The cost to support the index should be calculated- "What will it cost to
    updates, inserts and deletes that are performed against this object in
    relation to the savings to have this index in place to the business?"
    -If it's a partitioned object, what kind of index are you requesting and if
    it's a global index, you had better be sacrificing a small animal to prove
    to me the overhead in management to my team is worthy of it... :)

    Reviewing and auditing index usage in a mature production environment is
    rarely a waste of time. The task once allowed me to drop 30% of the indexes
    in an environment and re-allocate over 1TB in space back to the system.

    Kellyn Pot'Vin
    Sr. Database Administrator and Developer
    dbakevlar.com

    ------------------------------
    *From:* "Lange, Kevin G"
    *To:* ORACLE-L
    *Sent:* Thursday, August 11, 2011 3:22 PM
    *Subject:* RE: How many is too many

    That is a perfectly stated response. Too many is when adding them starts
    to hurt performance. I will not tell you how many one of our tables has
    because you would call me nuts.

    ------------------------------
    *From:* oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@freelists.org] *On Behalf Of *Morten Egan
    *Sent:* Thursday, August 11, 2011 4:18 PM
    *To:* veeeraman_at_gmail.com
    *Cc:* ORACLE-L
    *Subject:* Re: How many is too many

    The number that is considered a no-no, is the number where things start to
    go unacceptably slow :)

    Joking aside, if your program does what it is supposed to do, in an
    acceptable time, then it really doesn't matter.

    2011/8/11 Ram Raman

    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.




    --
    Regards,
    Morten Egan
    http://www.dbping.com


    This e-mail, including attachments, may include confidential and/or
    proprietary information, and may be used only by the person or entity
    to which it is addressed. If the reader of this e-mail is not the intended
    recipient or his or her authorized agent, the reader is hereby notified
    that any dissemination, distribution or copying of this e-mail is
    prohibited. If you have received this e-mail in error, please notify the
    sender by replying to this message and delete this e-mail immediately.

    --
    Howard A. Latham

    Sent from my Nokia N97

    --
    http://www.freelists.org/webpage/oracle-l
  • Greg Rahn at Aug 12, 2011 at 12:03 am
    Sounds like the "index death spiral" to me.
    My simple sanity check: when the total number of columns indexed is greater
    than the number of columns in the table, there is likely a design or tuning
    problem. Often times, even before then...
    On Thu, Aug 11, 2011 at 1:45 PM, Ram Raman wrote:

    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.
    --
    Regards,
    Greg Rahn
    http://structureddata.org

    --
    http://www.freelists.org/webpage/oracle-l
  • Ram Raman at Aug 12, 2011 at 3:14 am
    Rule of thumb! Thanks for the response Greg. Does this apply to Data
    warehouse or OLTP applications or both?
    On Thu, Aug 11, 2011 at 7:03 PM, Greg Rahn wrote:

    Sounds like the "index death spiral" to me.
    My simple sanity check: when the total number of columns indexed is
    greater than the number of columns in the table, there is likely a design or
    tuning problem. Often times, even before then...

    On Thu, Aug 11, 2011 at 1:45 PM, Ram Raman wrote:

    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,
  • Greg Rahn at Aug 12, 2011 at 5:47 am
    For OLTP, adding indexes is a known quantity (in general). There is a known
    and finite number of queries so adding indexes is about limiting the number
    of blocks visited.

    For DW, adding indexes in this manner (12 indexes on a table, etc.), is the
    beginning of the end. Data loads suffer and since query workloads are
    frequently unknown (ad-hoc), DBAs often misapply OLTP techniques - adding
    indexes for each problem query they see. Seems people forget why databases
    have indexes -- as a manner to efficiently access *a small number of rows.*
    When indexes are used as a means to reduce IO, it is then being mis-applied
    because the platform is not appropriately sized and/or correct
    DW technologies are not being applied (e.g. parallel execution + table
    scans) for *a large number of rows* problem.

    In any case, one has to start asking what good reasoning there is for 12
    indexes. That just shouts bad design and/or inappropriate
    tuning methodologies. Start asking why 5
    times<http://en.wikipedia.org/wiki/5_Whys>
    .
    On Thu, Aug 11, 2011 at 8:14 PM, Ram Raman wrote:


    Rule of thumb! Thanks for the response Greg. Does this apply to Data
    warehouse or OLTP applications or both?
    On Thu, Aug 11, 2011 at 7:03 PM, Greg Rahn wrote:

    Sounds like the "index death spiral" to me.
    My simple sanity check: when the total number of columns indexed is
    greater than the number of columns in the table, there is likely a design or
    tuning problem. Often times, even before then...
    --
    Regards,
    Greg Rahn
    http://structureddata.org

    --
    http://www.freelists.org/webpage/oracle-l
  • Michael Dinh at Aug 12, 2011 at 12:43 pm
    Why not monitor the indexes to see if they are being used?

    Sometimes indexes are created for a query that is only executed once in a blue moon.

    If indexes are not used in 1,2,3,5 months, then drop them? You need to determine the duration?

    For indexes not used, mark them as invisible first. If no one screams, then drop them.

    Indexes are labeled with PK, UK, FK do not get dropped.

    When troubleshooting performance issues and I have to add an index, I use $ versus _ to indicate that it was added by me for this reason, i.e. IX$TAB$COL versus IX_TAB_COL

    From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] On Behalf Of Greg Rahn [greg_at_structureddata.org]
    Sent: Thursday, August 11, 2011 10:47 PM
    To: oracle-l@freelists.org
    Subject: Re: How many is too many

    For OLTP, adding indexes is a known quantity (in general). There is a known and finite number of queries so adding indexes is about limiting the number of blocks visited.

    For DW, adding indexes in this manner (12 indexes on a table, etc.), is the beginning of the end. Data loads suffer and since query workloads are frequently unknown (ad-hoc), DBAs often misapply OLTP techniques - adding indexes for each problem query they see. Seems people forget why databases have indexes -- as a manner to efficiently access a small number of rows. When indexes are used as a means to reduce IO, it is then being mis-applied because the platform is not appropriately sized and/or correct DW technologies are not being applied (e.g. parallel execution + table scans) for a large number of rows problem.

    In any case, one has to start asking what good reasoning there is for 12 indexes. That just shouts bad design and/or inappropriate tuning methodologies. Start asking why 5 times<http://en.wikipedia.org/wiki/5_Whys>.

    On Thu, Aug 11, 2011 at 8:14 PM, Ram Raman > wrote:

    Rule of thumb! Thanks for the response Greg. Does this apply to Data warehouse or OLTP applications or both?

    On Thu, Aug 11, 2011 at 7:03 PM, Greg Rahn > wrote:
    Sounds like the "index death spiral" to me.
    My simple sanity check: when the total number of columns indexed is greater than the number of columns in the table, there is likely a design or tuning problem. Often times, even before then...
  • David Fitzjarrell at Aug 12, 2011 at 2:58 pm
    I've found a few cases over the years where an index wasn't 'used' (as in accessed) but its statistics WERE used by the optimizer and when the index was dropped the pl;ans changed for the worse (it's not common but it CAN happen):

    http://oratips-ddf.blogspot.com/2008/04/to-b-or-not-to-b.html

    David Fitzjarrell

    From: Michael Dinh
    To: "greg_at_structureddata.org"; "oracle-l@freelists.org"
    Sent: Friday, August 12, 2011 5:43 AM
    Subject: RE: How many is too many

    Why not monitor the indexes to see if they are being used?

    Sometimes indexes are created for a query that is only executed once in a blue moon.

    If indexes are not used in 1,2,3,5 months, then drop them? You need to determine the duration?

    For indexes not used, mark them as invisible first. If no one screams, then drop them.

    Indexes are labeled with PK, UK, FK do not get dropped.

    When troubleshooting performance issues and I have to add an index, I use $ versus _ to indicate that it was added by me for this reason, i.e. IX$TAB$COL versus IX_TAB_COL
    ________________________________________
    From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] On Behalf Of Greg Rahn [greg@structureddata.org]
    Sent: Thursday, August 11, 2011 10:47 PM
    To: oracle-l@freelists.org
    Subject: Re: How many is too many

    For OLTP, adding indexes is a known quantity (in general).  There is a known and finite number of queries so adding indexes is about limiting the number of blocks visited.

    For DW, adding indexes in this manner (12 indexes on a table, etc.), is the beginning of the end.  Data loads suffer and since query workloads are frequently unknown (ad-hoc), DBAs often misapply OLTP techniques - adding indexes for each problem query they see.  Seems people forget why databases have indexes -- as a manner to efficiently access a small number of rows.  When indexes are used as a means to reduce IO, it is then being mis-applied because the platform is not appropriately sized and/or correct DW technologies are not being applied (e.g. parallel execution + table scans) for a large number of rows problem.

    In any case, one has to start asking what good reasoning there is for 12 indexes.  That just shouts bad design and/or inappropriate tuning methodologies. Start asking why 5 times<http://en.wikipedia.org/wiki/5_Whys>.

    On Thu, Aug 11, 2011 at 8:14 PM, Ram Raman > wrote:

    Rule of thumb!  Thanks for the response Greg. Does this apply to Data warehouse or OLTP applications or both?

    On Thu, Aug 11, 2011 at 7:03 PM, Greg Rahn > wrote:
    Sounds like the "index death spiral" to me.
    My simple sanity check:  when the total number of columns indexed is greater than the number of columns in the table, there is likely a design or tuning problem.  Often times, even before then...
  • Kevin Closson at Aug 12, 2011 at 4:48 pm
    What about parallel execution + index scan? If a query only needs 12 of 100 columns then it's sort of columnar in that case.

    I do, of course, agree on the damage to load times but it is conceivable that drop, load, index could be possible particularly when one knows partitioning.

    We shouldn't forget that Oracle had the market share of DW before Exadata. Someone, somewhere worked out how to get indexes to work for DW. I agree that most people usually get it wrong though and I agree that working out I/O is a smart thing to do.  Most of us still Party Like It's 1999 thinking that high-performance, high-bandwidth I/O is some holy grail never to be achieved.

    A single 3.5" 15K RPM SAS/FC  drive can produce 200+ MB/s streaming. Start there, add drives and scale the plumbing. It's not  rocket science.

    P.S., Are you still buying the beer next time Greg? :-)

    ________________________________
    From: Greg Rahn
    To: oracle-l@freelists.org
    Sent: Thursday, August 11, 2011 10:47 PM
    Subject: Re: How many is too many

    For OLTP, adding indexes is a known quantity (in general).  There is a known and finite number of queries so adding indexes is about limiting the number of blocks visited.

    For DW, adding indexes in this manner (12 indexes on a table, etc.), is the beginning of the end.  Data loads suffer and since query workloads are frequently unknown (ad-hoc), DBAs often misapply OLTP techniques - adding indexes for each problem query they see.  Seems people forget why databases have indexes -- as a manner to efficiently access a small number of rows.  When indexes are used as a means to reduce IO, it is then being mis-applied because the platform is not appropriately sized and/or correct DW technologies are not being applied (e.g. parallel execution + table scans) for a large number of rows problem.

    In any case, one has to start asking what good reasoning there is for 12 indexes.  That just shouts bad design and/or inappropriate tuning methodologies. Start asking why 5 times.

    On Thu, Aug 11, 2011 at 8:14 PM, Ram Raman wrote:

    Rule of thumb!  Thanks for the response Greg. Does this apply to Data warehouse or OLTP applications or both?

    On Thu, Aug 11, 2011 at 7:03 PM, Greg Rahn wrote:

    Sounds like the "index death spiral" to me.
    My simple sanity check:  when the total number of columns indexed is greater than the number of columns in the table, there is likely a design or tuning problem.  Often times, even before then...
    -- Regards,
    Greg Rahn
    http://structureddata.org
  • Greg Rahn at Aug 12, 2011 at 5:29 pm
    Using PX with an index FFS could be a viable solution for a "skinny" table
    scan. The big problem that I've seen is when its not a multi-block read
    FFS, it's index -> table by rowid, which results in single block reads and
    that is very inefficient for a large amount of rows/data via direct path
    reads from disk.

    Sure - I'm still buying beers. [?][?][?][?][?][?]
    On Fri, Aug 12, 2011 at 9:48 AM, Kevin Closson wrote:

    What about parallel execution + index scan? If a query only needs 12 of
    100 columns then it's sort of columnar in that case.

    I do, of course, agree on the damage to load times but it is conceivable
    that drop, load, index could be possible particularly when one knows
    partitioning.

    We shouldn't forget that Oracle had the market share of DW before Exadata.
    Someone, somewhere worked out how to get indexes to work for DW. I agree
    that most people usually get it wrong though and I agree that working out
    I/O is a smart thing to do. Most of us still Party Like It's 1999 thinking
    that high-performance, high-bandwidth I/O is some holy grail never to be
    achieved.

    A single 3.5" 15K RPM SAS/FC drive can produce 200+ MB/s streaming. Start
    there, add drives and scale the plumbing. It's not rocket science.

    P.S., Are you still buying the beer next time Greg? :-)

    ------------------------------
    *From:* Greg Rahn

    *To:* oracle-l@freelists.org
    *Sent:* Thursday, August 11, 2011 10:47 PM
    *Subject:* Re: How many is too many

    For OLTP, adding indexes is a known quantity (in general). There is a
    known and finite number of queries so adding indexes is about limiting the
    number of blocks visited.

    For DW, adding indexes in this manner (12 indexes on a table, etc.), is the
    beginning of the end. Data loads suffer and since query workloads are
    frequently unknown (ad-hoc), DBAs often misapply OLTP techniques - adding
    indexes for each problem query they see. Seems people forget why databases
    have indexes -- as a manner to efficiently access *a small number of rows.
    * When indexes are used as a means to reduce IO, it is then being
    mis-applied because the platform is not appropriately sized and/or correct
    DW technologies are not being applied (e.g. parallel execution + table
    scans) for *a large number of rows* problem.

    In any case, one has to start asking what good reasoning there is for 12
    indexes. That just shouts bad design and/or inappropriate
    tuning methodologies. Start asking why 5 times<http://en.wikipedia.org/wiki/5_Whys>
    .

    On Thu, Aug 11, 2011 at 8:14 PM, Ram Raman wrote:


    Rule of thumb! Thanks for the response Greg. Does this apply to Data
    warehouse or OLTP applications or both?

    On Thu, Aug 11, 2011 at 7:03 PM, Greg Rahn wrote:

    Sounds like the "index death spiral" to me.
    My simple sanity check: when the total number of columns indexed is
    greater than the number of columns in the table, there is likely a design or
    tuning problem. Often times, even before then...


    --
    Regards,
    Greg Rahn
    http://structureddata.org

    --
    Regards,
    Greg Rahn
    http://structureddata.org

    --
    http://www.freelists.org/webpage/oracle-l
  • Kevin Closson at Aug 12, 2011 at 5:51 pm
    Yes, no doubt. If you have to dart off for a row it blows.

    ________________________________
    From: Greg Rahn
    To: Kevin Closson
    Cc: "oracle-l@freelists.org"
    Sent: Friday, August 12, 2011 10:29 AM
    Subject: Re: How many is too many

    Using PX with an index FFS could be a viable solution for a "skinny" table scan.  The big problem that I've seen is when its not a multi-block read FFS, it's index -> table by rowid, which results in single block reads and that is very inefficient for a large amount of rows/data via direct path reads from disk.

    Sure - I'm still buying beers.

    On Fri, Aug 12, 2011 at 9:48 AM, Kevin Closson wrote:

    What about parallel execution + index scan? If a query only needs 12 of 100 columns then it's sort of columnar in that case.


    I do, of course, agree on the damage to load times but it is conceivable that drop, load, index could be possible particularly when one knows partitioning.



    We shouldn't forget that Oracle had the market share of DW before Exadata. Someone, somewhere worked out how to get indexes to work for DW. I agree that most people usually get it wrong though and I agree that working out I/O is a smart thing to do.  Most of us still Party Like It's 1999 thinking that high-performance, high-bandwidth I/O is some holy grail never to be achieved.



    A single 3.5" 15K RPM SAS/FC  drive can produce 200+ MB/s streaming. Start there, add drives and scale the plumbing. It's not  rocket science.


    P.S., Are you still buying the beer next time Greg? :-)




    ________________________________
    From: Greg Rahn

    To: oracle-l@freelists.org
    Sent: Thursday, August 11, 2011 10:47 PM
    Subject: Re: How many is too many



    For OLTP, adding indexes is a known quantity (in general).  There is a known and finite number of queries so adding indexes is about limiting the number of blocks visited.


    For DW, adding indexes in this manner (12 indexes on a table, etc.), is the beginning of the end.  Data loads suffer and since query workloads are frequently unknown (ad-hoc), DBAs often misapply OLTP techniques - adding indexes for each problem query they see.  Seems people forget why databases have indexes -- as a manner to efficiently access a small number of rows.  When indexes are used as a means to reduce IO, it is then being mis-applied because the platform is not appropriately sized and/or correct DW technologies are not being applied (e.g. parallel execution + table scans) for a large number of rows problem.


    In any case, one has to start asking what good reasoning there is for 12 indexes.  That just shouts bad design and/or inappropriate tuning methodologies. Start asking why 5 times.


    On Thu, Aug 11, 2011 at 8:14 PM, Ram Raman wrote:

    Rule of thumb!  Thanks for the response Greg. Does this apply to Data warehouse or OLTP applications or both?

    On Thu, Aug 11, 2011 at 7:03 PM, Greg Rahn wrote:

    Sounds like the "index death spiral" to me.
    My simple sanity check:  when the total number of columns indexed is greater than the number of columns in the table, there is likely a design or tuning problem.  Often times, even before then...
    -- Regards,
    Greg Rahn
    http://structureddata.org

    --
    Regards,
    Greg Rahn
    http://structureddata.org

    --
    http://www.freelists.org/webpage/oracle-l
  • Chitale, Hemant Krishnarao at Aug 12, 2011 at 3:17 am
    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
    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.
  • Cary Millsap at Aug 12, 2011 at 4:18 am
    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@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-l
  • Toon Koppelaars at Aug 12, 2011 at 5:23 am
    Tables with that many indexes, to me, are usually a strong sign that the
    table holds multiple 'entity types', and in fact should have been designed
    as multiple tables. I bet that there are a lot of NULL-able columns in that
    table too, right?

    The fact that you mention 'third party product', by the way, is a sign in
    that direction too :-)
    On Thu, Aug 11, 2011 at 10:45 PM, Ram Raman wrote:

    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.
    --
    Toon Koppelaars
    RuleGen BV
    Toon.Koppelaars_at_RuleGen.com
    www.RuleGen.com
    TheHelsinkiDeclaration.blogspot.com

    (co)Author: "Applied Mathematics for Database Professionals"
    www.rulegen.com/am4dp-backcover-text

    --
    http://www.freelists.org/webpage/oracle-l
  • Ram Raman at Aug 12, 2011 at 4:00 pm
    Toon

    You are correct. There are about 70,000 tables owned by the application
    schema. I see one Primary key (not sure how it got there :)) and one unique
    key constraint owned by that user with no foreign key at all.

    On Fri, Aug 12, 2011 at 12:23 AM, Toon Koppelaars <
    toon.koppelaars_at_rulegen.com> wrote:
    Tables with that many indexes, to me, are usually a strong sign that the
    table holds multiple 'entity types', and in fact should have been designed
    as multiple tables. I bet that there are a lot of NULL-able columns in that
    table too, right?

    The fact that you mention 'third party product', by the way, is a sign in
    that direction too :-)


    On Thu, Aug 11, 2011 at 10:45 PM, Ram Raman wrote:

    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.


    --
    Toon Koppelaars
    RuleGen BV
    Toon.Koppelaars_at_RuleGen.com
    www.RuleGen.com <http://www.rulegen.com/>
    TheHelsinkiDeclaration.blogspot.com<http://thehelsinkideclaration.blogspot.com/>

    (co)Author: "Applied Mathematics for Database Professionals"
    www.rulegen.com/am4dp-backcover-text
    --
    http://www.freelists.org/webpage/oracle-l
  • Pavel at Aug 15, 2011 at 7:17 am
    Hi
    Could you please tell me what system have 70000 tables?(retail or something
    else?)

    Regards,
    Pavel.

    2011/8/12 Ram Raman
    Toon

    You are correct. There are about 70,000 tables owned by the application
    schema. I see one Primary key (not sure how it got there :)) and one unique
    key constraint owned by that user with no foreign key at all.



    On Fri, Aug 12, 2011 at 12:23 AM, Toon Koppelaars <
    toon.koppelaars_at_rulegen.com> wrote:
    Tables with that many indexes, to me, are usually a strong sign that the
    table holds multiple 'entity types', and in fact should have been designed
    as multiple tables. I bet that there are a lot of NULL-able columns in that
    table too, right?

    The fact that you mention 'third party product', by the way, is a sign in
    that direction too :-)


    On Thu, Aug 11, 2011 at 10:45 PM, Ram Raman wrote:

    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.


    --
    Toon Koppelaars
    RuleGen BV
    Toon.Koppelaars_at_RuleGen.com
    www.RuleGen.com <http://www.rulegen.com/>
    TheHelsinkiDeclaration.blogspot.com<http://thehelsinkideclaration.blogspot.com/>

    (co)Author: "Applied Mathematics for Database Professionals"
    www.rulegen.com/am4dp-backcover-text
    --
    http://www.freelists.org/webpage/oracle-l
  • Niall Litchfield at Aug 15, 2011 at 7:38 am
    That's a relatively common table count for ERP ( SAP, EBIZ, peoplesoft &c)

    On 15 Aug 2011 08:19, "Pavel" wrote:

    Hi
    Could you please tell me what system have 70000 tables?(retail or something
    else?)

    Regards,
    Pavel.

    2011/8/12 Ram Raman
    Toon

    You are correct. There are about 70,000 ...
    --
    http://www.freelists.org/webpage/oracle-l
  • Pavel at Aug 15, 2011 at 7:43 am
    Hi
    Thanks for reply, Niall. And what percent of usage these tables (in common)?
    Didn't work with this count of tables yet . :)

    Regards,
    Pavel.

    2011/8/15 Niall Litchfield
    That's a relatively common table count for ERP ( SAP, EBIZ, peoplesoft &c)

    On 15 Aug 2011 08:19, "Pavel" wrote:

    Hi
    Could you please tell me what system have 70000 tables?(retail or something
    else?)

    Regards,
    Pavel.



    2011/8/12 Ram Raman
    Toon

    You are correct. There are about 70,000 ...
    --
    http://www.freelists.org/webpage/oracle-l
  • Guillermo Alan Bort at Aug 15, 2011 at 1:57 pm
    wow... I'd like to see a memory report and a couple of AWR reports... I
    think library cache will either be very big, have a high miss rate or
    perhaps there are 70k objects but only a small (about 1%?) are used.

    this is why I hate "canned" software...
    Alan.-
    On Mon, Aug 15, 2011 at 4:43 AM, Pavel wrote:

    Hi
    Thanks for reply, Niall. And what percent of usage these tables (in
    common)? Didn't work with this count of tables yet . :)

    Regards,
    Pavel.


    2011/8/15 Niall Litchfield
    That's a relatively common table count for ERP ( SAP, EBIZ, peoplesoft &c)

    On 15 Aug 2011 08:19, "Pavel" wrote:

    Hi
    Could you please tell me what system have 70000 tables?(retail or
    something else?)

    Regards,
    Pavel.



    2011/8/12 Ram Raman
    Toon

    You are correct. There are about 70,000 ...
    --
    http://www.freelists.org/webpage/oracle-l
  • Niall Litchfield at Aug 15, 2011 at 2:28 pm
    mostly only a small percentage get used. Eg we have an apps client with 22k
    tables, but only 418 tables have more than 1000 rows in them - very poor
    proxy for commonly used tables.

    On Mon, Aug 15, 2011 at 2:57 PM, Guillermo Alan Bort
    wrote:
    wow... I'd like to see a memory report and a couple of AWR reports... I
    think library cache will either be very big, have a high miss rate or
    perhaps there are 70k objects but only a small (about 1%?) are used.

    this is why I hate "canned" software...
    Alan.-


    On Mon, Aug 15, 2011 at 4:43 AM, Pavel wrote:

    Hi
    Thanks for reply, Niall. And what percent of usage these tables (in
    common)? Didn't work with this count of tables yet . :)

    Regards,
    Pavel.


    2011/8/15 Niall Litchfield
    That's a relatively common table count for ERP ( SAP, EBIZ, peoplesoft
    &c)

    On 15 Aug 2011 08:19, "Pavel" wrote:

    Hi
    Could you please tell me what system have 70000 tables?(retail or
    something else?)

    Regards,
    Pavel.



    2011/8/12 Ram Raman
    Toon

    You are correct. There are about 70,000 ...
    --
    Niall Litchfield
    Oracle DBA
    http://www.orawin.info

    --
    http://www.freelists.org/webpage/oracle-l
  • Taylor, Chris David at Aug 12, 2011 at 11:58 am
    Sometimes, a "good" index causes exceptionally bad performance. We have a situation where if I add, what I "think" is, a good index on paper, the execution plan changes in such a way as to make subsequent queries perform horribly. The physical I/O looks good, the cost looks good, but the logical I/O goes berserk.

    Chris Taylor
    Sr. Oracle DBA
    Ingram Barge Company
    Nashville, TN 37205
    Office: 615-517-3355
    Cell: 615-663-1673
    Email: chris.taylor_at_ingrambarge.com

    CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.

    From: oracle-l-bounce_at_freelists.org On Behalf Of Ram Raman
    Sent: Thursday, August 11, 2011 3:45 PM
    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.
  • Walker, Jed S at Aug 12, 2011 at 7:42 pm
    I don't think it is so much a number as "is the index used". You can use the "alter index [no]monitoring" command to turn on monitoring and then watch v$object_usage to see which are used. Make sure you observe over a good time period to ensure you don't miss any reports that are not run too often.

    From: oracle-l-bounce_at_freelists.org On Behalf Of Ram Raman
    Sent: Thursday, August 11, 2011 2:45 PM
    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.
  • Kellyn Pot'vin at Aug 12, 2011 at 7:48 pm
    When stating an index audit, monitoring was what I was referring to, but in 10g, you have to get a lock on the object to turn on the monitoring...This can be the biggest hurdle, more so than the overhead of the index or monitoring for usage.
    Kellyn

    ________________________________
    From: "Walker, Jed S"
    To: "veeeraman_at_gmail.com"; ORACLE-L
    Sent: Friday, August 12, 2011 1:42 PM
    Subject: RE: How many is too many

    I don’t think it is so much a number as “is the index used”. You can use the “alter index [no]monitoring” command to turn on monitoring and then watch v$object_usage to see which are used. Make sure you observe over a good time period to ensure you don’t miss any reports that are not run too often.
    Â
    From:oracle-l-bounce_at_freelists.org On Behalf Of Ram Raman
    Sent: Thursday, August 11, 2011 2:45 PM
    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.
  • Walker, Jed S at Aug 12, 2011 at 7:54 pm
    Yes, but a simple loop I’ve found usually results in the lock being acquired after a bit (but not always).

    I just monitored one of our oltp systems for several months and found 150+ indexes of 500 never used. Developers often create “what they think” is needed rather than what is needed – getting back to your earlier point about “sacrificing a small animal”

    From: Kellyn Pot'vin
    Sent: Friday, August 12, 2011 1:48 PM
    To: Walker, Jed S; veeeraman_at_gmail.com; ORACLE-L
    Subject: Re: How many is too many

    When stating an index audit, monitoring was what I was referring to, but in 10g, you have to get a lock on the object to turn on the monitoring...This can be the biggest hurdle, more so than the overhead of the index or monitoring for usage.
    Kellyn

    From: "Walker, Jed S"
    To: "veeeraman_at_gmail.com"; ORACLE-L
    Sent: Friday, August 12, 2011 1:42 PM
    Subject: RE: How many is too many
    I don’t think it is so much a number as “is the index used”. You can use the “alter index [no]monitoring” command to turn on monitoring and then watch v$object_usage to see which are used. Make sure you observe over a good time period to ensure you don’t miss any reports that are not run too often.

    From: oracle-l-bounce_at_freelists.org On Behalf Of Ram Raman
    Sent: Thursday, August 11, 2011 2:45 PM
    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.
  • Ram Raman at Aug 12, 2011 at 8:37 pm
    Jed,

    Thanks for the reply.

    Can you please clarify "Yes, but a simple loop I�ve found usually results in
    the lock being acquired after a bit (but not always)."****

    Thanks.
    On Fri, Aug 12, 2011 at 2:54 PM, Walker, Jed S wrote:

    Yes, but a simple loop I�ve found usually results in the lock being
    acquired after a bit (but not always).****

    ** **

    I just monitored one of our oltp systems for several months and found 150+
    indexes of 500 never used. Developers often create �what they think� is
    needed rather than what is needed � getting back to your earlier point about
    �sacrificing a small animal� ****

    ** **

    *From:* Kellyn Pot'vin
    *Sent:* Friday, August 12, 2011 1:48 PM
    *To:* Walker, Jed S; veeeraman_at_gmail.com; ORACLE-L
    *Subject:* Re: How many is too many****

    ** **

    When stating an index audit, monitoring was what I was referring to, but in
    10g, you have to get a lock on the object to turn on the monitoring...This
    can be the biggest hurdle, more so than the overhead of the index or
    monitoring for usage.****

    Kellyn****

    ** **
    ------------------------------

    *From:* "Walker, Jed S"
    *To:* "veeeraman_at_gmail.com"; ORACLE-L <
    oracle-l@freelists.org>
    *Sent:* Friday, August 12, 2011 1:42 PM
    *Subject:* RE: How many is too many****

    I don�t think it is so much a number as �is the index used�. You can use
    the �alter index [no]monitoring� command to turn on monitoring and then
    watch v$object_usage to see which are used. Make sure you observe over a
    good time period to ensure you don�t miss any reports that are not run too
    often.****

    ****

    *From:* oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@freelists.org] *On Behalf Of *Ram Raman
    *Sent:* Thursday, August 11, 2011 2:45 PM
    *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.****

    ** **
    --
    http://www.freelists.org/webpage/oracle-l
  • Walker, Jed S at Aug 12, 2011 at 8:55 pm
    Sometimes when you try to turn on index monitoring you'll get a NOWAIT trying to get the lock to set the monitoring (I think Kellyn might have indicated this isn't the case in 11g). Chances are if you try hard enough you'll catch it at the right moment to get the lock and make the changes. So, you can create a simple loop something like the following (excuse the pseudo-code, I don't have an example with me right now), either shell or pl/sql. You might think because it is busy and you can't get the lock it is being used, but that could just be index maintenance (anyone correct me if I'm wrong there)

    Changed:=false

    Begin
    Alter index example_idx monitoring usage;
    Changed:=true;
    Exception
    If then null;
    End;

    From: Ram Raman
    Sent: Friday, August 12, 2011 2:37 PM
    To: Walker, Jed S
    Cc: Kellyn Pot'vin; ORACLE-L
    Subject: Re: How many is too many

    Jed,

    Thanks for the reply.

    Can you please clarify "Yes, but a simple loop I've found usually results in the lock being acquired after a bit (but not always)."

    Thanks.
    On Fri, Aug 12, 2011 at 2:54 PM, Walker, Jed S > wrote:
    Yes, but a simple loop I've found usually results in the lock being acquired after a bit (but not always).

    I just monitored one of our oltp systems for several months and found 150+ indexes of 500 never used. Developers often create "what they think" is needed rather than what is needed - getting back to your earlier point about "sacrificing a small animal"

    From: Kellyn Pot'vin [mailto:kellyn.potvin_at_ymail.com]
    Sent: Friday, August 12, 2011 1:48 PM
    To: Walker, Jed S; veeeraman_at_gmail.com; ORACLE-L
    Subject: Re: How many is too many

    When stating an index audit, monitoring was what I was referring to, but in 10g, you have to get a lock on the object to turn on the monitoring...This can be the biggest hurdle, more so than the overhead of the index or monitoring for usage.
    Kellyn

    From: "Walker, Jed S" >
    To: "veeeraman_at_gmail.com" >; ORACLE-L >
    Sent: Friday, August 12, 2011 1:42 PM
    Subject: RE: How many is too many
    I don't think it is so much a number as "is the index used". You can use the "alter index [no]monitoring" command to turn on monitoring and then watch v$object_usage to see which are used. Make sure you observe over a good time period to ensure you don't miss any reports that are not run too often.

    From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ram Raman
    Sent: Thursday, August 11, 2011 2:45 PM
    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.

Related Discussions

People

Translate

site design / logo © 2022 Grokbase