Find change data references in a time range with minimal Change tracking

When the table Policy defines change tracking as Level 0 or Level 1, the Change table is not used. Instead, the Change Tracking table identifies the type of change and the row in the source table that was changed.

The following example is a simple case of an application table with only one CDC table policy applied. This query identifies all the changes made to the source table in a specific change order and within a time range.

select ct.*                      -- specify change tracking fields here.
from   pub.”_File” f
inner join
       pub."_Cdc-Change-Tracking" ct 
          on f."_File-Number" = ct."_Source-Table-Number"
where f."_file-name" = ‘customer’
and   f."_Owner"     = ‘pub’
and   ct."_Time-Stamp" between ? and ?    -- time range parameter values
order by ct.”_Source-Table-Number", ct." _Change-Sequence";
         -- sort keys map to index _Sequence-Id

This use case is for a more complex case of any change -tracked application table with multiple CDC table Policies applied to it. This query finds all the changes that were made to the source table in the order in which the changes were made for the policy that is currently active. For this table, the table policy defines that the client sets the table Name parameter, Owner Name parameter, Start Time for the time range, and End Time for the time range. For improved performance, this query should be performed in two steps. The single step query is as follows:

select ct.*                      -- specify change tracking fields here.
from   pub.”_File” f
inner join 
       pub."_CDC-Table-Policy" tp
	  on  f.rowid = tp.”_Source-File-Recid”
	  and tp."_Policy-Instance"  = 0      -- Policy is current.
inner join
       pub."_Cdc-Change-Tracking" ct
          on f."_File-Number" = ct."_Source-Table-Number"
          and tp."_Policy-Id" = ct."_Policy-Id"
where f."_file-name" = ?              -- table name parameter from ETL app.
and   f."_Owner"     = ?              -- table name parameter from ETL app.
and   ct."_Time-Stamp" between ? and ?      -- time range parameter values
order by ct."_Change-Sequence";

The two step query is shown below. The second step can use an index to avoid sorting and is as follows:

-- Step 1 – give one row with CDC policy id, source table number
select ct."_Policy-Id",", f."_File-Number"             --for step 2 query.
from   pub.”_File” f
inner join 
       pub."_CDC-Table-Policy" tp
	  on  f.rowid = tp.”_Source-File-Recid”
	  and tp."_Policy-Instance"  = 0      -- Policy is current.
where f."_file-name" = ?              -- table name parameter from ETL app.
and   f."_Owner"     = ? ;            -- table name parameter from ETL app.

-- Step 2
select ct.*                           -- specify change tracking fields here.
from   pub."_Cdc-Change-Tracking" ct
where ct."_Source-Table-Number" = ?   -- source table number from step 1
and   ct."_Policy-Id" = ?             -- Table policy id parameter from step 1
and   ct."_Time-Stamp" between ? and ?      -- time range parameter values
order by ct."_Source-Table-Number", ct."_Time-Stamp", ct." _Change-Sequence";
                                        -- maps to index __Time-Stamp-Seq