RE: indexing
Date: Tue, 19 Feb 2013 11:45:36 -0500
Message-ID: <014b01ce0ec0$8b4b4a40$a1e1dec0$_at_rsiz.com>
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,
i.index_name,
substr(i.index_type,1,2) IT,
substr(i.uniqueness,1,1) U,
i.blevel,
ic.column_name, ic.column_position, ic.descend
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: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Zelli, Brian
Sent: Tuesday, February 19, 2013 11:09 AM
To: oracle-l (oracle-l_at_freelists.org)
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?
ciao,
Brian
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.
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Feb 19 2013 - 17:45:36 CET