One thing I hadn't seen mentioned yet is the other side of things - not the
access paths, but the overhead when doing DML. Simple case in point:
SQL> create table t (x1 int, x2 int, x3 int);
Table created.
SQL> create index i1 on t (x1);
Index created.
SQL> create index i2 on t (x2);
Index created.
SQL> create index i3 on t (x3);
Index created.
SQL> set autot on stat
SQL> insert into t values (1,1,1);
1 row created.
Statistics
----------------------------------------------------------
1 recursive calls
14 db block gets
1 consistent gets
0 physical reads
0 redo size
1159 bytes sent via SQL*Net to client
1345 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> r
1* insert into t values (1,1,1)
1 row created.
Statistics
----------------------------------------------------------
0 recursive calls
7 db block gets
1 consistent gets
0 physical reads
0 redo size
1159 bytes sent via SQL*Net to client
1345 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> r
1* insert into t values (1,1,1)
1 row created.
Statistics
----------------------------------------------------------
0 recursive calls
7 db block gets
1 consistent gets
0 physical reads
0 redo size
1160 bytes sent via SQL*Net to client
1345 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> r
1* insert into t values (1,1,1)
1 row created.
Statistics
----------------------------------------------------------
0 recursive calls
7 db block gets
1 consistent gets
0 physical reads
0 redo size
1160 bytes sent via SQL*Net to client
1345 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
Maintaining each individual index comes with a price. If we instead switch
to a single index, the number of blocks we need to modify is cut in half:
SQL> drop index i1;
Index dropped.
SQL> drop index i2;
Index dropped.
SQL> drop index i3;
Index dropped.
SQL> create index i on t (x1, x2, x3);
Index created.
SQL> insert into t values (1,1,1);
1 row created.
Statistics
----------------------------------------------------------
3 recursive calls
5 db block gets
5 consistent gets
0 physical reads
0 redo size
1161 bytes sent via SQL*Net to client
1345 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> r
1* insert into t values (1,1,1)
1 row created.
Statistics
----------------------------------------------------------
0 recursive calls
3 db block gets
1 consistent gets
0 physical reads
0 redo size
1160 bytes sent via SQL*Net to client
1345 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> r
1* insert into t values (1,1,1)
1 row created.
Statistics
----------------------------------------------------------
0 recursive calls
3 db block gets
1 consistent gets
0 physical reads
0 redo size
1161 bytes sent via SQL*Net to client
1345 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
The more indexes, the more work Oracle needs to do when modifying data. If
you have 30-single column indexes on a table, you'll be doing vastly more
work when inserting a row, compared to 4 or 5 combined ones.
Stefan
On Fri, Dec 4, 2015 at 4:07 PM, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
wrote:
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 um21:51 geschrieben:
thought that oracle would use multiple single column indexes at once on the
Hmm. I wonder if the people who thought of that policy somehow
same
table and condition? I suspect you will need to educate them onoracle 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
<mailto:sbecker6925@xxxxxxxxx> > wrote:
On Tue, Dec 1, 2015 at 2:46 PM, Sandra Becker <sbecker6925@xxxxxxxxx
> > Andrew,also require the primary key of EVERY table be a sequence. Again, no
This is the first time I have encountered such a policy. They
problem
However, I see queries frequently are not done on a unique value using thewith that policy. Put in place long before I came on board.
primary
other indexes come into play some of the time. I'm seeing some FTS on somekey, but on a range on another column. That seems to be when the
most interested in looking at right now.rather large tables, lots of disk I/O. Those are the queries I am
policy like that before. Their position is that composite indexes are
They were surprised here when I said I had never heard of a
bad. They
also seen single-column indexes that resulted in tremendous amounts of I/Ocertainly can be; I've seen that with poor design constructs. I've
composite index. It varies. I was curious what others have experienced,that could have been avoided by using an appropriately formed
what they
look for when reviewing indexes.
Thanks for the feedback.
Sandy
andrew.kerber@xxxxxxxxx <mailto:andrew.kerber@xxxxxxxxx> > wrote:On Tue, Dec 1, 2015 at 1:30 PM, Andrew Kerber <
that all indexes are single column. I suppose I could see it for primary> > > I have to say that I have never heard of a policy
key
queries are done on the unique value, but I cant visualize how something> > > indexes, when a sequence is always being used, and all
like
by someone who does not understand how oracle indexes work.> > > that could be designed. It sounds like a policy written
--
Sandy B.
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
--
Sandy B.