Re: Indexing options to avoid contention -- 10gR2
- From: David Aldridge <david@xxxxxxxxxxxxxxxxxx>
- To: Toon Koppelaars <toon@xxxxxxxxxxx>
- Date: Wed, 9 Apr 2008 19:11:20 -0700 (PDT)
Sadly not -- the code that maintains the table is untouchable. Thanks for the
thought though Toon.
Still, maybe a materialized view log capturing only the PK value ... hmmm.
----- Original Message ----
From: Toon Koppelaars <toon@xxxxxxxxxxx>
To: david@xxxxxxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Sent: Tuesday, April 8, 2008 11:37:39 PM
Subject: Re: Indexing options to avoid contention -- 10gR2
David,
Is a row-trigger currently maintaining the CREATE_DT and UPDATE_DT columns?
If so you could also opt for that row-trigger to dump (at create and update of
a row) the PK-value of the row into a second new table, that only has the
PK-column, together with some PROCESSED indicator.
And then if you need to capture the changes use this small table as a
nested-loop driver to your big one. First set processed flag to 'In process' or
something, then process them, then delete all 'In process' rows (there could be
new rows inserted during your processing, with process='n', by now).
There would be *no* need to add any index, not on the big table (I'm assuming
the PK is indexed...:-) ), and not on this new small one...
Toon
On 4/9/08, David Aldridge <david@xxxxxxxxxxxxxxxxxx> 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).
--
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
Toon Koppelaars
RuleGen BV
+31-615907269
toon@xxxxxxxxxxx
www.rulegen.com
Author: "Applied Mathematics for Database Professionals"
Other related posts:
