Re: Single-column vs composite index

From: Stefan Koehler <contact_at_soocs.de>
Date: Tue, 1 Dec 2015 22:05:01 +0100 (CET)
Message-ID: <25780413.985829.1449003901213.JavaMail.open-xchange_at_app09.ox.hosteurope.de>



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.

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Dec 01 2015 - 22:05:01 CET

Original text of this message