Re: Single-column vs composite index

From: Sandra Becker <sbecker6925_at_gmail.com>
Date: Tue, 1 Dec 2015 13:46:33 -0700
Message-ID: <CAJzM94DUEwr51pbWBWpo2WJJeCR2efXLXBngoucVJzqZDFsyzg_at_mail.gmail.com>



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> 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.
>
> On Tue, Dec 1, 2015 at 2:22 PM, Stefan Koehler <contact_at_soocs.de> wrote:
>
>> Hi Sandy,
>>
>> > I have identified several queries that are "ANDing" indexes yet still
>> have a high cost and over 10,000 buffer gets for a single execution.
>>
>> 10,000 buffer gets per execution is not an indicator. It is more about
>> buffer gets per row. Just quoting from Christian's TOP book
>> (http://www.apress.com/9781430257585) for example - important key word
>> is "Non-aggregated":
>> * Non-aggregated access paths that use 5 or fewer logical I/Os per row
>> returned are reasonable
>> * Non-aggregated access paths that use between 5 and 15 logical I/Os
>> per row returned are probably reasonable
>> * Non-aggregated access paths that use more than 15 to 20 logical I/Os
>> per row returned are probably inefficient
>>
>> > Question: In your experience, would a composite index have
>> better/worse/similar performance than "ANDing" two indexes? At the moment,
>> I don't have
>> > a place to test, but I am working on it.
>>
>> Performance for what? Query? DML? The CBO is very clever and can consider
>> "B-tree to Bitmap Conversions" to combine indexes and do bit-wise
>> operations. If it is faster or not depends on several factors, but
>> INDEX_COMBINE does not work with multi-column indexes up to 12.1.0.1 (have
>> not
>> tested it with 12.1.0.2 yet). It also depends on the environment, e.g. in
>> BI environments this is a very common design practice (e.g. star schema).
>>
>> Best Regards
>> Stefan Koehler
>>
>> Freelance Oracle performance consultant and researcher
>> Homepage: http://www.soocs.de
>> Twitter: _at_OracleSK
>>
>> > Sandra Becker <sbecker6925_at_gmail.com> hat am 1. Dezember 2015 um 20:33
>> geschrieben:
>> >
>> > Oracle EE 11gR2
>> >
>> > Still learning all the ins/outs at my new job. One thing I had
>> noticed is that all indexes are single column. When I asked about it, I
>> was told
>> > this was the current policy. I also was told that if I could show a
>> composite index would be more efficient without breaking anything (always a
>> > concern), it would be considered.
>> >
>> > I've used both single-column and composite in the past and my take is
>> that for index creation "it depends" on the table design, the code,
>> execution
>> > frequency of certain pieces of code, any existing performance issues,
>> is there a problem you're trying to solve, or is this just STD (SQL Tuning
>> > disorder), can other queries benefit from the same index, etc. At the
>> moment, we see the occasional bottleneck. I'm continuing to monitor to see
>> > the frequency and duration.
>> >
>> > So using OEM, AWR, ad hoc queries, explain plans, etc., I have
>> identified several queries that are "ANDing" indexes yet still have a high
>> cost and
>> > over 10,000 buffer gets for a single execution. I am not opposed to
>> "ANDing" at all and think it definitely improves performance in many cases.
>> >
>> > Question: In your experience, would a composite index have
>> better/worse/similar performance than "ANDing" two indexes? At the moment,
>> I don't
>> > have a place to test, but I am working on it.
>> >
>> > --
>> > Sandy B.
>>
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>
>
> --
> Andrew W. Kerber
>
> 'If at first you dont succeed, dont take up skydiving.'
>

-- 
Sandy B.

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Dec 01 2015 - 21:46:33 CET

Original text of this message