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@xxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxx> wrote:
Hi Sandy,
I have identified several queries that are "ANDing" indexes yet stillhave 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 havebetter/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: @OracleSK
Sandra Becker <sbecker6925@xxxxxxxxx> hat am 1. Dezember 2015 um20:33 geschrieben:
noticed is that all indexes are single column. When I asked about it, I
Oracle EE 11gR2
Still learning all the ins/outs at my new job. One thing I had
was told
this was the current policy. I also was told that if I could show acomposite index would be more efficient without breaking anything (always a
concern), it would be considered.that for index creation "it depends" on the table design, the code,
I've used both single-column and composite in the past and my take is
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 themoment, we see the occasional bottleneck. I'm continuing to monitor to see
the frequency and duration.identified several queries that are "ANDing" indexes yet still have a high
So using OEM, AWR, ad hoc queries, explain plans, etc., I have
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.
better/worse/similar performance than "ANDing" two indexes? At the moment,
Question: In your experience, would a composite index have
I don't
have a place to test, but I am working on it.
--
Sandy B.
--
//www.freelists.org/webpage/oracle-l
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
--
Sandy B.
