Re: RE: Re: Single-column vs composite index
Date: Fri, 04 Dec 2015 13:16:00 +0000
Message-ID: <13385619.24112.1449234960584.JavaMail.webmail_at_bluewin.ch>
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_at_jlcomp.demon.co.uk
Datum : 04/12/2015 - 10:07 (GMT)
An : oracle-l_at_freelists.org
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
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of l.flatz_at_bluewin.ch [l.flatz_at_bluewin.ch]
Sent: 04 December 2015 09:00
To: sbecker6925_at_gmail.com
Cc: oracle-l_at_freelists.org
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_at_gmail.com
Datum : 03/12/2015 - 22:25 (GMT)
An : andrew.kerber_at_gmail.com
Cc : mwf_at_rsiz.com, contact_at_soocs.de, oracle-l_at_freelists.org
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_at_gmail.com> 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_at_rsiz.com> 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Sandra Becker
Sent: Tuesday, December 01, 2015 4:14 PM
To: Stefan Koehler
Cc: andrew.kerber_at_gmail.com; 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_at_soocs.de> 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: _at_OracleSK
> Andrew Kerber <andrew.kerber_at_gmail.com> 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_at_gmail.com <mailto: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 <mailto: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.
-- 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 Fri Dec 04 2015 - 14:16:00 CET