Re: Single-column vs composite index
Date: Tue, 1 Dec 2015 14:09:40 -0700
Message-ID: <CAJzM94DqWT266FnRCVO61yyVOBcLj+=7F8okeB+r7Fv1_vnGsg_at_mail.gmail.com>
Very good points. Just in the short time I've been here, the primary application has changed and I had a couple of calls concerning performance with the latest changes. That's what started me down this path to begin with after reviewing the new code, which was well-written by the way.
The FTS are on tables with over 100M rows. I consider that to be a sizable amount. There are some FTS where there are less than 100 rows. No big deal on those. Probably more efficient than scanning an index and then going to the table anyway.
On Tue, Dec 1, 2015 at 2:00 PM, raza siddiqui <raza.siddiqui_at_oracle.com> wrote:
> Could also be that the original "design" has out-grown itself.
>
> Additionally, FTS are not necessarily a bad thing - but IT DEPENDS on size
> of the table being FTS'd. x000's of referential rows as compared to
> x000,000's of transient data rows
>
> As has been said, time to assess current state of database / table sizes,
> index usage and whether the application that is being served has also
> evolved over time, and whether "adjustments" need to be implemented to
> bring it to an even-keel
>
> $0.02
>
> Raza
>
> On 12/1/2015 12:51 PM, Andrew Kerber wrote:
>
> 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>
> 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>
>> 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.
>>
>>
>
>
> --
> Andrew W. Kerber
>
> 'If at first you dont succeed, dont take up skydiving.'
>
>
>
>
-- Sandy B. -- http://www.freelists.org/webpage/oracle-lReceived on Tue Dec 01 2015 - 22:09:40 CET