Return
-
Title
Compare table skipped because difference is greater than nn percent or nnnnn. -
Description
When running compare the following errors are encountered in the compare log on Source (*desvr* log):desvr 2021-09-10 16:35:58.357422 20530 1084229984 [1] DEBUG (swapSource^swapTarget)=0desvr 2021-09-10 16:35:59.632457 20530 1084229984 [1] Number of rows on source=17216250, number on target=52395271desvr 2021-09-10 16:35:59.633568 20530 1084229984 [1] Table skipped because difference is greater than 20 percent or 1000000desvr 2021-09-10 16:35:59.870139 20530 1084229984 [1] Status : SkippedThe command used is:sp_ctrl> compare owner.source_table_name -
Cause
Compare has the logic to check the row count difference between Source and Target tables using num_rows from dba_tables.By default, it will skip the compare if the difference is greater than 20% or 1 million rows. -
Resolution
WORKAROUND:Compare looks at num_rows column in dba_tables view to determine the row count.If one of the tables has not been analyzed in a long time, then compare may also interpret that the table is smaller than its counterpart on the other side (though in reality that may not be the case).The compare status then shows the table as “skipped”.The parameter SP_DEQ_COPY_CHECK_ROWS determines whether compare will invoke this logic or not.The default value is 200,000 rows.If the row count on either the Source or Target table is greater than this threshold then it will check if the Source and Target row count difference is greater than 20% or greater than 1 million rows.If it meets either condition it will skip the compare.If the number of rows is less than the value of the SP_DEQ_COPY_CHECK_ROWS parameter then compare will execute without invoking this logic.To disable this logic the following steps need to be performed:1. Set the SP_DEQ_COPY_CHECK_ROWS parameter to a value higher than the estimated number of rows on both the Source and Target table.The parameter needs to be set on both Source and Target prior to executing another compare.E.g. compare determines that the Source table has about 17M rows while the Target table has about 52M rows.In this case the parameter should be set to 100 million rows before executing compare again.sp_ctrl> set param SP_DEQ_COPY_CHECK_ROWS 1000000002. Execute compare.sp_ctrl> compare owner.source_table_name3. To reset the parameter:sp_ctrl> reset param SP_DEQ_COPY_CHECK_ROWSIf you set the parameter SP_DEQ_COPY_CHECK_ROWS to a very high value then the compare should work without suggesting to you to use copy and failing thereafter.Keeping the parameter at that high value would ensure that you do not have to tweak it from time to time to accommodate large tables with varying row counts. -
Additional Information
SP_DEQ_COPY_CHECK_ROWS