Indexing Options for Change Data Capture
Posted by David Aldridge on 2008-04-08
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).
 |
Search the Web: CDC |
2008-04-08 at 10:56 pm
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
where (CASE WHEN UPDATE_DT > DATE ‘2008-01-01′ THEN TRUNC(UPDATE_DT) END = TRUNC(SYSDATE-1)
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
2008-04-09 at 2:06 am
> 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 10.2.0.3. Also I saw a new feature of OWB 11g for Siebel integration, but couldn’t study this up to now.
2008-04-09 at 7:09 am
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
2008-04-09 at 7:17 am
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
2008-04-09 at 9:00 am
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..
2008-04-09 at 9:16 am
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.
2008-04-09 at 11:24 am
You might consider looking at SCNs instead of timestamps for that reason (since you’re on Oracle).
2008-04-09 at 12:24 pm
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.
2008-04-09 at 3:27 pm
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.
2008-04-11 at 11:44 am
[...] Aldridge, the Oracle Sponge, has a question about indexing options for change data capture. I’ll try to summarize it here. He writes, “I have a large and busy OLTP table, 100GB [...]
2008-04-11 at 1:24 pm
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:
SELECT * FROM S_ASSET
WHERE LAST_UPD > (sysdate - 30) AND NOT EXISTS
(SELECT 1 FROM S_ASSET_IMAGE_TABLE IMG WHERE S_ASSET.ROW_ID = IMG.ROW_ID AND S_ASSET.MODIFICATION_NUM = IMG.MODIFICATION_NUM AND S_ASSET.LAST_UPD = IMG.LAST_UPD)
Of course, if you don’t have remote or disconnected users, then you don’t have to worry about this.
2008-04-11 at 4:38 pm
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.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk
2008-04-13 at 8:07 am
[...] as I commented here we will also be working on a logical standby implementation to have the ETL window flexibility in [...]
2008-04-13 at 10:05 am
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
2008-04-13 at 10:30 am
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.
2008-04-22 at 5:59 pm
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: http://www.wisdomforce.com/dweb/index.php?id=1001
2008-04-23 at 7:30 am
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.
2008-05-13 at 1:25 pm
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))
2008-05-14 at 8:12 am
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.