Re: Single-column vs composite index

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Tue, 1 Dec 2015 14:30:53 -0600
Message-ID: <CAJvnOJbdUq6mi62eBz5r2ScLV=JRfkDF3=rduO=CZdQHGSityA_at_mail.gmail.com>



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

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

Original text of this message