Memory-Based Streaming Tables for Lower-Overhead Change Capture
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
The high cost and overhead associated with writing, storing, and deleting data in log-adjunct tables in database management systems (DBMSs, particularly for field audit logs, due to the volume of data generated and the rarity of queries on these logs, lead to inefficient resource usage and significant overhead.
Innovation Solution
The implementation of streaming tables and columns in DBMSs, where data is stored in memory during transactions and only streamed to external services as change logs, eliminating the need for persistent storage and reducing resource consumption.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If data is written to and stored in log-adjunct tables for change log capture, then data can be streamed to external systems, but storage overhead and resource consumption increase significantly
Solution Approach 1:
The patent extracts the change log capture function from traditional persistent storage by introducing memory-based streaming tables. Change log records are generated and streamed to external systems through memory buffers rather than being written to permanent storage, thereby eliminating the need for large storage volumes while maintaining reliable data capture.
Solution Approach 2:
The invention uses temporary memory structures (streaming tables) that exist only during the transaction scope. These memory-based tables are created when needed, used for capturing changes, and automatically discarded after the transaction completes, eliminating the need for persistent storage of audit data.
2Reliability
If data is written to and deleted from log-adjunct tables, then change log capture is maintained, but computational overhead and processing cost increase
Solution Approach 1:
The patent extracts the change log generation function from the traditional write-delete cycle by using memory-based streaming tables. Change log records are generated during transactions and streamed to external systems through memory buffers, eliminating the need for subsequent deletion operations and reducing computational overhead.
Solution Approach 2:
The invention maintains continuous change log capture through memory-based streaming tables that operate throughout the transaction scope. Change records are continuously streamed to external systems as they are generated, eliminating the intermittent write-delete cycle and improving processing efficiency.
3Reliability
If detailed audit information is stored in field audit tables, then compliance requirements are met, but data volume and system overhead increase
Solution Approach 1:
The patent extracts the audit information capture function from persistent storage by using memory-based streaming tables. Detailed audit information is captured and streamed to external systems through memory buffers, eliminating the need for large persistent storage structures and reducing system overhead while maintaining complete audit information.
Solution Approach 2:
The invention uses temporary memory structures for audit information storage that are automatically discarded after transaction completion. These memory-based tables provide detailed audit tracking without the long-term storage requirements and associated system overhead of traditional persistent storage solutions.
4Adaptability or versatility
If log-adjunct tables are used for change log capture, then data streaming capability is provided, but writing and managing data incurs high cost
Solution Approach 1:
The patent extracts the data streaming function from traditional log-adjunct tables by using memory-based streaming tables. Change log records are generated and streamed to external systems through memory buffers, eliminating the need for expensive write operations to persistent storage and reducing overall resource consumption.
Solution Approach 2:
The invention uses temporary memory structures that are inexpensive compared to persistent storage. These memory-based tables provide data streaming capability without the high cost of writing and managing data in permanent storage systems, as the memory structures are automatically discarded after use.
Data Source
AI summary
As with database tables in general, a streaming table and its columns are defined within by a database. DML commands are issued against a streaming table that specify to insert rows and values into their defined columns. Change records in a change log (e.g., redo log) are generated to record the changes specified by DML commands. The change log may be used to stream data captured by the change log. However, unlike with database tables in general, the rows of a streaming table are not persistently stored in the database at commit time. Alternatively, rows are stored persistently but without values for columns defined as streaming columns. Columns defined as streaming columns may be set to values specified by DML commands but are not stored in the database; streaming column values are captured in change logs, however.