View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0003793 | SymmetricDS | Improvement | public | 2018-11-13 21:33 | 2019-05-01 14:26 |
| Reporter | mmichalek | Assigned To | mmichalek | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.8.0 | ||||
| Target Version | 3.9.19 | Fixed in Version | 3.9.19 | ||
| Summary | 0003793: Optimize routing query for Oracle when majority of sym_data is unrouted | ||||
| 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 | ||||
| Tags | No tags attached. | ||||
|
SymmetricDS: 3.9 1d2d96b0 2019-04-04 14:37:03 Details Diff |
0003793: Optimize routing query for Oracle when majority of sym_data is unrouted |
Affected Issues 0003793 |
|
| mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/oracle/OracleSymmetricDialect.java | Diff File | ||
| mod - symmetric-core/src/main/java/org/jumpmind/symmetric/common/ParameterConstants.java | Diff File | ||
| mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/RouterServiceSqlMap.java | Diff File | ||
|
SymmetricDS: 3.10 9cff3b69 2019-04-04 14:37:03 Details Diff |
0003793: Optimize routing query for Oracle when majority of sym_data is unrouted |
Affected Issues 0003793 |
|
| mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/oracle/OracleSymmetricDialect.java | Diff File | ||
| mod - symmetric-core/src/main/java/org/jumpmind/symmetric/common/ParameterConstants.java | Diff File | ||
| mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/RouterServiceSqlMap.java | Diff File | ||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2018-11-13 21:33 | mmichalek | New Issue | |
| 2018-11-13 21:33 | mmichalek | Status | new => assigned |
| 2018-11-13 21:33 | mmichalek | Assigned To | => mmichalek |
| 2018-11-28 18:23 | mmichalek | Target Version | 3.8.41 => |
| 2019-04-04 18:36 | mmichalek | Description Updated | View Revisions |
| 2019-04-04 18:40 | mmichalek | Status | assigned => resolved |
| 2019-04-04 18:40 | mmichalek | Resolution | open => fixed |
| 2019-04-04 18:40 | mmichalek | Fixed in Version | => 3.9.19 |
| 2019-04-04 18:40 | mmichalek | Target Version | => 3.9.19 |
| 2019-04-04 19:00 | mmichalek | Changeset attached | => SymmetricDS 3.9 1d2d96b0 |
| 2019-04-04 19:00 | mmichalek | Changeset attached | => SymmetricDS 3.10 9cff3b69 |
| 2019-04-23 15:14 | elong | Status | resolved => closed |
| 2019-05-01 14:26 | mmichalek | Relationship added | related to 0003931 |