OceanBase Database Community Edition V4.3.3

V4.3.3

Overview

OceanBase Database V4.3.3 is the first General Availability (GA) release in the V4.3 series, offering significant advancements in key areas. It introduces support for vector types and indexes for AI-related analysis and processing, enhancing its core relational database capabilities. Additionally, it implements a new form of columnstore replicas, ensuring physical isolation between transaction processing (TP) and analytical processing (AP) resources in hybrid transaction/analytical processing (HTAP) scenarios. This version also boosts the overall performance of AP queries. Furthermore, the update includes support for the complex ARRAY data type, improved computing performance for the RoaringBitmap data type, enhanced query rewriting based on materialized views, extended external table features, better import performance for external tables, and optimized strategies for plan generation and execution of AP SQL statements. These enhancements significantly improve the database's ability to handle online analytical processing (OLAP) workloads. Additionally, the new version offers a quick restore feature, allowing you to restore only logs instead of data to your local server for faster read and write services. It also introduces support for query-level resource groups, enhancing system reliability and usability. Most features from OceanBase Database V4.2.4 and earlier are supported in V4.3.3. An upcoming integrated version will target online transaction processing (OLTP) scenarios.

Key features

Kernel enhancements

• Vector types and indexes (Experimental)

  The rise of AI applications has led to a surge in unstructured data like images, videos, and texts. Embedding algorithms are now able to represent this data as high-dimensional vectors for analysis and processing. This gave rise to vector databases, which are fully managed solutions for handling unstructured data, including storing, indexing, and retrieving embedding vectors. Vector indexes are a key feature, transforming keyword-based searches into vectorized retrievals. This shift turns deterministic searches into similarity searches, enabling the retrieval of large-scale, high-dimensional vectors.

  OceanBase Database V4.3.3 now supports vector type storage, vector indexes, and embedding vector retrieval. It handles float vectors with up to 16,000 dimensions and supports basic operations like addition, subtraction, multiplication, comparison, and aggregation. It also offers precise search and Approximate Nearest Neighbor Search (ANNS) with Hierarchical Navigable Small World Network (HNSW) indexes for up to 2,000 dimensions. This can be applied to Retrieval-Augmented Generation (RAG) for scenarios such as image and video retrieval, behavior preference recommendations, security and fraud detection, and ChatGPT-like applications.

  This feature is in the experimental stage in OceanBase Database V4.3.3, and will be enhanced for production use in later versions.

• ARRAY data type

  ARRAY is a complex data type commonly used in AP business scenarios to store multiple elements of the same type. It is an ideal choice for managing and querying multi-valued attributes that cannot be effectively represented by relational data. Starting from OceanBase Database V4.3.3, the ARRAY data type is supported in MySQL mode. You can define a column as either a numeric or character array, including nested arrays, when creating a table. It supports expressions for querying or writing to array objects, array_contains expressions, and the ANY operator to check if an element is present in the array. Additionally, it supports operators like +, -, =, and != for calculations and comparisons of array elements.

• Read-only columnstore replicas (Experimental)

  OceanBase Database supports columnar storage since V4.3.0. You can create a columnstore table, rowstore table, or hybrid rowstore-columnstore table based on the business type. The replica deployment form is consistent among zones of the same tenant, regardless of the table form. For example, suppose the units of Tenant X are distributed across three zones in 1:1:1 mode. If you create a hybrid rowstore-columnstore table named T1 in Tenant X, the T1 table has a rowstore replica and a columnstore replica in each of the zones. To implement physical isolation between TP resources and AP resources in HTAP scenarios, OceanBase Database V4.3.3 introduces a new deployment form that allows you to add an independent zone for storing read-only columnstore replicas based on the original cluster. In the new zone, all user tables are stored in columnar storage mode. In an AP business scenario, you can use an independent OceanBase Database Proxy (ODP) for database connection and set the session-level system variable ob_route_policy, which specifies the routing strategy, to COLUMN_STORE_ONLY so that you can access only columnstore replicas for queries and analysis in weak-consistency read mode, without affecting TP business. Compared with the hybrid rowstore-columnstore architecture, the 3+1 deployment form can help reduce the storage overhead. This feature must be used in combination with ODP V4.3.2 or later.

  This feature is in the experimental stage in OceanBase Database V4.3.3, and will be enhanced for production use in later versions.

• Enhanced materialized view capabilities

  To reduce the manual rewrite workload, OceanBase Database supports materialized view-based rewriting since V4.3.1. When the system variable QUERY_REWRITE_ENABLED is set to True, you can specify the ENABLE QUERY REWRITE clause in the materialized view creation statement to enable automatic rewriting. After automatic rewriting is enabled, the system can rewrite table queries into materialized view-based queries. OceanBase Database V4.3.1 supports rewriting a table query into a query based on a non-aggregate materialized view as long as the FROM clause in the original query fully matches that in the materialized view and the WHERE condition partially matches that in the materialized view. OceanBase Database V4.3.3 also supports query rewriting in the following scenarios when specific conditions are met: (1) The join order or join type in the FROM clause of the original query is different from that of the materialized view. (2) The original query contains tables not included in the materialized view. (3) The original query needs to be rewritten into a query based on an aggregate materialized view. (4) Aggregation rollup is involved.

  In addition, the new version extends the SQL types supported for incremental refreshes and real-time materialized views. Earlier versions already support incremental refreshes and real-time queries in single-table aggregate and multi-table join scenarios. OceanBase Database V4.3.3 extends the support for incremental refreshes and real-time queries in join aggregate scenarios.

  OceanBase Database supports only rowstore materialized views in earlier versions. OceanBase Database V4.3.3 also supports columnstore materialized views, making it possible to achieve higher query performance in complex analysis scenarios that involve materialized view references.

• Enhanced INSERT OVERWRITE statement

  OceanBase Database supports table-level overwrite based on the INSERT OVERWRITE statement since V4.3.2. Specifically, the database can empty old data and write new data in a table in an atomic manner. This feature applies to AP business scenarios that involve periodic data update, data conversion, data cleansing, and data correction. However, in OceanBase Database V4.3.2, you can overwrite only a whole table, and cannot overwrite the data of specific partitions or columns. In OceanBase Database V4.3.3, you can specify partitions, subpartitions, or columns of the destination table in the INSERT OVERWRITE statement, allowing for more flexibility in data overwriting and making it applicable to a wider range of business scenarios.

• Support for compressed files in LOAD DATA statements and external table queries

  The LOAD DATA statement is commonly used to import text files from a server or client, or in direct load mode to a database. In earlier versions, you can import only normal text files. To import a compressed file such as a GZIP file, you must first decompress the file, which makes the operation complex. In the new version, you can import compressed files in GZIP, DEFLATE, and ZSTD formats. The files are loaded, decompressed, and written to the database simultaneously during the import process. Compressed files are also supported for external tables with external files in the CSV format. You can directly query data from a compressed file in any of the preceding formats by accessing the corresponding external table.

• Partition-wise data export by using the SELECT INTO OUTFILE statement

  OceanBase Database of earlier versions allow you to use the SELECT INTO OUTFILE statement to export multiple files. However, you cannot export data in a partition-wise manner. OceanBase Database V4.3.3 allows you to export data by partition to obtain a clearer directory structure. You can also build a partitioned external table based on the file directories and improve external table query efficiency through partition pruning.

• Distributed late materialization

  In a single-table query, the database usually filters data based on the index table and then queries row data from the primary table to avoid a full-table scan. Each access to the primary table is a random I/O query. If a large amount of data is involved in table access during the execution of an SQL statement, a significant amount of performance resources will be consumed. Take the statement SELECT * FROM t1 WHERE c1 > 1 ORDER BY c2 LIMIT 10; as an example. If a composite index on c1 and c2 exists, the generated execution plan typically filters data that meets the c1 > 1 condition in the index table, then accesses the t1 table to retrieve data, and finally selects the top 10 data records. If a large amount of data is involved in table access, the performance will be poor. However, if you modify the execution plan to first filter data based on the index table, then sort the data and select the top 10 data records, and finally query the primary table for 10 times, the amount of performance resources consumed by random I/Os can be significantly reduced. This optimization strategy is called late materialization. Simply put, late materialization is a strategy where a single-table query sorts the filtered data and limits the number of data records returned before retrieving data from the primary table, thus reducing performance resource consumption by random I/Os.

  OceanBase Database has previously supported the late materialization feature for non-partitioned tables, and this capability has been extended in V4.3.3 to include support for partitioned tables.

• Hyperscan support for regular expressions

  OceanBase Database of earlier versions use International Components for Unicode (ICU) as the underlying regular expression engine. ICU provides full support for unicode character sets and abundant external interfaces. However, its performance is poorer compared to Hyperscan. OceanBase Database V4.3.3 supports Hyperscan as the regular expression engine, which obviously improves the matching performance of regular expressions. However, Hyperscan supports only the x86 architecture and does not apply to the Arm platform. Therefore, Hyperscan is not enabled by default. If the cluster contains only servers of the x86 architecture and demands higher matching performance, you can set the _regex_engine parameter to Hyperscan.

• Asynchronous job scheduling

  OceanBase Database of earlier versions support various data import statements, such as INSERT OVERWRITE, INSERT SELECT, CREATE TABLE AS, and LOAD DATA. You can use these statements to write data to your database in real time. However, real-time data import requires the session to wait until the import is complete and cannot be interrupted during this process, which is inconvenient when a large amount of data is to be imported. To address this issue, OceanBase Database V4.3.3 provides the asynchronous job scheduling capability through the DBMS_SCHEDULER system package. You can use the SUBMIT JOB, SHOW JOB STATUS, and CANCEL JOB statements to respectively create an asynchronous import job, query the job status, and cancel a job.

• Enhanced row estimation and statistics collection

  Cost estimation by the optimizer relies on accurate row estimation for each operator. Accurate row estimation relies on an appropriate estimation strategy and accurate statistics. OceanBase Database of earlier versions have restructured the base table-based and selectivity-based row estimation methods to improve the cost estimation accuracy of the optimizer. OceanBase Database V4.3.3 makes the following improvements for some complex scenarios:

  • Calculation of the joint selectivity in the case of multiple base table predicates: The cardinality_estimation_model system variable is provided for the predicate correlation assumption model to control the base table-based row estimation strategy to be used by the optimizer. The default value is PARTIAL, indicating that the predicates are partially correlated. In this case, the joint selectivity is calculated through exponential backoff.
  • Adjustment of the method for calculating the number of distinct values (NDV): An environment cardinality is maintained during plan generation. The joint NDV of multiple expressions is calculated based on the environment cardinality, thereby affecting the predicate selectivity and the row estimation of the GROUP BY operator.
  • Adaptive statistics correction upon expiration: The number of inserted, deleted, and updated rows is refreshed to the internal table that stores statistics every 15 minutes. By default, a table where the number of rows after data change exceeds 10 times the original number of rows is marked as a table with significantly expired statistics, and its statistics will be adaptively corrected. For example, an asynchronous statistics collection job is initiated every 15 minutes at the background for tables with significantly expired statistics, or dynamic sampling is used to ensure that appropriate execution plans are generated.
  • Dynamic sampling supported for more complex predicate scenarios: For common complex predicates of base tables, dynamic sampling can be used to calculate a more accurate selectivity of complex predicates, even in scenarios where statistics are effective.

  OceanBase Database V4.3.3 also optimizes the sampling strategy for histogram collection. Specifically, histogram collection is decoupled from basic statistics collection, the hist_est_percent parameter is provided to specify the sampling ratio of histogram collection, and the hist_block_sample parameter is provided to specify whether to use block sampling for histogram collection. To optimize the histogram collection performance, the new version selects a sampling strategy for histogram collection based on the table size. Row sampling is used for a small table, and block sampling is used for a large table.

Compatibility with MySQL

• SHOW CREATE USER

  OceanBase Database V4.3.3 supports the syntax of the SHOW CREATE USER statement in MySQL, which is used to display the information about database users. The administrator or a user with the SELECT privilege on the oceanbase or mysql database can view the information about all users in the tenant, whereas a normal user can view only its own information.

• Value assignment to a system variable through a subquery

  In earlier versions, OceanBase Database allowed the use of subqueries to assign values to user variables. With V4.3.3, it now supports using subqueries to assign values to system variables for MySQL compatibility.

• [Patch from V4.2] Support for more character sets and collations

  Apart from character sets supported in earlier versions, OceanBase Database V4.3.3 also supports the ASCII character set (with ascii_bin and ascii_general_ci collations) and TIS620 character set (with tis620_bin and tis620_thai_ci collations). Moreover, the new version also supports the utf8mb4_unicode_520_ci, utf8mb4_croatian_ci, utf8mb4_czech_ci, and utf8mb4_0900_ai_ci collations for the utf8mb4 character set, and supports the utf8mb3 character set as the alias of the utf8mb4 character set.

• Expressions as default values

  The new version supports using an expression that does not rely on other columns as the default value of a column, for example, DEFAULT(DATE_FORMAT(sysdate(), '%Y%m%d')), DEFAULT(CURRENT_DATE), and DEFAULT(UNIX_TIMESTAMP()).

• CHECK TABLE statement

  The new version partially supports the syntax of the CHECK TABLE statement. In the new version, you can use the statement to check whether a table or view exists in the database or whether objects referenced in a view are valid. Other features of the statement are not supported.

Performance improvements

• Performance improvement for the RoaringBitmap data type

  OceanBase Database V4.3.2 has supported the RoaringBitmap data type and related expressions to meet multidimensional analysis requirements in business scenarios such as user profiling, personalized recommendation, and precise marketing. However, the performance is poor in some scenarios. OceanBase Database V4.3.3 focuses on analyzing the performance issues of RoaringBitmap data computations and significantly improves the execution performance in cardinality, AND, OR, XOR, ANDNOT, and aggregate scenarios by optimizing memory application and expression execution logic to reduce unnecessary performance overhead.

• Performance improvement for external table import

  OceanBase Database V4.3.3 optimizes execution performance in the phase of reading data from external tables during direct load, improving import performance by about 15% compared to the previous version.

Reliability improvements

• Quick restore

  In OceanBase Database of earlier versions, physical restore is a process of restoring the full data. A physical restore is completed only after all the data (minor compaction data and baseline data) and logs are restored. Then, you can log in to and use the restored tenant. If a large amount of data is to be restored to a tenant, the restore will take a long time and you need to reserve sufficient disk space for the tenant at the very beginning to ensure a successful restore. In some scenarios, a tenant is restored only for query and verification purposes and will be destroyed later. If only a few tablets are involved during the query, a full restore costs too high and is a waste of storage space, time, and network bandwidth. The new version provides a quick restore feature that allows you to provide read and write services by restoring only logs rather than data to your local server. In addition, the data backup feature allows you to build an intermediate-layer index for a backup SSTable based on the backup address. With this index, OBServer nodes can randomly read data from the backup SSTable like reading local data.

• Automatic routing to the primary/standby tenants

  OceanBase Database V2.x and V3.x support primary and standby clusters. A primary/standby cluster pair is uniquely identified by the cluster name (CLUSTER_NAME). If you use ODP to connect to an OceanBase cluster, you are automatically routed to the primary cluster based on the cluster name. OceanBase Database V4.x supports primary and standby tenants. The primary and standby tenants do not record the information of each other. The primary/standby relationship is maintained by using external tools such as OCP. In this case, you cannot be automatically routed to the primary tenant if you use ODP for database connection. To address this issue, the new version allows you to manage a pair of primary/standby tenants by service name (SERVICE_NAME). When you use ODP for database connection, you can specify the service name in the command for login, for example, obclient -h $ip -P $port -u$user_name@SERVICE:$service_name. This way, ODP can route your connection to the primary tenant based on the service name. Moreover, the sys tenant provides a set of service name management commands. You can create, enable, disable, or delete a service name.

  The service name feature is supported in ODP V4.3.1 and later and OCP V4.3.1 and later.

• Verification of primary/standby tenant switchover and failover

  OceanBase Database supports primary/standby tenant switchover in scenarios without data loss and primary/standby tenant failover in scenarios with data loss. The switchover and failover can fail. Therefore, to reduce the risks of failures, OceanBase Database V4.3.3 supports the verification feature (SWITCHOVER/FAILOVER VERIFY). You can add the VERIFY keyword after the switchover or failover command to verify in advance whether the operation can be performed. If the operation cannot be performed, an error is returned. In this case, you can take the suggested actions.

Resource optimization

• [Patch from V4.2] Memory usage optimization for SQL_AUDIT

  The SQL_AUDIT view records information about the SQL statements executed on each server of the current tenant. A queue of pointers to 10 million records is constructed for a normal tenant, based on which memory is preallocated to tenant servers upon their startup. The preallocated memory is called static memory. However, dynamic memory is actually used by records. The new version optimizes the memory structure for SQL_AUDIT. Specifically, the size of initial static memory is reduced to adapt to small-scale tenants. You can also dynamically adjust the size of queue memory based on the actual number of records, allowing a tenant with high specifications to store more records.

Security enhancements

• [Patch from V4.2] Compatibility with the REFERENCES, CREATE ROLE, DROP ROLE, and TRIGGER privileges of MySQL

  OceanBase Database V4.3.3 is compatible with the REFERENCES, CREATE ROLE, DROP ROLE, and TRIGGER privileges of MySQL. You can grant the REFERENCES privilege at the global, database, table, or column level. This privilege is required to create a foreign key constraint during table creation or table modification. Additionally, you can configure the REFERENCES privilege to not take effect at the column level, which is compatible with MySQL. The CREATE ROLE and DROP ROLE privileges take effect globally. The CREATE USER or CREATE ROLE privilege is required to create a role. The CREATE USER or DROP ROLE privilege is required to drop a role. You can grant the TRIGGER privilege at the global, database, or table level. This privilege is required to create, drop, execute, or display triggers in a table.

  By default, verification is disabled for the preceding privileges in upgrade scenarios to avoid affecting online business after the upgrade. If these privileges are required in the new version, you can use the ob_security_version variable to specify a security version, which cannot be rolled back to V4.3.2 once it is set to V4.3.3. By default, verification is enabled for the preceding privileges in a new cluster.

• Security audit

  Cloud consoles support database audit based on SQL statistics recorded in the SQL_AUDIT view. However, in the case of high business traffic, some statistics may not be collected if the SQL_AUDIT view is refreshed frequently. OceanBase Database V4.3.3 provides a new database audit feature. You can use filters to specify the types of requests to be audited, such as login, logout, DML statements, and CMD commands. Corresponding records are persisted to the local disk or audit files of Alibaba Cloud Object Storage Service (OSS), and are rotated, deleted, and compressed based on the configured strategy. You can read audit records in cloud consoles, or directly read audit records in the database by using the external table feature of OceanBase Database.

Usability improvements

• Query-level resource groups

  OceanBase Database of earlier versions allow you to use the DBMS_RESOURCE_MANAGER system package to configure resource groups at the user level, background task level, or column parameter level, to implement CPU and IOPS resource isolation. OceanBase Database V4.3.3 supports query-level resource group binding. You can specify the /*+ resource_group('group_name')*/ hint in an SQL statement to force this statement to use resources in the specified resource group. If the specified resource group does not exist, resources in the default resource group are used. After the resource group is changed, you need to reconnect the session for the new resource group to take effect.

• More parameters and variables supported for the OPT_PARAM hint

  You can use the OPT_PARAM hint to specify optimizer-related parameters or system variables to be updated at the query level. Apart from those supported in earlier versions, OceanBase Database V4.3.3 also supports the optimizer_index_cost_adj parameter and the runtime_filter_type system variable.

• LS replica management

  OceanBase Database of versions earlier than V4.0 provide a series of O&M commands for partition replica management. For example, you can use related commands to add a replica to a partition, drop a replica from a partition, and convert the type of a replica for a partition. OceanBase Database V4.x replaces the concept of partition with log stream (LS). OceanBase Database V4.3.3 redesigns the partition O&M commands in earlier versions for LS replica-level O&M. It provides a series of syntaxes for adding LS replicas, dropping LS replicas, converting the type of LS replicas, migrating LS replicas, modifying the number of Paxos members of an LS replica, and canceling a disaster recovery task, to meet manual LS replica O&M requirements.

• Enhanced PS diagnostic capabilities

  When a prepared statement (PS) handle leak occurs in earlier versions, you can only query the GV$OB_PS_ITEM_INFO view for global information, and no session-level diagnostic methods are available. OceanBase Database V4.3.3 provides the [G]V$OB_SESSION_PS_INFO view to display the PS reference information of each session to help you accurately locate PS handle leaks.

• Support for querying data from the [G]V$PLAN_CACHE_PLAN_EXPLAIN view only by plan_id

  When you query data from the [G]V$PLAN_CACHE_PLAN_EXPLAIN view in earlier versions, you must specify ip, port, tenant_id, and plan_id as the filter conditions. If you specify only plan_id, the query result set is empty, which means poor usability of the view. The new version supports scanning the underlying virtual table of this view and allows you to specify only plan_id to accurately query data from this view.

• trace_id parsing

  OceanBase Database uses trace_id to mark the full process of an SQL request. trace_id can be associated with monitoring metrics or query logs and contains the IP address and port number of the OBServer node that initiates the SQL request. However, no method is available for directly parsing trace_id. The new version provides the decode_trace_id function for parsing trace_id to obtain the IP address and port number.

• Display of events of primary and standby tenants

  In OceanBase Database of earlier versions, events of primary and standby tenants such as switchover and failover are recorded in RootService events, which are cleared over time. Moreover, it is difficult to find tenant-level events among cluster-level events. OceanBase Database V4.3.3 splits the events of primary and standby tenants at the tenant level and displays the events in the CDB/DBA_OB_TENANT_EVENT_HISTORY view.

Compatibility changes

Product behavioral changes

View changes

Parameter changes

System variable changes

System package changes

Function changes

Syntax changes

Recommended versions of platforms and tools

The following table lists the recommended versions of platforms and tools for OceanBase Database V4.3.3_CE.

Upgrade notes

• OceanBase Database V4.3.0 Beta and its later minor versions, V4.3.1 Beta and its later minor versions, as well as V4.3.2 Beta Hotfix1 and its later minor versions can be directly upgraded to V4.3.3.
• OceanBase Database V4.2.x or earlier cannot be upgraded to V4.3.3. As the version evolves, corresponding upgrade paths will be supported.
• OceanBase Database V4.3.2 reconstructs the persistent format of multi-source data, and therefore format conversion needs to be performed for multi-source data during the upgrade. You must reserve sufficient upgrade time if a large number of partitions are involved.
• During an upgrade to V4.3.3, make sure that no incremental direct load task is in progress. You can query the oceanbase.__all_virtual_load_data_stat view for confirmation. We plan to resolve this issue in later versions.

Considerations

• OceanBase Database V4.3.x introduces some Advanced Vector Extensions 2 (AVX2) instructions. If the CPU of a server does not support these instructions, the server may crash, generating a core dump file.
• If x86 servers that support AVX2 instructions are co-deployed with Arm servers or x86 servers that do not support AVX2 instructions, accuracy issues may occur during backup and restore.
• The OceanBase Change Data Capture (CDC) process may crash, generating a core dump file, when the CDC and table-level restore features are used together.