View Revisions: Issue #3793
| Summary | 0003793: Optimize routing query for Oracle when majority of sym_data is unrouted | ||
|---|---|---|---|
| Revision | 2018-11-13 21: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 |
||
| Revision | 2019-04-04 18: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 |
||