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: @OracleSK
Andrew Kerber <andrew.kerber@xxxxxxxxx> 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@xxxxxxxxx
<mailto: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
<mailto: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.
