What's New
OceanBase Database V4.3.1 introduces new features and optimizes the performance based on V4.3.0. The new version introduces the full-text indexing feature to improve the retrieval efficiency. It also introduces other new features, such as real-time materialized views, query rewriting based on materialized views, and materialized views with a primary key, to meet the data analysis requirements in various scenarios. Moreover, the new version introduces new partition management mechanisms, such as partition exchange, external table partitioning, and data type extension for partitioning keys in MySQL mode, to significantly improve its capability in processing large-sized datasets.
The new version upgrades the multimodel features that involve the JSON, XML, and geographic information system (GIS) data types. It supports JSON multi-valued indexes and partial updates to further promote the migration and integration of heterogeneous data. To accommodate growing data storage requirements, OceanBase Database V4.3.1 provides the incremental direct load feature, optimizes the DML performance in scenarios with multiple local indexes, and increases the basic statistics collection efficiency. These efforts remarkably improve the performance in row sampling and small-specification transaction processing (TP) scenarios.
In terms of resource utilization, the new version introduces the commit log (clog) caching, SQL query temporary result, and system log compression features to increase resource utilization efficiency. It integrates with the permission system of MySQL and supports operating system configuration checks to enhance system security. As for user experience, OceanBase Database V4.3.1 continues to provide quality services, improves the resource specification estimation capability, enhances backup transparency, and supports IPv6 addresses to facilitate database management and O&M.
The new version enhances the compatibility with MySQL and Oracle Database while ensuring the compatibility with the ecosystem. Specifically, it supports lateral derived tables, locking functions of MySQL, and view comment and remote user-defined functions (UDFs) of Oracle Database, enabling OceanBase Database to seamlessly integrate with the existing ecosystem.
For information about the new version of different OceanBase Database editions, see Release Notes of OceanBase Database Enterprise Edition V4.3.1 and Release Notes of OceanBase Database Community Edition V4.3.1.
The following sections introduce the highlights in the new version.
High performance
Full-text indexing (an experimental feature)
OceanBase Database V4.3.1 provides the full-text indexing feature to effectively simplify the fuzzy query process that involves large amounts of text data. In earlier versions, fuzzy queries of text data rely on full table scans, which demonstrate low efficiency. In the new version, you can create keyword-based indexes to drastically improve the query efficiency.
The full-text indexing feature is in the experimental stage in V4.3.1, and will be enhanced for use in production environments in later versions. This feature aims to address the limitations of conventional B-tree indexes in query scenarios with large amounts of text data, and provides more powerful support for complex query scenarios such as approximate matching and correlation sorting.
For more information about the feature, see Full-text indexing.
Enhancements in materialized view capabilities
OceanBase Database V4.3.1 supports the real-time materialized view feature and provides real-time computing capabilities based on materialized views and materialized view logs (mlogs) to accommodate analytics businesses that demand high real-time performance. The new version supports PRIMARY KEY constraints on materialized views. You can specify a primary key for a materialized view to optimize the performance in scenarios such as single-row query, range query, or association based on the primary key. The new version also supports incremental updates of materialized views in inner joins to improve the materialized view refresh performance in some scenarios.
OceanBase Database V4.3.1 supports automatic rewriting of materialized views. If you create a materialized view and specify ENABLE QUERY REWRITE when the system variable QUERY_REWRITE_ENABLED is set to True, the system can automatically rewrite a table query as a materialized view query to effectively reduce the workload in business modifications.
For more information about how to create a real-time materialized view, see the Create real-time materialized views section in the Create a materialized view (MySQL mode) or Create a materialized view (Oracle mode) topic.
For more information about automatic rewriting of materialized views, see Rewrite queries based on materialized views (MySQL mode) and Rewrite queries based on materialized views (Oracle mode).
Enhancements in partition management
OceanBase Database V4.3.1 provides the partition exchange feature. You can modify the partition and table definitions in the data dictionary to migrate data with minimal delay from one table to a partition in another table without physically replicating the data. This significantly improves the data migration performance.
The external table partitioning feature allows you to manage data in a way similar to LIST partitioning of a regular table and supports automatic and manual partition creation. In automatic partition creation mode, the system groups files by partition based on the definition of the partitioning key. In manual partition creation mode, you need to specify the path to the data file of each partition. In this case, the system implements partition pruning based on the partitioning conditions for an external table query, thereby reducing the number of files to scan and significantly improving the query performance.
Apart from RANGE COLUMNS partitioning keys, OceanBase Database V4.3.1 also supports other partitioning key types in RANGE COLUMNS partitioning, such as columns of the double, float, decimal, and timestamp data types.
For more information about external table partitioning, see External partitioning (MySQL mode), External partitioning (Oracle mode), Exchange partitions (MySQL mode), and Exchange partitions (Oracle mode).
Multimodel features
JSON multi-valued indexes in MySQL mode (an experimental feature)
OceanBase Database V4.3.1 in MySQL mode supports the multi-valued index feature for JSON data. You can create a secondary index on a JSON array of multiple elements. This enhances the capabilities to query complex JSON data structures while ensuring data model flexibility. The multi-valued index feature is in the experimental stage in V4.3.1, and will be enhanced for use in production environments in later versions.
For more information about how to create a multi-valued index, see the Create multi-valued indexes section in the Create an index topic.
Support for new JSON expressions and partial updates of MySQL
The new version supports the JSON_SCHEMA_VALID, JSON_SCHEMA_VALIDATION_REPORT, and JSON_ARRAY_APPEND expressions.
OceanBase Database V4.3.1 supports JSON partial updates. You can use specific expressions, such as json_set, json_replace, and json_remove, to update only partial fields in a JSON document, thereby improving the update performance. For more information about JSON partial update, see JSON partial update.
MySQL GIS enhancements
OceanBase Database V4.1.x already supports GIS data types and some spatial object-related expressions of MySQL. In OceanBase Database V4.3.1, support for the storage, computing, and analysis of spatial data is added. The new version supports MySQL expressions such as ST_Crosses, ST_Overlaps, ST_Difference, ST_Union, ST_SymDifference, ST_Length, ST_Centroid, and ST_AsGeoJSON. To accommodate PostgreSQL database users in the GIS industry, OceanBase Database V4.3.1 supports expressions such as _ST_Touches, _ST_Equals, _ST_MakeEnvelope, _ST_ClipByBox2D, _ST_GeometryType, _ST_IsCollection, _ST_NumInteriorRings, _ST_PointOnSurface, ST_AsMVTGeom, and _ST_AsMVT, as well as the storage of three-dimensional spatial objects.
Oracle JSON enhancements
OceanBase Database V4.2.1 already supports the JSON_OBJECT_T and JSON_ELEMENT_T data types in PL statements. On top of that, OceanBase Database V4.3.1 also supports the JSON_ARRAY_T data type.
New Oracle GIS features
To allow GIS-dependent businesses to conveniently access and analyze spatial data, OceanBase Database V4.3.1 supports the SDO_GEOMETRY data type. The data type can be used to describe the following spatial data types: Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and GeometryCollection. The new version also allows you to use the GET_DIMS(), GET_GTYPE(), and ST_ISVALID() member functions to query the dimension, type, and validity status of SDO_GEOMETRY spatial objects. Moreover, it allows you to use the GET_WKB(), GET_WKT(), and GET_GEOJSON() functions to convert SDO_GEOMETRY objects into the Well-Known-Binary or Well-Known-Text data type, or into a JSON data type.
Compatible XML features
- OceanBase Database V4.3.1 is compatible with the ExtractValue and UpdateXML expressions in MySQL mode.
- OceanBase Database V4.3.1 is compatible with the XMLTABLE, INSERTCHILDXML, DELETEXML, and XMLSEQUENCE expressions in Oracle mode.
Incremental direct load (an experimental feature)
In OceanBase Database V4.3.0, for a table whose data needs to be loaded multiple times, the existing data in the table needs to be written repeatedly during each load. This compromises the incremental load performance. OceanBase Database V4.3.1 provides the incremental direct load feature to improve the incremental load efficiency. Specifically, the database can focus on processing new data without the need to write existing data. This ensures high performance. You can use the /*+ direct(need_sort, max_errors_allowed, load_mode)*/ hint in the LOAD DATA or INSERT INTO SELECT statement to specify whether to enable the incremental direct load feature. You can set load_mode to inc_replace to enable incremental direct load. The feature is in the experimental stage in V4.3.1, and will be enhanced for use in production environments in later versions.
For more information about incremental direct load, see the Incremental direct load section in the Overview topic.
Resource utilization
Log caching
OceanBase Database V4.3.1 supports the clog caching feature. Log consumers can directly read logs from the cache. This avoids repeated disk reads and reduces the bandwidth occupied by the log disk. The 50065, 50066, and 120010 statistical items are added to the SYSSTAT system status table to indicate the number of log cache hits, number of log cache misses, and clog cache size.
Log compression
OceanBase Database V4.3.1 allows you to compress clogs for storage. Compressing logs committed by transactions improves the log disk throughput and increases the total amount of clogs that can be stored in the log disk. It also allows you to compress system logs. When the number of uncompressed system log files reaches the specified value, newly generated system log files will be compressed to save storage space. When the disk space occupied by system logs exceeds the specified value, system log files will be deleted starting from the oldest ones. If you enable zstd compression, the log storage capacity is estimated to increase by 20 times compared to when compression is disabled.
Compression of temporary results of SQL queries
You can use the tenant-level parameter spill_compression_codec or an SQL-level hint, such as /*+opt_param('spill_compression_codec', 'lz4') */, to specify whether to compress temporary results of SQL queries and the compression algorithm. This feature effectively reduces the disk space occupied temporarily, so as to support query tasks with higher workload.
Security
PL privilege management in MySQL mode
OceanBase Database V4.3.1 in MySQL mode provides the Procedural Language (PL) privilege management feature. It provides options such as CREATE ROUTINE, EXECUTE, and ALTER ROUTINE to control the execution of stored procedures and stored functions. The new version provides the mysql.procs_priv internal table to display authorization information about stored procedures and stored functions. This feature is automatically enabled for clusters newly created in the new version. For existing clusters, you need to manually enable the feature.
Column-level privilege management in MySQL mode
OceanBase Database V4.3.1 provides the column-level privilege management feature in MySQL mode. You can specify whether a user has the SELECT, INSERT, or UPDATE privilege on specific columns in a table, contributing to more precise and secure data management.
References
For more information about OceanBase Database V4.3.1, see the following release notes: