Re: Single-column vs composite index
Date: Tue, 1 Dec 2015 14:13:37 -0700
Message-ID: <CAJzM94BTY43bLTeOs5BUOwE3pTCjUmpkhU5fbz3aBj1eSad5mw_at_mail.gmail.com>
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. -- http://www.freelists.org/webpage/oracle-lReceived on Tue Dec 01 2015 - 22:13:37 CET