Scientifically it is not quite decided how silver works on vampires. There is a
sad lack of volunteer vampires to test on.
----Ursprüngliche Nachricht----
Von : jonathan@xxxxxxxxxxxxxxxxxx
Datum : 04/12/2015 - 10:07 (GMT)
An : oracle-l@xxxxxxxxxxxxx
Betreff : RE: Re: Single-column vs composite index
P {margin-top:0;margin-bottom:0;}
I've got to disagree with you on that one, Lothar: Silver bullets is
werewolves, not vampires.
Sandra,
Adding my $0.02 - For many years I used to tell people that if all their
indexes were single column they'd done it wrong; then, when bitmap conversion
of b-tree indexes became the default, I changed this to "less wrong" but in
need of careful justification.
In a mixed OLTP/DSS environment it's possible that a very large percentage of
the indexes could be single column to benefit the DSS component, but it's
almost guaranteeable that there should be some multi-column indexes to optimise
OLTP response time. Trivial
example: show me the orders placed by this customer in the last two months:
you don't want a query screen to have to combine a "customer_id" index with an
"order_date" index to find two rows, you want the "(customer_id, order_date)
compress 1" index.
Regards
Jonathan Lewis
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of l.flatz@xxxxxxxxxx [l.flatz@xxxxxxxxxx]
Sent: 04 December 2015 09:00
To: sbecker6925@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Re: Single-column vs composite index
Good job. Seems they mixing up cause and symptom.
You can have optimzer missestimations because of multi column primary KEYS.
There is a fair chance of inter column dependency if the child table inherits
the PK of the parent table. The classic from my own experience is
table mobile_phone_call with the key columns customer_id, mobile_phone_number
and some dateTime column.
In real live most of the time one customer got one mobile_phone_number. Thus
leading the optimizer into underestimation the result if both columns used as
search criteria.
Although the database will automatically generate a multi column index on that,
the fuss from the underestimation has got NOTHING to do with the INDEX.
I distrust silver bullets anyway. Should those silver bullet guys hunt vampires
in the dark rather than messing up IT.
----Ursprüngliche Nachricht----
Von : sbecker6925@xxxxxxxxx
Datum : 03/12/2015 - 22:25 (GMT)
An : andrew.kerber@xxxxxxxxx
Cc : mwf@xxxxxxxx, contact@xxxxxxxx, oracle-l@xxxxxxxxxxxxx
Betreff : Re: Single-column vs composite index
As if there aren't enough restrictions, they had to set up a completely
arbitrary one because of the incorrect belief that "ALL composite indexes are
bad". I've fought with that belief before and was able to prove it ain't so.
Caution
will be used and team discussion before making any changes in production, but
it would be nice to get them to look at possibilities. One of the reasons I
was hired was to be pro-active and see where improvements might be made.
As luck would have it, I've just been tasked with cloning this particular
database to our sandbox for some testing to take place 1st quarter. I will
then take "suspect" queries and play with some indexing scenarios in the
sandbox. I'll be sure to grab other
queries that use the same index and verify they don't break. Or attempt to at
any rate. Once it hits production, the results can surprise you. Love the
feature where you can hide indexes. Used it many times at my previous
employer. Lots of good info in
this discussion.
Sandy
On Thu, Dec 3, 2015 at 1:32 PM, Andrew Kerber
<andrew.kerber@xxxxxxxxx> wrote:
Similar but related, it could be a legacy database that originated in a system
that did not allow concatenated indexes.
On Thu, Dec 3, 2015 at 1:58 PM, Mark W. Farnham
<mwf@xxxxxxxx> wrote:
At the very least disallowing concatenated indexes as policy is wearing a
voluntary straight-jacket.
Everything about data model and index design should be as free from restriction
as possible but should consider operational overhead.
For a single example, you might demonstrate to them on a test system where a
frequent query of a small number of columns on a wide table can be done
completely
from the index without dipping into the table at all (making cluster factor
irrelevant, by the way).
If some single column index is not a constraint definition and is never the
leading edge or used alone in a query, you might well save both overhead and
provide
quicker and cheaper query response by adding the column to an existing index
(or a few if relevant) and dropping the single column index. Your mileage will
vary.
To me this sounds like a policy rooted in one of both of the following:
1)
Database agnostic policy and some databases just don’t have concatenated indexes
2)
Someone when hog wild at some point in time and created excessive indexes,
particularly on hot oltp tables where this was a material nuisance to insert,
update, and delete performance.
Good luck. If anyone has a sane explanation of why this policy might be good,
I’m all ears.
mwf
From:
oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Sandra Becker
Sent: Tuesday, December 01, 2015 4:14 PM
To: Stefan Koehler
Cc: andrew.kerber@xxxxxxxxx; oracle-l
Subject: Re: Single-column vs composite index
Also valid points. For a few tables, that is definitely the scenario; for
others it isn't. It will definitely require more time to monitor and evaluate
before any changes are considered. Only looking at those
queries doing massive amounts of I/O and causing "concern" in the user
community right now.
Sandy
On Tue, Dec 1, 2015 at 2:05 PM, Stefan Koehler <contact@xxxxxxxx> wrote:
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.
