Indexing Options for Change Data Capture

I just posted this question to the Oracle list, but thought I might post it here in case that reaches a wider audience. It also may be a situation that others in the DW arena have faced before.


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).




26 thoughts on “Indexing Options for Change Data Capture

  1. Is partitioning an option (or rather an option you have licenced). If so, that could be used to reduce contention.
    On the function-based index, the Nvl(update_dt,create_dt) would change functionality. If the CREATE_DT was 2007-01-20 23:45 but the UPDATE_DT was 2007-01-21 00:05, then the NVL wouldn’t pick it up on the run on the Jan 21st (as it would fail the ” DATE ‘2008-01-01’ THEN TRUNC(UPDATE_DT) END, CASE WHEN CREATE_DT > DATE ‘2008-01-01’ THEN TRUNC(CREATE_DT) END
    with a where clause of
    or CASE WHEN CREATE_DT > DATE ‘2008-01-01’ THEN TRUNC(CREATE_DT) END = trunc(sysdate-1))
    should (assuming that 100GB covers a large time period) reduce the size of the index and therefore improve any FAST FULL INDEX SCAN
    The index would have a lot of duplicates so either the ROWID would be used to order the rows (which would affect contention depending how ROWIDs determined) or you would add an extra column [eg REVERSE(primary_key)] to that index to reduce contention

  2. > The desire is obviously to provide the maximum performance benefit while reducing the impact on the OLTP system.

    David we are on a similar project, I advised to have a logical standby of the source database as a staging environment for Async CDC(we are rich yes:). Also we will be much flexible with the extraction window if a full extraction needed, any additional indexing or materialized views needed and data type constraints of logical standby was not a problem until now.

    The source is Siebel and both source and target are Also I saw a new feature of OWB 11g for Siebel integration, but couldn’t study this up to now.

  3. Thanks Gary — partitioning is available, and I guess we could hash partition the index only. That would be an interesting option.

    For the Nvl() functionality I think in our particular case we’d be OK. We’d just be looking for the changes in the last 24 hours or so. Still, it’s better to be as flexible as possible.

    I’m thinking that there are two cases for contention — where sessions simultaneously add exactly the same value to the index, and where they add very close values. The latter is dealt with by both partitioning and reversing the index. Adding the pk and reversing that is also very interesting – I’ll have to check whether the PK is also growing sequentially or not.

    I like the idea of reducing index size with the

  4. Hi HTY,

    This is also Siebel ;) and async CDC is coming pretty soon if we can dispel the FUD over it. I’m hoping it’s going to be the panacea for all these woes :D

  5. the other challenge is read consistency…an outstanding txn on your table may (or may not) be trying to put something into your date range. Naturally the longer you wait after the fact, the less the chances, but you do need to be careful…ie

    oltp: 10pm, insert into table values (‘apr-4th, 10pm’)

    cdc: midnight, get everything for apr-4th

    oltp: 12:10am, commit…

    cdc will miss that data..

  6. Yes indeed, and it’s a challenge that we’ve already looked at. Fortunately the queries are typical OLTP short duration stuff which makes it a little easier.

  7. You might consider looking at SCNs instead of timestamps for that reason (since you’re on Oracle).

  8. update_date nullable … brilliant!

    Assuming one would ever need rows inserted but never updated … “where update_date is null” rather than “where update_date = insert_date”

    I’ll go take a running jump now.

  9. JB, we’re bound at the moment to whatever is in the tables, and these dates are our only clues.

    We do have Informatica PowerExchange running in our perf environment though, so that’s moving along nicely.

  10. Pingback: Log Buffer #92: a Carnival of the Vanities for DBAs

  11. Not an index suggestion but a warning about your logic to identify changes. In Siebel the last update date fields may not be reliable for extracting data. This comes up when your Siebel instance supports disconnected users syncing up with the main application, the last update date field will reflect the date from the user’s machine, not when the sync occurred. In this case, these records can be missed during the next extract. If the user entered their data several days ago then sync up right before the next ETL process kicks off, their records will be missed as last_upd is older than sysdate – 1.

    Siebel 8.0 introduced a DB last update field that is supposed to be reliable as it would reflect the sync date but if you are on anything earlier this could be a problem.

    Also, from personal experience using (sysdate – 1) to identify changes can be a real pain if the data warehouse is down for a day and you can’t run the ETL til the following day. You have a lot of SQL’s you have to go and temporarily fix to be (sysdate – 2) just to pick up that lost day and then you have to go back and reset to (sysdate – 1).

    I recall you mentioned in a previous post working with the Siebel Analytic Apps – take a look at their CDC logic, they have a whole system of image tables to identify changes. They rely on the ROW_ID (which is the primary key in Siebel) and MODIFICATION_NUM columns. They do include dates, narrowing to the last 30 days (which should catch any late syncers) to reduce the overall data set scanned. As a result, they end up having an index on ROW_ID+MODIFICATION_NUM+LAST_UPD on the base and image tables and extract changed records from the base table using logic like this:

    WHERE LAST_UPD > (sysdate – 30) AND NOT EXISTS

    Of course, if you don’t have remote or disconnected users, then you don’t have to worry about this.

  12. How about something completely different – copy Oracle’s replication log.

    Is it reasonable to add a row level trigger to the table after insert and update. Copy the rowid to another table, with a timestamp for when the copy arrived. Delete the row when you’ve processed the log item. It avoids the very large indexes you would need because you wanted to identify a small set of data; it avoids most of the contention because your log could have multiple freelists / assm.

    On the down-side, of course, a trigger-based solution turns array processing into single-row processing.

    Jonathan Lewis

  13. Pingback: A DDL tacking application, if CASCADE option of DBMS_STATS needed everytime, ORA_HASH post update and others « H.Tonguç Yılmaz - Oracle Blog

  14. Patrick — fortunately I don’t have to deal with disconnected users. I had to in a previous engagement, and it was pretty interesting. One of the side-effects was a much more complex load process as transactions loaded in a single ETL run could apply to multiple fact table partitions (some up to six months old).

    You’re right about the need for flexibility in the load to take account of having to modify that sysdate-1 predicate. With Informatica we tend to use a parameter file that contains the date to read data from which we also update with each load. It’s pretty clunky — I’d rather be able to write dates to the source system and be able to join directly but that’s sometimes more bureaucracy than it’s worth. Another handy trick with Informatica, and this is something I picked up from Siebel Analytics, is to think about any elements in the SQL statement that you might want to modify in future and place that as a parameter which can be overridden from the parameter file. Siebel Analytics does this to switch between a full load and an incremental one by referencing either the source table directly or a view that contains the CDC logic — very slick. We use it for hints that control parallelism and for issues such as “last_upd >= Trunc(sysdate)-1”, which would appear in the source qualifier SQL as “last_upd >= $$LST_UPD_GTE_TO”, with the parameter file containing $$LST_UPD_GTE_TO=TRUNC(SYSDATE-1)

    Thanks for the thoughts, Patrick

  15. Jonathan, yes that’s a very tidy and efficient option. I’ll have to see if the trigger approach is a possibility. Like you say the array processing turns into single-row, but that’s the same as for materialized views also, and it’s unlikely to be a significant issue on a system like Siebel. Still, the Siebel team may be wary of it. We’d probably log the PK value (confusingly called the row_id in Siebel) instead of the Oracle rowid just to be safe (I think it’s smaller than the rowid anyway).

    One attractive feature of a system like that is that the rowid’s can be added to a sort of “rotating log table”, in which we store the date/time of the change, the rowid, and something like the day_of_week_number. By partitioning on day_of_week_number we can keep seven days of history and purge old entries very cheaply with a partition truncation. We can also maintain a useful amount of history (a week, a month, a quarter …) while still being able to use partition pruning to read those of interest for a daily load without having to redefine new partitions periodically or mess with indexes. If we needed to temporarily go to more than a week between data extracts because of a problem in one of the systems we’d just have to suspend the truncation and the logic for extracting the changes would be unaffected — it’s just be a little slower.

    I’m thinking of implementing a partitioning scheme like that in the data warehouse landing area for the captured changes themselves when we have our proper CDC solution in place. That’s as long as we don’t end up with a requirement to keep a permanent history of every source system change, of course, in which case we’d load captured changes directly to a daily-range partitioned table anyway.

    Thanks Jonathan.

  16. What is the max allowed latency? I just thought why not to move the Change Data Capture operation off from your oltp machine and perform on the different machines? instead of using triggers (not efficient really) just copy the redo logs to the staging linux/windows box and parse and load the changes to destination. Logminer would do for light loaded DB. If there are many transactions you could use for instance Database Sync for CDC:

  17. We don’t have any stringent requirements for near real tme capture — we’re only interested in end-of-day results, not intraday. There’s certainly a confusing range of options out there though.

  18. Hi David,

    Did you implement a solution already?

    I’m a bit late with my proposal. But anyway – my idea comes down to: are there any other ways apart from create_dt to recognize the newly inserted records? Using create_dt for this seems to me too much work because we are normally interested only in the new records (hence we are using a very limited infomation of the column and the eventual index).

    For example, if the table has a surrogate increasing ID column (the new reacords get the highest ID), you might take advantage of it. What about a small log table where the latest “captured” ID ls logged?

    Theen the logic would look this way (you will still need an index on update_dt):

    log the current MAX_ID;

    select …
    from …
    where (ID > MAX_ID_PREVIOUS_RUN and ID <= MAX_ID)
    or (update_dt >= trunc(sysdate)-1 and update_dt < trunc(sysdate))

  19. Well, the solution turns out to be the path of least resistance, which is a full table scan in the early hours of the morning. Frankly the bureaucratics overhead of trying to get changes into the system was more than I was prepared to take on :D

    I think there’s a flaw in your method though, as it relies on two kep points:

    i) A max_id that is growing sequentially.
    ii) Having an index on the max_id.

    Which is rather the same as the problem of having an index on the update_dt itself, I feel, with the same remediations being required.

  20. David,

    Great blog. I tend to be more of a lurker on this and other blogs for a while. I figured I’d offer you some thoughts on this subject though.

    I see you have a solution – but one that sounds like you may revisit. A few thoughts:

    1. It sounds like this is Siebel, so I assume that your update_dt field is really LAST_UPD. Are you certain that this field is nullable in your implementation. Every version I’ve ever inspected this on (including the 8.0 I just tested) populates both CREATED and LAST_UPD on the initial insert of the record.
    2. Again, assuming this is Siebel, I’d point you to MODIFICATION_NUM being compared to an image table (as someone else suggested) as a reliable source of an answer to “What records have been updated since I last ran?” The date fields are just dates – and have issues others have commented on. The MODIFICATION_NUM field is updated on each insert or update statement that goes through Siebel processes.
    3. Please verify that your filter is really based on updates to the table you are working with – and not on one or more of what Siebel calls a “Business Component.” For example, if you want to extract “all orders that have been updated”, your solution is not likely to drive strictly off of the S_ORDER table, but also off of the S_ORDER_ITEM table (and possibly the variety of attribute and extended attribute tables for each.) Similarly, Siebel models the “primary address” as a part of the Account – but the S_ORG_EXT table won’t be updated just becuase someone updates the zipcode on the primary address.

  21. Thanks Joe,

    Yes, I think you might well be right on point 1. That certainly makes it more simple, though some of the basic challenges remain.

    2 and 3 are very interesting also. I wish I could get that kind of insight from our own Siebel team :D

  22. Todor,

    Perhaps. I’m not knowledgable enough about the SCN and when it increments to confirm. But here’s the scoop on MODIFICATION_NUM and why it is really there.

    Each update statement is of the form:
    UPDATE table set
    field1 = :bv1,
    field2 = :bv2,

    modification_num = :bvN
    where row_id = :bvX
    and modification_num = :bvY

    Why do such a thing? Concurency control.

    Siebel creates a data object in its application server that it calls a “Business Component” (or BC for short). It creates a BC from a select query against the database. The user can then work with the data in the BC – and even update it. The updates are staged in the BC object on the application server – and only written back to the database on certain run time events (WriteRecord I believe – perhaps a few others).

    Well, Siebel doesn’t take out a FOR UPDATE lock on the database. What it does do is store the MODIFICATION_NUM and ROW_ID for each record in each table in the BC. When the update statement is executed, Siebel sets the MODIFICATION_NUM to CURR_MOD_NUM + 1 (bvN in my example above). It also includes CURR_MOD_NUM in the update statement (bvY in my example).

    So – what happens if another BC object has updated that record on that table in the meantime? As you can see, the update statement will not update any rows, since the filter in the where clause “fails”. Siebel traps this error, rolls back the user’s change and complains.

    Note that this can happen between sessions (which is the more logical case) or within a user session (which is the more common one). It is possible that a user session creates two different BC objects that contain the same record in the database – and then try to update both of them in turn. This is almost always a “bug”, but one that happens from time to time.

    So – as you can see, MODIFICATION_NUM is not really intended to be a counter of the number of times a record has been updated in the server database. But you can also see that it effectively is.

  23. So MODIFICATION_NUM is the Siebel’s mechanism for applying correct optimistic locking.

  24. Todor –

    Correct. An interesting part of the product’s history is that it “originally” had to mostly solve for what Siebel calls “remote” users. Before version 7 (released in 2000 or 2001) the primary usage of Siebel was for mobile field sales users who worked disconnected on their laptops on a local database (Sybase SQL Anywhere). Siebel had a neat little “store and forward” replication scheme and had to solve concurrency problems in this kind of architecture.

    It’s “connected client” architecture was 2-tier client server in which the SQL statements came directly from the client application on the user’s computer. Of course, the modification_num solution works for this architecture as well, but it really wasn’t an option for the product to use a server database feature.

    The mobile client architecture is still in use today – but Siebel’s application server architecture (as well as the ubiquity of broadband connections compared to end of the 20th century) makes it much less common in my experience.

    There are some decisions that appear to have been made in the pursuit of “database agnosticism” (although none terribly limiting in practice), but this one I think they got pretty much right.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s