I just watched Oracle's In Memory database release. I can understand that removing a lot OLAP indexes accelerated the database a bit. But does that really mean maintaining 2 table storages doesn't have extra cost? Like why would insert still be faster when 2 storages need to be maintained simultaneously?
The disadvantage of maintaining both row and column table storage
in-memory-databaseoracle
Related Question
- What to call a column that references another row in the same table
- Oracle – How to Prevent Duplicate Values in Column During Insert
- Changing the WHAT Column of DBA_JOBS – Oracle 11g
- Oracle Index Monitoring Status Change – Why It Changes to ‘No’?
- Oracle 11g R2 – Understanding SYS_C00054_14031410:34:03$ Column
- Repeat row in the result based on the value of a column
- Oracle – Advantages and Disadvantages of Maintaining One ORACLE_HOME
Best Answer
Maintaining two representations of data is obviously more complex than maintaining only one, and added complexity always has a cost. That cost might only be in code complexity and not measurable in terms of raw throughput, but even that needs to be carefully evaluated (more complexity implies higher risk of bugs, and higher cost of maintenance).
That's a fundamental trade-off, like everything else - are the benefits of these new features good enough to warrant the added costs (complexity, CPU, or anything else)? Apparently they are.
Regarding inserts, remember that inserts (and updates) generally have two sides: a read side and a write site. The read side (if only, e.g., checking a primary key for duplicates) can potentially be accelerated by the new in-memory structures. Same for join processing that can happen in complex inserts/updates/merges.
The write side itself can't be faster* (since it does what it used to do without the in-memory option, and then some to maintain consistency of the in-memory store), but if you make the read side an order of magnitude faster, the overall processing time can be reduced. By how much is an open question, and will depend on a whole lot of factors.
Also note that if you end up removing indexes, this will have an impact on insert performance for the affected tables. This would be an "indirect" benefit - not a speedup thanks to the in-memory option per se, but because that option allowed you to reduce the number of indexes.
*Never say never. There may be corner cases.