Re: Single-column vs composite index
Date: Thu, 3 Dec 2015 14:25:07 -0700
Message-ID: <CAJzM94A6yNJt=oxBWLL0o-5rF+n+48_Y+SsY1Yrxj0Y5VBwF9A_at_mail.gmail.com>
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_at_gmail.com> 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_at_rsiz.com> 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_at_freelists.org [mailto:
>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Sandra Becker
>> *Sent:* Tuesday, December 01, 2015 4:14 PM
>> *To:* Stefan Koehler
>> *Cc:* andrew.kerber_at_gmail.com; 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_at_soocs.de> 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: _at_OracleSK
>>
>> > Andrew Kerber <andrew.kerber_at_gmail.com> hat am 1. Dezember 2015 um
>> 21:51 geschrieben:
>> >
>> > Hmm. I wonder if the people who thought of that policy somehow
>> thought that oracle would use multiple single column indexes at once on the
>> same
>> > table and condition? I suspect you will need to educate them on oracle
>> indexing strategies. I would start with your biggest hitter, and work from
>> > there. Most likely the person who implemented the policy has left, and
>> the reasoning behind it left with them
>> >
>> > On Tue, Dec 1, 2015 at 2:46 PM, Sandra Becker <sbecker6925_at_gmail.com
>> <mailto:sbecker6925_at_gmail.com> > wrote:
>> > > > Andrew,
>> > >
>> > > This is the first time I have encountered such a policy. They
>> also require the primary key of EVERY table be a sequence. Again, no
>> problem
>> > > with that policy. Put in place long before I came on board.
>> However, I see queries frequently are not done on a unique value using the
>> primary
>> > > key, but on a range on another column. That seems to be when the
>> other indexes come into play some of the time. I'm seeing some FTS on some
>> > > rather large tables, lots of disk I/O. Those are the queries I am
>> most interested in looking at right now.
>> > >
>> > > They were surprised here when I said I had never heard of a policy
>> like that before. Their position is that composite indexes are bad. They
>> > > certainly can be; I've seen that with poor design constructs. I've
>> also seen single-column indexes that resulted in tremendous amounts of I/O
>> > > that could have been avoided by using an appropriately formed
>> composite index. It varies. I was curious what others have experienced,
>> what they
>> > > look for when reviewing indexes.
>> > >
>> > > Thanks for the feedback.
>> > >
>> > > Sandy
>> > >
>>
>> > > On Tue, Dec 1, 2015 at 1:30 PM, Andrew Kerber <
>> andrew.kerber_at_gmail.com <mailto:andrew.kerber_at_gmail.com> > wrote:
>> > > > > > I have to say that I have never heard of a policy
>> that all indexes are single column. I suppose I could see it for primary
>> key
>> > > > > > indexes, when a sequence is always being used, and all
>> queries are done on the unique value, but I cant visualize how something
>> like
>> > > > > > that could be designed. It sounds like a policy written
>> by someone who does not understand how oracle indexes work.
>>
>>
>>
>>
>> --
>>
>> Sandy B.
>>
>
>
>
> --
> Andrew W. Kerber
>
> 'If at first you dont succeed, dont take up skydiving.'
>
-- Sandy B. -- http://www.freelists.org/webpage/oracle-lReceived on Thu Dec 03 2015 - 22:25:07 CET