Re: Indexing options to avoid contention -- 10gR2
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: david@xxxxxxxxxxxxxxxxxx
- Date: Wed, 09 Apr 2008 06:38:27 +0200
David,
First of all, forget about reverse indexes, because then you
couldn't have range scans.
There is something that is missing from your analysis, which is the
relative ratio of inserts vs updates.
Presumably, contention is mostly a concern for inserts, and create_dt.
Unless updates are applied following a condition on create_dt, they
shouldn't be much of an issue.
I believe that you should consider your query as the union of two
different SELECT statements (it's quite possible that this is what the
optimizer does, anyway). For the SELECT that involves create_dt, what
I'd like to have in such a case is a table partitioned by day, which
would spare me an index and make contention not much worse than what it
is without an index (moreover, depending on your data, subpartitioning
might help). This would make update_dt the only column worth indexing,
adding some overhead because of index management but little contention
issues.
If partitioning isn't an option (not least because the table is already
partitioned), two separate columns are more costly than one, and I don't
believe that ii) would be very efficient. I'd have the same doubts as
you concerning iii), you have high odds of sending the optimizer on a
wrong track with such a query. The solution I'd like best is probably
the function-based index, it would cost a little extra CPU, and all the
more that rows are frequently updated, but if CPU isn't the bottleneck
it may be OK. Note also that if you have more updates than inserts, it
should relieve contention. I wouldn't consider such a simple FBI to be
much more of a risk than a regular index.
HTH
Stéphane Faroult
David Aldridge wrote:
I have a large and busy OLTP table, 100GB or so, against which there
is a need to capture changes. Until an asynchronous CDC solution is in
place we have to rely on two columns: create_date and update_date
(null until the first update), both being of DATE type of course.
These are currently unindexed, but there is a desire to index them to
improve change capture performance for queries such as:
select ...
from ...
where (create_dt >= trunc(sysdate)-1 and create_dt < trunc(sysdate))
or (update_dt >= trunc(sysdate)-1 and update_dt < trunc(sysdate))
The desire is obviously to provide the maximum performance benefit
while reducing the impact on the OLTP system.
I thought of four different indexing options:
i) Indexing the columns separately, leading in the best case to an
unusual execution plan where the indexes are range scanned and the
results merged before accessing the table.
ii) A single composite index (create_date,update_date),leading to a
fast full index scan.
iii) A single composite index (update_date,create_date), rewriting the
query predicate as ...
(create_dt >= trunc(sysdate)-1 and create_dt < trunc(sysdate) and
update_date is null)
or (update_dt >= trunc(sysdate)-1 and update_dt < trunc(sysdate))
... and leading to two index range scans. (not sure about this)
iv) A single-column function based index on
(Nvl(update_dt,create_dt)) and rewriting the predicate appropriately.
Whichever of these is chosen the growth pattern for the data naturally
tends towards index contention as all the new values are equal to
sysdate.
So the nub of my problem comes down to these questions:
Is it possible to mitigate the index contention problem with a
suitably high initrans values? Does it seem likely that implementing a
reverse index would be worthwhile in reducing contention (I could
probably take the pain of the fast full scan if it was, although it
would lead to a larger index due to the 50/50 block splits).
Would you regard implementing a function-based index as risky in any
significant way on major tables of a busy OLTP system? How about a
reverse function based index?
Basically, "what would you do"?
Maybe I'm overthinking it, but I'd like to go to the sytem developers
with a fully thought out set of options (even if they do tell me to
take a running jump anyway).
--
//www.freelists.org/webpage/oracle-l
Other related posts:
