As if there aren't enough restrictions, they had to set up a completely
arbitrary one because of the incorrect belief that "*ALL* composite indexes
are bad". I've fought with that belief before and was able to prove it
ain't so. Caution will be used and team discussion before making any
changes in production, but it would be nice to get them to look at
possibilities. One of the reasons I was hired was to be pro-active and see
where improvements might be made.
As luck would have it, I've just been tasked with cloning this particular
database to our sandbox for some testing to take place 1st quarter. I will
then take "suspect" queries and play with some indexing scenarios in the
sandbox. I'll be sure to grab other queries that use the same index and
verify they don't break. Or attempt to at any rate. Once it hits
production, the results can surprise you. Love the feature where you can
hide indexes. Used it many times at my previous employer. Lots of good
info in this discussion.
Sandy
On Thu, Dec 3, 2015 at 1:32 PM, Andrew Kerber <andrew.kerber@xxxxxxxxx>
wrote:
Similar but related, it could be a legacy database that originated in a
system that did not allow concatenated indexes.
On Thu, Dec 3, 2015 at 1:58 PM, Mark W. Farnham <mwf@xxxxxxxx> wrote:
At the very least disallowing concatenated indexes as policy is wearing a
voluntary straight-jacket.
Everything about data model and index design should be as free from
restriction as possible but should consider operational overhead.
For a single example, you might demonstrate to them on a test system
where a frequent query of a small number of columns on a wide table can be
done completely from the index without dipping into the table at all
(making cluster factor irrelevant, by the way).
If some single column index is not a constraint definition and is never
the leading edge or used alone in a query, you might well save both
overhead and provide quicker and cheaper query response by adding the
column to an existing index (or a few if relevant) and dropping the single
column index. Your mileage will vary.
To me this sounds like a policy rooted in one of both of the following:
1) Database agnostic policy and some databases just don’t have
concatenated indexes
2) Someone when hog wild at some point in time and created excessive
indexes, particularly on hot oltp tables where this was a material nuisance
to insert, update, and delete performance.
Good luck. If anyone has a sane explanation of why this policy might be
good, I’m all ears.
mwf
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Sandra Becker
*Sent:* Tuesday, December 01, 2015 4:14 PM
*To:* Stefan Koehler
*Cc:* andrew.kerber@xxxxxxxxx; oracle-l
*Subject:* Re: Single-column vs composite index
Also valid points. For a few tables, that is definitely the scenario;
for others it isn't. It will definitely require more time to monitor and
evaluate before any changes are considered. Only looking at those queries
doing massive amounts of I/O and causing "concern" in the user community
right now.
Sandy
On Tue, Dec 1, 2015 at 2:05 PM, Stefan Koehler <contact@xxxxxxxx> wrote:
Hi guys,
as i previously mentioned it depends on the kind of application and
environment.
Just think about an app that allows dynamic queries with all possible
predicate combinations. It is impossible to create proper composite indexes
for
all these cases, but it is a valid approach to index each column and let
the optimizer work out the combinations (+ "B-tree to Bitmap Conversions").
They also can be used in joins.
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK
Andrew Kerber <andrew.kerber@xxxxxxxxx> hat am 1. Dezember 2015 um21:51 geschrieben:
thought that oracle would use multiple single column indexes at once on the
Hmm. I wonder if the people who thought of that policy somehow
same
table and condition? I suspect you will need to educate them on oracleindexing strategies. I would start with your biggest hitter, and work from
there. Most likely the person who implemented the policy has left, andthe reasoning behind it left with them
<mailto:sbecker6925@xxxxxxxxx> > wrote:
On Tue, Dec 1, 2015 at 2:46 PM, Sandra Becker <sbecker6925@xxxxxxxxx
> > Andrew,also require the primary key of EVERY table be a sequence. Again, no
This is the first time I have encountered such a policy. They
problem
However, I see queries frequently are not done on a unique value using thewith that policy. Put in place long before I came on board.
primary
other indexes come into play some of the time. I'm seeing some FTS on somekey, but on a range on another column. That seems to be when the
most interested in looking at right now.rather large tables, lots of disk I/O. Those are the queries I am
like that before. Their position is that composite indexes are bad. They
They were surprised here when I said I had never heard of a policy
also seen single-column indexes that resulted in tremendous amounts of I/Ocertainly can be; I've seen that with poor design constructs. I've
composite index. It varies. I was curious what others have experienced,that could have been avoided by using an appropriately formed
what they
look for when reviewing indexes.
Thanks for the feedback.
Sandy
andrew.kerber@xxxxxxxxx <mailto:andrew.kerber@xxxxxxxxx> > wrote:On Tue, Dec 1, 2015 at 1:30 PM, Andrew Kerber <
that all indexes are single column. I suppose I could see it for primary> > > I have to say that I have never heard of a policy
key
queries are done on the unique value, but I cant visualize how something> > > indexes, when a sequence is always being used, and all
like
by someone who does not understand how oracle indexes work.> > > that could be designed. It sounds like a policy written
--
Sandy B.
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
