View Revisions: Issue #3793

Summary 0003793: Optimize routing query for Oracle when majority of sym_data is unrouted
Revision 2019-04-04 14:36 by mmichalek
Description This routing query select ~31M out of 42 M rows. It timed out after 1 hour.

select d.data_id, d.table_name, d.event_type, '' as row_data, '' as pk_data, '' as old_data, d.create_time, d.trigger_hist_id, d.channel_id, d.transaction_id, d.source_node_id, d.external_data, d.node_list from sym_data d where d.channel_id='default' and data_id >= 804584792 order by d.create_time asc, d.data_id asc

Adding the full table scan hint, it comes back in 85 seconds:
 select /*+ full (d) */ d.data_id, d.table_name, d.event_type, '' as row_data, '' as pk_data, '' as old_data, d.create_time, d.trigger_hist_id, d.channel_id, d.transaction_id, d.source_node_id, d.external_data, d.node_list from sym_data d where d.channel_id='default' and data_id >= 804584792 order by d.create_time asc, d.data_id asc

This is off by default. Can be enabled for Oracle with:
oracle.use.select.data.using.start.data.id.hint=true
Revision 2018-11-13 16:33 by mmichalek
Description This routing query select ~31M out of 42 M rows. It timed out after 1 hour.

select d.data_id, d.table_name, d.event_type, '' as row_data, '' as pk_data, '' as old_data, d.create_time, d.trigger_hist_id, d.channel_id, d.transaction_id, d.source_node_id, d.external_data, d.node_list from sym_data d where d.channel_id='default' and data_id >= 804584792 order by d.create_time asc, d.data_id asc

Adding the full table scan hint, it comes back in 85 seconds:
 select /*+ full (d) */ d.data_id, d.table_name, d.event_type, '' as row_data, '' as pk_data, '' as old_data, d.create_time, d.trigger_hist_id, d.channel_id, d.transaction_id, d.source_node_id, d.external_data, d.node_list from sym_data d where d.channel_id='default' and data_id >= 804584792 order by d.create_time asc, d.data_id asc