RE: indexing

From: Mark W. Farnham <>
Date: Tue, 19 Feb 2013 14:16:38 -0500
Message-ID: <022301ce0ed5$a5234ca0$ef69e5e0$>

If you either examine cluster factors and do a little math to get an idea whether existing queries will be degraded, OR, create those invisible indexes and spin up a plan for the ones you're worried about you can make it more science than art without going all the way to RAT. I think of the process of index consolidation as not involving much creativity at all - mostly just paying attention to the column sets and orders and the numbers.

But you definitely "can't just create indexes without considering the impact the new index may have on current queries." Or even change or drop existing ones.

Sensible and objective reduction and improvement in index strategy is likely to correspond to a big improvement in your system's overall performance. Once in a while adding a new index is a big net win. I do think this is the ultimate in "it depends" on a case by case basis of testing the actuals, but I think it is all an objective analysis, not art.

Or else I'm a witch doctor and I didn't know it. My first art teacher said to the class: "If you can't make it good small, make it bigger. If that doesn't help, color it red."
Then he came by my desk, looked at my efforts, and said: "Mark, you should paint big red barns." He meant the real ones, not paintings.


-----Original Message-----

From: [] On Behalf Of Sent: Tuesday, February 19, 2013 1:20 PM To:
Cc:; 'oracle-l'; Subject: RE: indexing

I have this fight as well. I have JDE developers that want to make indexes every time they see a slow query (rather than fix the query). The problem with indexes is that it's much more of an art than a science. You can't just create indexes without considering the impact the new index may have on current queries. There are ways to test indexes including regression testing using RAT if you have it. You can also take a look at the queries going against that table by querying v$sqlarea and run some before and after tests to see if any of the current queries suffer from the new index.

WGB From: "Mark W. Farnham" <> To: <>, "'oracle-l'" <>
Date: 02/19/2013 10:46 AM

Subject:        RE: indexing
Sent by:

Oracle CAN handle many, many indexes.
However, it is rarely a good idea to have many, many indexes on a single table (revise sentence as required if you're using partitioning).

It is relatively expensive to maintain indexes, and very often many indexes created "ad hoc" by individual developers can be rationalized together into a few indexes that service all the desired queries very effectively with much less overhead.

You apparently have a political problem as well.

Suggestion: Pick a particular table or set of tables typically used together in queries and review the indexes for things such as common initial columns (especially proper subsets of columns in the same order) and then set up a demo that shows all queries run materially at the same efficiency with the fewer combined indexes and at substantially lower overhead for insert, update, and delete. (It *MAY* remain effective to retain some column sets that enforce uniqueness or a primary key, but Oracle can use a non-unique index to enforce uniqueness - the actual optimal "texture" that mitigates toward using precisely the column set for uniqueness control and another with the unique key plus some additional columns to avoid table reads entirely is a measurement, not a thumb rule.)

If the developers can be shown that appropriate combinations into fewer indexes does not noticeably affect their query efficiency (and it might reduce plan generation cost, having fewer access choices), and insert, update, and delete efficiency improve to boot, they are more likely to work together with you to do the smart thing.

Giving them a tool to easily show the columns in the existing indexes for a given table may help them avoid inventing new ones on the fly and possibly they can negotiable with each other a bit on being optimal, especially where columns flipped in order late in a concatenated index is all that differs.

Here is an example, which I call q_icol.sql:

set null ~
column owner format a12
column column_name format a30
column column_position format 90 hea CP
column blevel format 0 hea B
break on owner on table_name skip 1 on TT on index_name on IT on U on blevel select i.owner,

   decode(i.table_owner,i.owner,' ','*') X,    i.table_name,
   substr(i.table_type,1,2) TT,
   substr(i.index_type,1,2) IT,
   substr(i.uniqueness,1,1) U,


from dba_indexes i,dba_ind_columns ic
where i.table_owner = '&table_owner'
  and i.table_name in (&table_list)
  and i.owner       = ic.index_owner
  and i.index_name  = ic.index_name
  and i.table_owner = ic.table_owner
  and i.table_name  = ic.table_name

order by i.owner,i.table_name,i.index_name,ic.column_position;

-----Original Message-----

From: [] On Behalf Of Zelli, Brian
Sent: Tuesday, February 19, 2013 11:09 AM To: oracle-l ( Subject: indexing

I have developers who are able to create indexes thru their application builder. On one table they have 30 indexes. I've often said to them that they are creating too many but I get overruled by their manager. Are they creating too many? Can oracle handle many, many indexes? Is there a way
I can at least minimize any depreciation of performance if they are going to
be allowed to do this?

This email message may contain legally privileged and/or confidential information. If you are not the intended recipient(s), or the employee or agent responsible for the delivery of this message to the intended recipient(s), you are hereby notified that any disclosure, copying, distribution, or use of this email message is prohibited. If you have received this message in error, please notify the sender immediately by e-mail and delete this email message from your computer. Thank you.


Although this e-mail and any attachments are believed to be free of any virus or other defect which might affect any computer system, it is the responsibility of the recipient to check that it is virus-free and the sender accepts no responsibility or liability for any loss, injury, damage, cost or expense arising in any way from receipt or use thereof by the recipient.

The information contained in this electronic mail message is confidential information and intended only for the use of the individual or entity named above, and may be privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this transmission in error, please contact the sender immediately, delete this material from your computer and destroy all related paper media. Please note that the documents transmitted are not intended to be binding until a hard copy has been manually signed by all parties. Thank you.


-- Received on Tue Feb 19 2013 - 20:16:38 CET

Original text of this message