RE: indexing
- From: wblanchard@xxxxxxxxxxxxxxx
- To: mwf@xxxxxxxx
- Date: Tue, 19 Feb 2013 12:20:28 -0600
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" <mwf@xxxxxxxx>
To: <Brian.Zelli@xxxxxxxxxxxxxxx>, "'oracle-l'"
<oracle-l@xxxxxxxxxxxxx>
Date: 02/19/2013 10:46 AM
Subject: RE: indexing
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Zelli, Brian
Sent: Tuesday, February 19, 2013 11:09 AM
To: oracle-l (oracle-l@xxxxxxxxxxxxx)
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
