Re: Single-column vs composite index

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Thu, 3 Dec 2015 14:32:46 -0600
Message-ID: <CAJvnOJYsWLSrm9+Xxh5zZmVroyTrAm3o=vKY6=VMGc=e+7CESA_at_mail.gmail.com>



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.'

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Dec 03 2015 - 21:32:46 CET

Original text of this message