Hi please can anybody explain to me? I have a polling table with these records for example
| CATALOG_NAME | DATABASE_NAME | OTHER_RESERVED | SCHEME_NAME | TABLE_NAME | UPDATE_TS | UPDATE_TYPE |
|---|
| null | null | null | null | TABLE1 | 8.11.2019 7:50:51,111111 | 1 |
| null | null | null | null | TABLE2 | 8.11.2019 7:50:51,222222 | 1 |
| null | null | null | null | TABLE3 | 8.11.2019 7:50:51,111111 | 1 |
The event will add new records
| CATALOG_NAME | DATABASE_NAME | OTHER_RESERVED | SCHEME_NAME | TABLE_NAME | UPDATE_TS | UPDATE_TYPE |
|---|
| null | null | null | null | TABLE1 | 8.11.2019 7:50:51,111111 | 1 |
| null | null | null | null | TABLE2 | 8.11.2019 7:50:51,222222 | 1 |
| null | null | null | null | TABLE3 | 8.11.2019 7:50:52,111111 | 1 |
| null | null | obis1 | null | TABLE1 | 8.11.2019 7:50:51 | 1 |
| null | null | obis1 | null | TABLE2 | 8.11.2019 7:50:51 | 1 |
| null | null | obis1 | null | TABLE3 | 8.11.2019 7:50:52 | 1 |
And then performs delete and result look like this:
| CATALOG_NAME | DATABASE_NAME | OTHER_RESERVED | SCHEME_NAME | TABLE_NAME | UPDATE_TS | UPDATE_TYPE |
|---|
| null | null | null | null | TABLE1 | 8.11.2019 7:50:51,111111 | 1 |
| null | null | null | null | TABLE2 | 8.11.2019 7:50:51,222222 | 1 |
| null | null | null | null | TABLE3 | 8.11.2019 7:50:52,111111 | 1 |
| null | null | obis1 | null | TABLE1 | 8.11.2019 7:50:51 | 1 |
| null | null | obis1 | null | TABLE2 | 8.11.2019 7:50:51 | 1 |
It perfroms delete just to the datetime which is truncated to seconds and only the unique ones. So my question is how can I achieve that the records will be all deleted after cache purging? Oracle documentation says the UPDATE_TS is unique key, but this is though without the seconds decimals.