Timeline for will table partitioning and compression help heavily used OLTP database?
Current License: CC BY-SA 3.0
8 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Aug 9, 2015 at 6:18 | history | tweeted | twitter.com/#!/StackDBAs/status/630261780049207296 | ||
| Jul 31, 2015 at 5:05 | answer | added | Aaron | timeline score: 1 | |
| Jul 30, 2015 at 22:01 | answer | added | Dave | timeline score: 3 | |
| Jul 30, 2015 at 21:23 | comment | added | Kin Shah | if you want to purge that table - How about you create another table with different schema and exact same structure and then do a schema switch. Then in the new table you can implement partitioning which would be much manageable. If you need the data from the old table, your best bet is to go for table partitioning (its an enterprise edition feature). | |
| Jul 30, 2015 at 21:19 | comment | added | Thronk | Your question pre-supposes this approach will not work, and in fact, I think you will be hard pressed to find proof it will not work. Partitioning is one of the best ways to improve performance in OLTP as well as OLAP. You can switch out partitions in seconds which would take log space and time to delete and you can do this while database is online. I use monthly partitions like this currently, | |
| Jul 30, 2015 at 21:10 | comment | added | Justin Cave |
Why wouldn't it work? There may be valid reasons but nothing in what you've posted here would seem to indicate that your boss is wrong. If the table is partitioned by audit_date and your purge process is just deleting rows based on their audit_date, dropping an old partition is going to be vastly more efficient than deleting rows. Perhaps, though, you know something about your data that we don't that tells you that your boss's idea won't work.
|
|
| Jul 30, 2015 at 20:59 | review | First posts | |||
| Jul 30, 2015 at 21:20 | |||||
| Jul 30, 2015 at 20:58 | history | asked | Ran Wei | CC BY-SA 3.0 |