OceanBase Database

SQL - V2.2.77Enterprise Edition

OceanBase Introduction
- What is OceanBase Database?
- Benefits
- System architecture
- Features
- Compatibility with Oracle
  - Compatibility with Oracle Database
  - SQL data types
  - Built-in functions
  - System views
- Compatibility with MySQL
- Scenarios
- Technical standards
- Related services
OceanBase Database Overview
- Database introduction
  - Overview
  - Overall architecture
  - Basic concepts
- Data distribution
  - Cluster management
  - Tenant and resource management
  - Data distribution
  - Replicas and services
  - Data balancing
- Data model
  - Multi-tenant architecture
  - Compatibility modes
  - System tenant
  - General tenant
  - Table and table group
  - Secondary index
  - Tables without a primary key
  - Views
- High availability
  - High availability solution
  - Deployment modes
  - REDO log management
- Transaction management
  - Isolation level
  - Concurrency control
  - GTS
  - Local transaction
  - Distributed transaction
  - Distributed query
- Storage architecture
  - Overview
  - LSM-tree architecture
  - MemTable
  - Block storage (SSTable)
  - Minor compaction and major compaction
  - Caching
  - Read and write process
  - DDL
- SQL engine
  - Execution process of SQL queries
  - Query rewrite
    - Overview
    - Rule-based query rewrite
    - Cost-based query rewrite
  - Query optimization
    - Access path
      - Overview
      - Rule-based path selection
      - Cost-based path selection
    - Join algorithms
      - Overview
      - Join algorithms
      - Join order
  - SQL execution plan
    - Introduction to SQL execution plan
    - Execution plan operators
      - TABLE SCAN
      - TABLE LOOKUP
      - JOIN
      - COUNT
      - GROUP BY
      - WINDOW FUNCTION
      - SUBPLAN FILTER
      - DISTINCT
      - SEQUENCE
      - MATERIAL
      - SORT
      - LIMIT
      - FOR UPDATE
      - SELECT INTO
      - SUBPLAN SCAN
      - UNION
      - INTERSECT
      - EXCEPT/MINUS
      - INSERT
      - DELETE
      - UPDATE
      - MERGE
      - EXCHANGE
      - GI
    - Plan cache
    - Fast parameterization
    - Real-time execution plan display
  - Distributed execution plan
    - Distributed execution and parallel query
    - Generate a distributed plan
    - Schedule distributed execution plans
    - Manage distributed execution plans
    - Execution of parallel queries
    - Parameter tuning for a parallel query
- Client access
  - Data link
  - ODP
  - Database driver
- Backup and recovery
  - Overview
  - Backup architecture
  - Restoration architecture
  - Backup set
  - Archive log round
- Primary and standby databases
  - Overview
  - Typical scenarios
  - Protection mode
  - Role switchover
  - Log transfer service
  - Primary/standby database configuration
Deploy OceanBase Database
- Deployment introduction
- Deployment process
- Preparations before deployment
  - Prepare servers
  - Server configuration requirements
  - Prepare the installation packages
- Configure the deployment environment
  - Configure through the CLI
    - Install OAT-CLI
    - Use OAT-CLI to configure the deployment environment
    - Configure the clock source
    - Upload the installation packages
    - Configure the host information
    - Check the environment before deployment
  - Configure through the graphical interface
    - Deploy OAT
    - Verification after deployment
    - Configure the clock source
    - Upload the installation packages
    - Add a server
    - Initialize the server
- Deploy OCP
  - Deployment description
  - Deploy through the CLI
    - Generate a configuration file
    - Start deployment
  - Deploy through the GUI
    - Add an OCP configuration file
    - Deploy OCP
  - Check after deployment
- Deploy the OceanBase cluster
  - Configure DNS (optional)
  - Add an OBServer to the OCP resource pool
  - Create an OceanBase cluster
- Deploy OBProxy
  - Deployment description
  - Deploy OBProxy
- Create an OceanBase tenant
  - View available resources of a business tenant
  - Create a tenant
  - Verification after deployment
- Deploy OMS
  - Deployment description
  - Deploy through the CLI
    - Modify the configuration file
    - Start deployment
  - Deploy through the GUI
    - Add an OMS configuration file
    - Deploy OMS
  - Check after deployment
- Deploy ODC
  - Deployment description
  - Deploy through the CLI
    - Modify the configuration file
    - Start deployment
  - Deploy through the GUI
    - Add an ODC configuration file
    - Deploy ODC
  - Check after deployment
Quick Start
- Overview
- Connect to OceanBase Database
  - Connect to a MySQL tenant from a MySQL client
  - Use OBClient to connect to an OceanBase Database tenant
- Basic operations (MySQL)
  - Database operations
  - Table operations
  - Index operations
  - Insert data
  - Query data
  - Update data
  - Delete data
  - Commit a transaction
  - Roll back a transaction
- Basic Operations (Oracle)
  - Table-level operations
  - Index operations
  - Insert data
  - Query data
  - Update data
  - Delete data
  - Commit a transaction
  - Roll back a transaction
Administrator Guide
- Basic database components
- Database management tools
  - Management tools overview
  - OBClient
  - MySQL client
  - OceanBase Cloud Platform
  - OceanBase Developer Center
- Connect to OceanBase Database
  - Connect to a MySQL tenant from a MySQL client
  - Use OBClient to connect to an OceanBase Database tenant
- View the database version
- Cluster and multi-tenant management
  - Cluster management
    - Overview of cluster management
    - Cluster management operations
  - Zone management
    - Overview of zone management
    - Add or delete zones
    - Start or stop a zone
    - Modify a zone
  - OBServer management
    - Overview of OBServer management
    - View OBServer status
    - Stop an OBServer
    - Start an OBServer
    - Manage OBServer status
  - Resource management
    - Overview of resource management
    - Create a resource unit
    - View resource units
    - Modify resource units
    - Delete resource units
    - Create a resource pool
    - Check resource configurations
    - Modify a resource pool
    - Delete a resource pool
  - Tenant management
    - Overview of tenant management
    - Create a tenant
    - View tenants
    - Modify a tenant
    - Delete a tenant
    - View tenant sessions
    - Terminate a tenant session
    - Tenant management variables
  - Memory management
    - Overview of memory management
    - Memory structure of OceanBase Database
    - Maximum memory for OceanBase Database
    - Internal system memory management
    - Internal tenant memory management
    - Plan cache
    - Common memory issues
- Database object management
  - Manage tables
    - About tables
    - Create a table
    - Define an auto-increment column
    - Define column constraints
    - View table definition
    - Modify a table
    - Empty a table
    - Drop a table
  - Manage table groups
    - About table groups
    - Table group management statements
  - Manage indexes
    - About indexes
    - Create an index
    - View an index
    - Delete an index
  - Manage views, sequences, and synonyms
    - Manage views
    - Manage sequences
    - Manage synonyms
  - Manage triggers
    - Overview
    - Create a DML trigger
    - Modify and drop a trigger
    - Disable and enable a trigger
    - Delete a trigger
    - View trigger information
- Data distribution and path management
  - Manage partitioned tables and partitioned indexes
    - About partitions
    - Partitioning strategies
    - Create a partitioned table
      - Partitioned tables
      - Subpartitioned tables
    - Partition pruning
    - Name and query a partition
    - Create an index on a partitioned table
      - Local index
      - Global index
      - Use an index
    - Maintain partitioned tables
      - Partitioned tables
      - Subpartitioned tables
  - Manage replicas
    - Overview of replicas
    - Use table replicas
  - Locality management
    - Overview of locality management
    - Modify tenant locality
  - OBProxy management
    - OBProxy management overview
    - OBProxy management operations
      - Start and stop OBProxy
      - Slow queries in OBProxy
      - OBProxy O\&M practices
    - Routing feature
      - SQL parser
      - LDC routing
      - Read/write splitting
      - Follower-first read
      - Blacklist
    - Load-balancing
    - Manage drivers
- Manage transactions
  - Overview of transactions
  - Commit a transaction
  - Roll back a transaction
  - Levels of transaction isolation
- Data storage management
  - Major compaction management
    - Major compaction management overview
    - Automatically trigger a major compaction
    - Manually trigger major compaction
    - View major compactions
    - Major compaction timeout and errors
    - Major compaction control parameters
  - Minor compaction management
    - Minor compaction management overview
    - Automatically trigger a minor compaction
    - Manually trigger a minor compaction
    - View minor compactions
    - Control parameters of minor compactions
  - Data compression
    - Data compression overview
    - Data encoding and compression
- Data migration
  - Data migration overview
  - Migrate data by using SQL scripts
  - Migrate data by using MySQLDump
  - Migrate data by using DataX
  - Migrate data by using OUTFILE statements
  - Migrate data by using OMS
- Manage user privileges
  - Users and privileges
  - Create a user
  - Manage roles
  - Modify user privileges
  - View the whitelist
  - Lock and unlock a user
  - Delete a user
- Data monitoring and alerts
  - Monitoring alert overview
  - Monitoring
    - Status monitoring
    - Performance monitoring
    - Common performance monitoring metrics
    - SQL monitoring
  - Alerts
    - Alert configuration
    - Alert subscription
    - Alert message management
    - Common alert item management
- Data high availability
  - Recycle bin management
    - Objects supported by the recycle bin
    - Recycle bin for databases, tables, and indexes
    - Recycle bin for tenants
  - Flashback queries
  - Restore points
  - Manage backup and restoration
    - Introduction to physical backup and restoration
    - Deploy NFS
    - Data backup
      - Backup by using commands
      - Enable backup based on log splitting
      - Change the backup destination during backup
      - View the backup progress
      - Stop a backup
      - Automatically clear backup data
      - Manually clear backup data
      - Cancel backup clearing
    - Secondary backup (cluster level)
      - Overview of secondary backup
      - Perform secondary backup
      - Automatically clear secondary backup data
      - Manually clear secondary backup data
    - Secondary backup (tenant level)
    - Restore data
      - Perform full restoration
      - View the full restoration progress and results
      - Restore a specified table
    - Restore secondary backup data
    - Routine maintenance
      - Backup-related maintenance
      - Operations related to secondary backup
  - Primary/Standby cluster management
    - Primary/Standby cluster configuration overview
    - Deploy the primary and standby clusters
      - Configuration requirements
      - Deploy a primary cluster
      - Check the configurations of the primary cluster
      - Deploy a standby cluster by using SQL commands
    - Tenant management
    - Log transfer service
      - Overview
      - Configure log transfer parameters for a standby cluster
      - Configure log compression
    - Protection mode
      - Protection mode overview
      - Protection mode and protection level
      - Switch the protection mode
    - Role switching
      - Switchover and failover
      - Preparations for a switchover
      - Switchover
        Switchover by using commands
      - Failover
        Preparations for a failover
        Lossless failover
        Connect the original primary cluster after a lossless failover
        Lossy failover
        Restore the original primary cluster after a lossy failover
        Correct the data after a failover
    - Standby cluster read service
    - Delete a standby cluster
    - Precautions and use constraints
    - Troubleshooting
      - Failover-related errors
      - Switchover errors
      - Protection mode switching errors
      - Tenant creation for a standby cluster got stuck
      - Data synchronization enablement errors
- Exception handling
  - Exception handling overview
  - Exception handling logic
  - Handle major compaction exceptions
  - Handle load exceptions
  - Handle tenant memory overrun exceptions
- Data security management
  - Data transmission encryption
    - Overview
    - Transmission encryption of OBServers
    - Transmission encryption of ODP
  - Set row-level access control
  - Audit databases
    - Overview
    - Enable audit
    - Audit user logon operations
    - Audit user operations
    - Audit specified database objects
Developer Guide (MySQL Mode)
- Foreword
  - About OceanBase Database developers
  - Overview
  - About OceanBase Database
    - Overview of OceanBase clusters
    - Introduction to OceanBase tenants
    - Database objects in a MySQL tenant of OceanBase Database
    - About how to access OceanBase Database
      - About MySQL clients (mysql)
      - About OceanBase client OBClient
      - About SQL
      - JDBC driver
      - OceanBase Java database connection driver
  - About sample database TPCC
- Connect to OceanBase Database
  - Connect to an OceanBase tenant through a MySQL client
  - Connect to an OceanBase tenant through OBClient
  - Connect to OceanBase Database through ODC
  - Create OceanBase sample database TPCC
  - Explore MySQL tenants of OceanBase Database through OBClient
  - Query table data
    - About query statements
    - Search for data that satisfies specified conditions in a table
    - Sort query results
    - Query data from multiple tables
    - Use operators and functions in a query
      - Use arithmetic operators in queries
      - Use numeric functions in queries
      - Use string connectors in a query
      - Use string functions in queries
      - Use date and time functions in queries
      - Use type conversion functions in queries
      - Use aggregate functions in queries
      - Use NULL-related functions in queries
      - Use CASE functions in queries
      - Use the SELECT FOR UPDATE statement to lock query results
    - View the query execution plan
    - Use SQL hints in queries
    - About query timeout design
- DML statements and transactions
  - About DML statements
    - Overview of DML statements
    - About the INSERT statement
    - About the UPDATE statement
    - About the DELETE statement
    - About the REPLACE INTO statement
  - About transaction control statements
  - Commit a transaction
  - Roll back a transaction
  - Transaction savepoints
  - About transaction timeout
- Create and manage database objects
  - About DDL statements
  - Create and manage tables
    - About SQL data types
    - Create a table
    - About auto-increment columns
    - About types of column constraints
    - About table indexes
    - Flash back a deleted table
  - Create and manage partitioned tables
    - Overview
    - Partition routing
    - Partitioning strategies
    - Indexing on partitioned tables
    - Recommendations for using partitioned tables
  - Create and manage table groups
    - About table groups
    - Specify a table group when creating a table
    - View information about a table group
    - Add a table to a table group
    - Delete a table group
  - Create and manage views
    - Create a view
    - Modify a view
    - Delete a view
- Migrate data to OceanBase Database
  - About data migration and synchronization
  - DataX
  - Examples of DataX reader and writer plug-ins for different data sources
  - OceanBase data loading methods
- Migrate data to OceanBase Database
  - Example of connecting a Java application to OceanBase Database
  - General OceanBase parameters and variables
  - General SQL hints for OceanBase
Developer Guide (Oracle Mode)
- Foreword
  - About OceanBase Database developers
  - Overview
  - About OceanBase Database
    - Overview of OceanBase clusters
    - Introduction to OceanBase tenants
    - Database objects in a MySQL tenant of OceanBase
    - Database objects in an Oracle tenant of OceanBase
    - About oceanbase database access methods
      - About MySQL clients (mysql)
      - About OceanBase client OBClient
      - About ODC
      - About SQL
      - JDBC driver
      - OceanBase JDBC driver
  - About sample database TPCC
- Connect to OceanBase Database
  - Connect to an OceanBase tenant through OBClient
  - Connect to OceanBase Database through ODC
  - Create OceanBase sample database TPCC
  - Explore Oracle tenants of OceanBase through OBClient
  - Explore Oracle tenants of OceanBase through ODC
  - Query table data
    - About query statements
    - Search for data that satisfies specified conditions in a table
    - Sort query results
    - Query data from multiple tables
    - Use operators and functions in a query
      - Use arithmetic operators in queries
      - Use numeric functions in queries
      - Use string connectors in a query
      - Use string functions in queries
      - Use date and time functions in queries
      - Use type conversion functions in queries
      - Use aggregate functions in queries
      - Use NULL-related functions in queries
      - Use CASE functions in queries
      - Use the DECODE function in queries
      - Use complex analytic functions in queries
    - View the query execution plan
    - Use SQL hints in queries
    - About query timeout design
    - About Flashback query
- About DML statements and transactions
  - About DML statements
    - Overview of DML statements
    - About the INSERT statement
    - About the UPDATE statement
    - About the MERGE statement
    - About the DELETE statement
  - About transaction control statements
  - Commit a transaction
  - Roll back a transaction
  - Transaction savepoints
  - About transaction timeout
- Create and manage database objects
  - About the DDL statement
  - Create and manage tables
    - About SQL data types
    - Create a table
    - About auto-increment columns
    - About types of column constraints
    - About table indexes
    - Flash back a deleted table
  - Create and manage partitioned tables
    - Overview of partitioning
    - Partition routing
    - Partitioning strategies
    - Indexing on partitioned tables
    - Recommendations for using partitioned tables
  - Create and manage table groups
    - About table groups
    - Specify a table group when creating a table
    - View information about a table group
    - Add a table to a table group
    - Delete a table group
  - Create and manage views
    - Create a view
    - Modify a view
    - Delete a view
  - Create and manage sequences
    - Overview
    - Create and use sequences
    - Modify sequence attributes
    - Delete a sequence
  - Create and manage synonyms
    - Overview
    - Create a synonym
    - Delete a synonym
- Work in globalized environments
  - Globalization support features
  - Default NLS parameters
  - View NLS parameters
  - Modify NLS parameters
  - Use Unicode in internationalized programs
- Build extensible applications
  - Extensible applications
  - Use bind variables to improve the extensibility
  - Use PL to improve the performance
  - Concurrency and extensibility
  - Limit the number of concurrent sessions
  - Production environment data processing techniques
- Migrate data to OceanBase Database
  - Data migration and synchronization
  - DataX
  - Examples of DataX reader and writer plug-ins for different data sources
  - OceanBase data loading methods
- Appendix
  - Example of connecting a Java application to OceanBase Database
  - General OceanBase parameters and variables
  - Common SQL Hints for OceanBase Database
SQL Reference (MySQL Mode)
- Elements
  - Data type
    - Data type overview
    - Numeric data types
    - Date and time data types
    - Character data types
    - Large-object data types
  - Expressions
  - Type conversion
  - Character sets
  - Collations
  - Data comparison rules
  - Literals
  - Comments
- Operators
  - Arithmetic operators
  - Bitwise operators
  - Comparison operators
  - Logical operators
  - Date and time operators
  - Concatenation operators
  - Hierarchical query operators
  - Collation operators
- Functions
  - Single-row functions
    - Date and time functions
    - String functions
    - Conversion functions
    - Mathematical functions
    - Comparison functions
    - Process control functions
  - Aggregate functions
  - Analytic functions
  - Information functions
  - Other functions
- Queries and subqueries
  - Overview
  - JOIN
  - Set
- SQL statements
  - General syntax
  - ALTER DATABASE
  - ALTER OUTLINE
  - ALTER RESOURCE POOL
  - ALTER RESOURCE UNIT
  - ALTER SYSTEM
  - ALTER TABLE
  - ALTER TABLEGROUP
  - ALTER TENANT
  - ALTER USER
  - CREATE DATABASE
  - CREATE INDEX
  - CREATE OUTLINE
  - CREATE RESOURCE POOL
  - CREATE RESOURCE UNIT
  - CREATE RESTORE POINT
  - CREATE SYNONYM
  - CREATE TABLE
  - CREATE TABLEGROUP
  - CREATE TENANT
  - CREATE USER
  - CREATE VIEW
  - DELETE
  - DROP DATABASE
  - DROP INDEX
  - DROP OUTLINE
  - DROP RESOURCE POOL
  - DROP RESOURCE UNIT
  - DROP RESTORE POINT
  - DROP TABLE
  - DROP TABLEGROUP
  - DROP TENANT
  - DROP SYNONYM
  - DROP USER
  - DROP VIEW
  - EXPLAIN
  - FLASHBACK DATABASE
  - FLASHBACK TABLE
  - FLASHBACK TENANT
  - GRANT
  - INSERT
  - KILL
  - PURGE DATABASE
  - PURGE INDEX
  - PURGE RECYCLEBIN
  - PURGE TABLE
  - PURGE TENANT
  - RENAME TABLE
  - RENAME USER
  - REPLACE
  - REVOKE
  - SAVEPOINT
  - SCHEMA
  - SELECT
  - SESSION
  - SET PASSWORD
  - SHOW GRANTS
  - SHOW RECYCLEBIN
  - TRANSACTION
  - TRUNCATE TABLE
  - UPDATE
SQL Reference (Oracle Mode)
- SQL overview
- Pseudocolumns
- Elements
  - Built-in data types
    - Overview of built-in data types
    - Character data types
      - Overview of character data types
      - CHAR data type
      - NCHAR data type
      - NVARCHAR2 data type
      - VARCHAR2 data type
      - VARCHAR data type
    - Numeric data types
      - Overview of numeric data types
      - NUMBER data type
      - FLOAT data type
      - Floating-point numbers
      - Numeric precedence
    - Date, time, and interval data types
      - Overview of datetime and interval data types
      - DATE data type
      - TIMESTAMP data type
      - TIMESTAMP WITH TIME ZONE data type
      - TIMESTAMP WITH LOCAL TIME ZONE data type
      - INTERVAL YEAR TO MONTH data type
      - INTERVAL DAY TO SECOND data type
      - Calculation of DATE and INTERVAL values
    - RAW data type
    - Large object data types
      - Overview of large object data types
      - BLOB data type
      - CLOB data type
  - Comparison rules of data types
    - Overview of data type comparison rules
    - Numeric values
    - Date values
    - Character values
    - Data type precedence
    - Data type conversion
    - Security notes for data conversions
  - Literals
    - Literal overview
    - Text literals
    - Numeric literals
    - Datetime literals
      - Date literals
      - Timestamp literals
    - Interval literals
  - Formatting
    - Formatting overview
    - Number formatting
    - Datetime formatting
    - RR datetime format element
    - String-to-date conversion rules
  - Null values
    - Null value overview
    - Null values in SQL functions
    - Null values in comparison conditions
    - Null values in conditional expressions
  - Comments
    - Overview
    - Comments in SQL statements
    - Comments on schema objects and non-schema objects
    - Hint
      - Hint overview
      - Lists of hints
        Hints related to access paths
        Hints related to join orders
        Hints related to join operations
        Hints related to parallel execution
        Hints related to query policies
        Hints related to query transformation
        Other hints
  - Database objects
    - Schema objects
  - Database naming conventions
    - Overview of the naming conventions of database objects
    - Naming rules of database objects
    - Examples of schema object names
    - Rules for naming schema objects
  - Methods of referencing database objects
    - Overview of database object references
    - Reference schema objects
    - Reference partitioned tables and indexes
    - Reference object type attributes and methods
- Operators
  - Operator overview
  - Arithmetic operators
  - Concatenation operators
  - Hierarchical query operators
  - Set operators
  - Collations
- Functions
  - Function overview
  - Single-row functions
    - Numeric functions
      - ABS
      - ACOS
      - BITAND
      - CEIL
      - EXP
      - FLOOR
      - LN
      - LOG
      - MOD
      - POWER
      - REMAINDER
      - ROUND
      - SIGN
      - SQRT
      - TRUNC
    - String functions that return strings
      - CHR
      - CONCAT
      - INITCAP
      - LOWER
      - LPAD
      - LTRIM
      - REGEXP_REPLACE
      - REPLACE
      - RPAD
      - RTRIM
      - SUBSTR
      - TRANSLATE
      - TRIM
      - UPPER
    - String functions that return numbers
      - ASCII
      - INSTR
      - LENGTH
    - Datetime functions
      - ADD_MONTHS
      - CURRENT_DATE
      - CURRENT_TIMESTAMP
      - DBTIMEZONE
      - EXTRACT(datetime)
      - FROM_TZ
      - LAST_DAY
      - LOCALTIMESTAMP
      - MONTHS_BETWEEN
      - NEXT_DAY
      - NUMTODSINTERVAL
      - NUMTOYMINTERVAL
      - ROUND (date)
      - SESSIONTIMEZONE
      - SYS_EXTRACT_UTC
      - SYSDATE
      - SYSTIMESTAMP
      - TO_CHAR (datetime)
      - TO_DSINTERVAL
      - TO_TIMESTAMP
      - TO_TIMESTAMP_TZ
      - TO_YMINTERVAL
      - TRUNC (date)
      - TZ_OFFSET
    - General comparison functions
      - GREATEST
      - LEAST
    - Conversion functions
      - CAST
      - HEXTORAW
      - RAWTOHEX
      - TO_BINARY_DOUBLE
      - TO_BINARY_FLOAT
      - TO_CHAR (character)
      - TO_CHAR (datetime)
      - TO_CHAR (number)
      - TO_DATE
      - TO_DSINTERVAL
      - TO_NUMBER
      - TO_TIMESTAMP
      - TO_TIMESTAMP_TZ
      - TO_YMINTERVAL
    - Encoding and decoding functions
      - DECODE
      - ORA_HASH
      - VSIZE
    - Null value-related functions
      - COALESCE
      - LNNVL
      - NVL
      - NVL2
  - Aggregate functions
    - APPROX_COUNT_DISTINCT
    - AVG
    - COUNT
    - KEEP
    - LISTAGG
    - MAX
    - MIN
    - ROLLUP
    - STDDEV
    - STDDEV_POP
    - STDDEV_SAMP
    - SUM
    - VARIANCE
    - WMSYS.WM_CONCAT/WM_CONCAT
  - Analytic functions
    - Description of window functions
    - AVG
    - COUNT
    - KEEP
    - SUM
    - MAX
    - MIN
    - LISTAGG
    - STDDEV
    - STDDEV_POP
    - STDDEV_SAMP
    - VARIANCE
    - RANK
    - LEAD
    - LAG
    - FIRST_VALUE
    - LAST_VALUE
    - NTH_VALUE
    - CUME_DIST
    - DENSE_RANK
    - NTILE
    - PERCENT_RANK
    - RATIO_TO_REPORT
    - ROW_NUMBER
    - WMSYS.WM_CONCAT/WM_CONCAT
- Expressions
  - Overview of SQL expressions
  - Simple expressions
  - Compound expressions
  - Case expressions
  - Column expressions
  - Datetime expressions
  - Function expressions
  - Interval expressions
  - Scalar subquery expressions
  - Expression lists
- Conditions
  - Overview of SQL conditions
  - Comparison conditions
  - Logical conditions
  - Pattern-matching conditions
  - NULL conditions
  - Compound conditions
  - BETWEEN conditions
  - EXISTS conditions
  - IN conditions
- Queries and subqueries
  - Overview of queries and subqueries
  - Simple queries
  - Hierarchical queries
  - Sets
  - Joins
  - Subqueries
- SQL statements
  - DDL
    - ALTER KEYSTORE
    - ALTER OUTLINE
    - ALTER SEQUENCE
    - ALTER SESSION
    - ALTER TABLE
    - ALTER TABLEGROUP
    - ALTER USER
    - CREATE INDEX
    - CREATE KEYSTORE
    - CREATE OUTLINE
    - CREATE RESTORE POINT
    - CREATE SEQUENCE
    - CREATE SYNONYM
    - CREATE TABLE
    - CREATE TABLEGROUP
    - CREATE TABLESPACE
    - CREATE USER
    - CREATE VIEW
    - DROP INDEX
    - DROP OUTLINE
    - DROP RESTORE POINT
    - DROP SEQUENCE
    - DROP SYNONYM
    - DROP TABLE
    - DROP TABLEGROUP
    - DROP TABLESPACE
    - DROP USER
    - DROP VIEW
    - RENAME
    - TRUNCATE TABLE
  - DML
    - DELETE
    - INSERT
    - MERGE
    - PURGE INDEX
    - PURGE RECYCLEBIN
    - PURGE TABLE
    - SELECT
    - UPDATE
  - DCL
    - AUDIT
    - EXPLAIN
    - FLASHBACK TABLE BEFORE DROP
    - GRANT
    - KILL
    - REVOKE
    - SAVEPOINT
    - SET NAMES
    - SET PASSWORD
    - SET VARIABLE
    - SHOW
    - SHOW RECYCLEBIN
    - SHRINK
    - TRANSACTION
SQL Tuning Guide
- Execution process of SQL queries
- SQL execution plan
  - Introduction to SQL execution plan
  - Execution plan operators
    - TABLE SCAN
    - TABLE LOOKUP
    - JOIN
    - COUNT
    - GROUP BY
    - WINDOW FUNCTION
    - SUBPLAN FILTER
    - DISTINCT
    - SEQUENCE
    - MATERIAL
    - SORT
    - LIMIT
    - FOR UPDATE
    - SELECT INTO
    - SUBPLAN SCAN
    - UNION
    - INTERSECT
    - EXCEPT/MINUS
    - INSERT
    - DELETE
    - UPDATE
    - MERGE
    - EXCHANGE
    - GI
  - Plan cache
  - Fast parameterization
  - Execution plan display (EXPLAIN)
  - Real-time execution plan display
- Distributed execution plan
  - Distributed execution and parallel query
  - Generate a distributed plan
  - Schedule distributed execution plans
  - Manage distributed execution plans
  - Execution of parallel queries
  - Parameter tuning for a parallel query
- SQL tuning
  - Overview of SQL tuning
  - General procedure of SQL tuning
  - Monitor the SQL execution performance
    - (g)v$sql_audit introduction
    - SQL Trace
    - Plan cache view
    - Examples of SQL performance analysis
      - Use SQL Audit to analyze wait events in a query
      - Analyze SQL queries that cause an abrupt RT jitter
      - View the shape of an execution plan and analyze the plan
      - Analyze slow SQL queries
      - Query SQL traffic distribution and QPS
      - Check whether SQL queries are balanced across servers in a cluster
      - Find the TOP N SQL queries
      - Analyze whether an unusually large number of remote execution requests exist in the system or the execution of an SQL statement
      - Find full table scan SQL statements in a tenant
      - Find the TOP N queries with the longest execution time within a specified period
      - Analyze queries related to distributed plans
      - Check whether the RPC execution count of a distributed subplan is balanced across servers in a cluster
  - Query rewrite
    - Overview
    - Rule-based query rewrite
    - Cost-based query rewrite
  - Query optimization
    - Access path
      - Overview
      - Rule-based path selection
      - Cost-based path selection
    - Join algorithms
      - Overview
      - Join algorithms
      - Join order
  - Manage execution plans
    - Optimizer hints
    - Plan binding
    - SQL plan management
- Concepts
- FAQ about SQL tuning
Reference Guide (MySQL Mode)
- System views
  - Overview
  - Dictionary views
    - mysql.help_topic
    - mysql.help_category
    - mysql.help_keyword
    - mysql.help_relation
    - mysql.db
    - mysql.proc
    - mysql.time_zone
    - mysql.time_zone_name
    - mysql.time_zone_transition
    - mysql.time_zone_transition_type
    - mysql.user
    - information_schema.CHARACTER_SETS
    - information_schema.COLLATIONS
    - information_schema.COLLATION_CHARACTER_SET_APPLICABILITY
    - information_schema.COLUMNS
    - information_schema.DBA_OUTLINES
    - information_schema.ENGINES
    - information_schema.GLOBAL_STATUS
    - information_schema.GLOBAL_VARIABLES
    - information_schema.KEY_COLUMN_USAGE
    - information_schema.PARAMETERS
    - information_schema.PARTITIONS
    - information_schema.PROCESSLIST
    - information_schema.REFERENTIAL_CONSTRAINTS
    - information_schema.ROUTINES
    - information_schema.SCHEMATA
    - information_schema.SCHEMA_PRIVILEGES
    - information_schema.SESSION_STATUS
    - information_schema.SESSION_VARIABLES
    - information_schema.STATISTICS
    - information_schema.TABLES
    - information_schema.TABLE_CONSTRAINTS
    - information_schema.TABLE_PRIVILEGES
    - information_schema.USER_PRIVILEGES
    - information_schema.USER_RECYCLEBIN
    - information_schema.VIEWS
    - oceanbase.CDB_OB_BACKUP_ARCHIVELOG_SUMMARY
    - oceanbase.CDB_OB_BACKUP_JOB_DETAILS
    - oceanbase.CDB_OB_BACKUP_SET_DETAILS
    - oceanbase.CDB_OB_BACKUP_PROGRESS
    - oceanbase.CDB_OB_BACKUP_SET_EXPIRED
    - oceanbase.CDB_OB_BACKUP_ARCHIVELOG_PROGRESS
    - oceanbase.CDB_OB_BACKUP_CLEAN_HISTORY
    - oceanbase.CDB_OB_BACKUP_TASK_CLEAN_HISTORY
    - oceanbase.CDB_OB_RESTORE_PROGRESS
    - oceanbase.CDB_OB_RESTORE_HISTORY
    - oceanbase.CDB_CKPT_HISTORY
    - oceanbase.CDB_OB_BACKUP_VALIDATION_JOB
    - oceanbase.CDB_OB_BACKUP_VALIDATION_JOB_HISTORY
    - oceanbase.CDB_OB_TENANT_BACKUP_VALIDATION_TASK
    - oceanbase.CDB_OB_BACKUP_VALIDATION_TASK_HISTORY
    - oceanbase.CDB_OB_BACKUP_BACKUP_ARCHIVELOG_SUMMARY
    - oceanbase.CDB_OB_BACKUP_BACKUPSET_TASK_HISTORY
    - oceanbase.CDB_OB_BACKUP_BACKUPSET_TASK
    - oceanbase.CDB_OB_BACKUP_BACKUPSET_JOB_HISTORY
    - oceanbase.CDB_OB_BACKUP_BACKUPSET_JOB
    - oceanbase.CDB_OB_BACKUP_SET_OBSOLETE
    - oceanbase.CDB_OB_BACKUP_ARCHIVELOG
    - oceanbase.CDB_OB_BACKUP_BACKUP_ARCHIVELOG
    - oceanbase.CDB_OB_BACKUP_PIECE_FILES
    - oceanbase.CDB_OB_BACKUP_SET_FILES
  - Performance views
    - gv$plan_cache_stat
    - gv$plan_cache_plan_stat
    - gv$session_event
    - gv$session_wait
    - gv$session_wait_history
    - gv$system_event
    - gv$sesstat
    - gv$sysstat
    - gv$sql_audit
    - gv$latch
    - gv$memory
    - gv$memstore
    - gv$memstore_info
    - gv$plan_cache_plan_explain
    - gv$obrpc_outgoing
    - gv$obrpc_incoming
    - gv$sql
    - gv$sql_plan_monitor
    - gv$outline
    - gv$concurrent_limit_sql
    - gv$sql_plan_statistics
    - gv$server_memstore
    - gv$unit_load_balance_event_history
    - gv$tenant
    - gv$database
    - gv$table
    - gv$unit
    - gv$partition
    - gv$lock_wait_stat
    - gv$session_longops
    - gv$tenant_memstore_allocator_info
    - gv$minor_merge_info
    - gv$tenant_px_worker_stat
    - gv$partition_audit
    - gv$ps_stat
    - gv$ps_item_info
    - gv$sql_workarea
    - gv$sql_workarea_histogram
    - gv$ob_sql_workarea_memory_info
    - gv$server_schema_info
    - gv$merge_info
    - gv$lock
    - v$statname
    - v$event_name
    - v$session_event
    - v$session_wait
    - v$session_wait_history
    - v$sesstat
    - v$sysstat
    - v$system_event
    - v$memory
    - v$memstore
    - v$memstore_info
    - v$plan_cache_stat
    - v$plan_cache_plan_stat
    - v$plan_cache_plan_explain
    - v$sql_audit
    - v$obrpc_outgoing
    - v$obrpc_incoming
    - v$sql
    - v$sql_plan_monitor
    - v$sql_plan_statistics
    - v$unit
    - v$partition
    - v$lock_wait_stat
    - v$session_longops
    - v$latch
    - v$tenant_memstore_allocator_info
    - v$tenant_px_worker_stat
    - v$partition_audit
    - v$ob_cluster
    - v$ob_standby_status
    - v$ob_cluster_stats
    - v$ob_cluster_event_history
    - v$ps_stat
    - v$ps_item_info
    - v$sql_workarea
    - v$sql_workarea_active
    - v$sql_workarea_histogram
    - v$ob_sql_workarea_memory_info
    - v$ob_timestamp_service
    - v$server_schema_info
    - v$merge_info
    - v$lock
    - v$sql_monitor_statname
    - v$restore_point
    - v$ob_cluster_failover_info
    - v$encrypted_tables
    - v$encrypted_tablespaces
    - v$ob_all_clusters
- System variables
  - System variable overview
  - auto_increment_increment
  - auto_increment_offset
  - autocommit
  - character_set_client
  - character_set_connection
  - character_set_database
  - character_set_results
  - character_set_server
  - character_set_system
  - collation_connection
  - collation_database
  - collation_server
  - interactive_timeout
  - last_insert_id
  - max_allowed_packet
  - sql_mode
  - time_zone
  - tx_isolation
  - version_comment
  - wait_timeout
  - binlog_row_image
  - character_set_filesystem
  - connect_timeout
  - datadir
  - debug_sync
  - div_precision_increment
  - explicit_defaults_for_timestamp
  - group_concat_max_len
  - identity
  - lower_case_table_names
  - net_read_timeout
  - net_write_timeout
  - read_only
  - sql_auto_is_null
  - sql_select_limit
  - timestamp
  - tx_read_only
  - version
  - sql_warnings
  - max_user_connections
  - init_connect
  - license
  - net_buffer_length
  - system_time_zone
  - query_cache_size
  - query_cache_type
  - sql_quote_show_create
  - max_sp_recursion_depth
  - sql_safe_updates
  - ob_default_replica_num
  - ob_proxy_partition_hit
  - ob_log_level
  - ob_max_parallel_degree
  - ob_query_timeout
  - ob_read_consistency
  - ob_enable_transformation
  - ob_trx_timeout
  - ob_enable_plan_cache
  - ob_enable_index_direct_select
  - ob_proxy_set_trx_executed
  - ob_enable_aggregation_pushdown
  - ob_last_schema_version
  - ob_global_debug_sync
  - ob_proxy_global_variables_version
  - ob_enable_trace_log
  - ob_enable_hash_group_by
  - ob_enable_blk_nestedloop_join
  - ob_bnl_join_cache_size
  - ob_org_cluster_id
  - ob_plan_cache_percentage
  - ob_plan_cache_evict_high_percentage
  - ob_plan_cache_evict_low_percentage
  - recyclebin
  - ob_capability_flag
  - ob_stmt_parallel_degree
  - is_result_accurate
  - error_on_overlap_time
  - ob_compatibility_mode
  - ob_create_table_strict_mode
  - ob_sql_work_area_percentage
  - ob_route_policy
  - ob_enable_transmission_checksum
  - foreign_key_checks
  - ob_enable_truncate_flashback
  - ob_tcp_invited_nodes
  - sql_throttle_current_priority
  - sql_throttle_priority
  - sql_throttle_rt
  - sql_throttle_network
  - auto_increment_cache_size
  - ob_enable_jit
  - ob_temp_tablespace_size_percentage
  - ob_timestamp_service
  - plugin_dir
  - undo_retention
  - ob_sql_audit_percentage
  - ob_enable_sql_audit
  - optimizer_use_sql_plan_baselines
  - optimizer_capture_sql_plan_baselines
  - parallel_max_servers
  - parallel_servers_target
  - ob_early_lock_release
  - ob_trx_idle_timeout
  - block_encryption_mode
  - ob_reserved_meta_memory_percentage
  - ob_check_sys_variable
  - tracefile_identifier
  - transaction_isolation
  - ob_trx_lock_timeout
  - validate_password_check_user_name
  - validate_password_length
  - validate_password_mixed_case_count
  - validate_password_number_count
  - validate_password_policy
  - validate_password_special_char_count
  - default_password_lifetime
  - ob_trace_info
  - secure_file_priv
  - ob_pl_block_timeout
  - performance_schema
  - transaction_read_only
  - max_connections
- System configuration items
  - Overview of system configuration items
  - all_cluster_list
  - enable_unit_balance_resource_weight
  - plan_cache_high_watermark
  - plan_cache_low_watermark
  - sql_audit_queue_size
  - tenant_cpu_variation_per_server
  - max_stale_time_for_weak_consistency
  - sql_work_area
  - auto_leader_switch_interval
  - auto_delete_expired_backup
  - autoinc_cache_refresh_interval
  - audit_sys_operations
  - audit_trail
  - balancer_idle_time
  - balancer_log_interval
  - balancer_timeout_check_interval
  - balancer_task_timeout
  - balancer_tolerance_percentage
  - balancer_emergency_percentage
  - balance_blacklist_failure_threshold
  - balance_blacklist_retry_interval
  - backup_concurrency
  - backup_dest
  - backup_net_limit
  - backup_recovery_window
  - backup_region
  - builtin_db_data_verify_cycle
  - bf_cache_miss_count_threshold
  - bf_cache_priority
  - cache_wash_threshold
  - clog_cache_priority
  - clog_sync_time_warn_threshold
  - clog_disk_usage_limit_percentage
  - clog_transport_compress_all
  - clog_transport_compress_func
  - clog_persistence_compress_func
  - clog_max_unconfirmed_log_count
  - cluster
  - cluster_id
  - cpu_count
  - cpu_quota_concurrency
  - cpu_reserved
  - config_additional_dir
  - data_copy_concurrency
  - data_dir
  - datafile_disk_percentage
  - dtl_buffer_size
  - datafile_size
  - default_compress_func
  - default_compress
  - default_progressive_merge_num
  - default_row_format
  - devname
  - data_disk_usage_limit_percentage
  - disk_io_thread_count
  - dead_socket_detection_timeout
  - enable_clog_persistence_compress
  - election_cpu_quota
  - enable_one_phase_commit
  - enable_sys_unit_standalone
  - enable_pg
  - enable_smooth_leader_switch
  - election_blacklist_interval
  - enable_election_group
  - enable_auto_leader_switch
  - enable_global_freeze_trigger
  - enable_manual_merge
  - enable_merge_by_turn
  - enable_perf_event
  - enable_rebalance
  - enable_record_trace_log
  - enable_record_trace_id
  - enable_early_lock_release
  - enable_rereplication
  - enable_rich_error_msg
  - enable_rootservice_standalone
  - enable_sql_audit
  - enable_sql_operator_dump
  - enable_async_syslog
  - enable_syslog_recycle
  - enable_syslog_wf
  - enable_upgrade_mode
  - enable_separate_sys_clog
  - enable_ddl
  - enable_major_freeze
  - enable_rebuild_on_purpose
  - enable_log_archive
  - enable_monotonic_weak_read
  - external_kms_info
  - freeze_trigger_percentage
  - flush_log_at_trx_commit
  - fuse_row_cache_priority
  - force_refresh_location_cache_interval
  - force_refresh_location_cache_threshold
  - get_leader_candidate_rpc_timeout
  - global_major_freeze_residual_memory
  - global_write_halt_residual_memory
  - ignore_replay_checksum_error
  - global_index_build_single_replica_timeout
  - high_priority_net_thread_count
  - ignore_replica_checksum_error
  - ignore_replay_checksum_error
  - index_cache_priority
  - index_clog_cache_priority
  - index_info_block_cache_priority
  - internal_sql_execute_timeout
  - large_query_worker_percentage
  - large_query_threshold
  - leak_mod_to_check
  - lease_time
  - location_cache_cpu_quota
  - location_cache_expire_time
  - location_cache_priority
  - location_cache_refresh_min_interval
  - location_fetch_concurrency
  - location_refresh_thread_count
  - log_archive_checkpoint_interval
  - log_archive_concurrency
  - log_restore_concurrency
  - major_freeze_duty_time
  - max_kept_major_version_number
  - max_string_print_length
  - max_syslog_file_count
  - merge_stat_sampling_ratio
  - major_compact_trigger
  - memory_chunk_cache_size
  - memory_limit
  - memory_limit_percentage
  - memory_reserved
  - merge_thread_count
  - merger_check_interval
  - merger_completion_percentage
  - merger_switch_leader_duration_time
  - merger_warm_up_duration_time
  - max_px_worker_count
  - migration_disable_time
  - min_observer_version
  - minor_deferred_gc_time
  - minor_freeze_times
  - minor_warm_up_duration_time
  - mysql_port
  - minor_merge_concurrency
  - multiblock_read_gap_size
  - multiblock_read_size
  - micro_block_merge_verify_level
  - migrate_concurrency
  - minor_compact_trigger
  - memstore_limit_percentage
  - net_thread_count
  - obconfig_url
  - ob_enable_batched_multi_statement
  - partition_table_check_interval
  - partition_table_scan_batch_count
  - plan_cache_evict_interval
  - px_task_size
  - px_workers_per_cpu_quota
  - replica_safe_remove_time
  - resource_hard_limit
  - resource_soft_limit
  - rootservice_async_task_queue_size
  - rootservice_async_task_thread_count
  - rootservice_list
  - rootservice_ready_check_interval
  - row_compaction_update_limit
  - row_purge_thread_count
  - rpc_port
  - rpc_timeout
  - restore_concurrency
  - rootservice_memory_limit
  - rebuild_replica_data_lag_threshold
  - schema_history_expire_time
  - ssl_client_authentication
  - server_check_interval
  - server_data_copy_in_concurrency
  - server_data_copy_out_concurrency
  - server_permanent_offline_time
  - server_temporary_offline_time
  - stack_size
  - server_balance_critical_disk_waterlevel
  - server_balance_disk_tolerance_percent
  - system_memory
  - server_balance_cpu_mem_tolerance_percent
  - server_cpu_quota_max
  - server_cpu_quota_min
  - sql_audit_memory_limit
  - sys_bkgd_io_high_percentage
  - sys_bkgd_io_low_percentage
  - sys_bkgd_io_timeout
  - sys_bkgd_net_percentage
  - sys_bkgd_migration_change_member_list_timeout
  - sys_bkgd_migration_retry_num
  - syslog_level
  - switchover_process_thread_count
  - system_cpu_quota
  - sys_cpu_limit_trigger
  - system_trace_level
  - syslog_io_bandwidth_limit
  - tablet_size
  - tableapi_transport_compress_func
  - tenant_task_queue_size
  - tenant_groups
  - trace_log_slow_query_watermark
  - trace_log_sampling_interval
  - trx_2pc_retry_interval
  - trx_try_wait_lock_timeout
  - trx_force_kill_threshold
  - tde_method
  - token_reserved_percentage
  - unit_balance_resource_weight
  - user_block_cache_priority
  - user_row_cache_priority
  - user_tab_col_stat_cache_priority
  - user_iort_up_percentage
  - use_large_pages
  - virtual_table_location_cache_expire_time
  - workers_per_cpu_quota
  - wait_leader_batch_count
  - writing_throttling_maximum_duration
  - writing_throttling_trigger_percentage
  - weak_read_version_refresh_interval
  - workarea_size_policy
  - zone
  - zone_merge_concurrency
  - zone_merge_order
  - zone_merge_timeout
  - ob_ssl_invited_common_names
  - ssl_external_kms_info
  - ob_event_history_recycle_interval
  - backup_backup_dest
  - plsql_ccflags
  - plsql_code_type
  - plsql_debug
  - plsql_optimize_level
  - plsql_v2_compatibility
  - plsql_warnings
  - recyclebin_object_expire_time
  - log_archive_batch_buffer_limit
  - clog_disk_utilization_threshold
  - backup_backup_archive_log_batch_count
  - backup_backup_archivelog_retry_interval
  - backup_backupset_batch_count
  - backup_backupset_retry_interval
  - open_cursors
  - location_cache_refresh_rpc_timeout
  - location_cache_refresh_sql_timeout
  - backup_dest_option
  - backup_backup_dest_option
  - backup_zone
  - backup_log_archive_option
  - enable_tcp_keepalive
  - tcp_keepidle
  - tcp_keepintvl
  - tcp_keepcnt
- Reserved keywords
Reference Guide (Oracle Mode)
- System views
  - Dictionary views
    - ALL_ALL_TABLES
    - ALL_ARGUMENTS
    - ALL_COL_COMMENTS
    - ALL_COLL_TYPES
    - ALL_COL_PRIVS
    - ALL_CONSTRAINTS
    - ALL_CONS_COLUMNS
    - ALL_DEF_AUDIT_OPTS
    - ALL_ERRORS
    - ALL_INDEXES
    - ALL_IND_COLUMNS
    - ALL_IND_EXPRESSIONS
    - ALL_IND_PARTITIONS
    - ALL_IND_SUBPARTITIONS
    - ALL_METHOD_PARAMS
    - ALL_OBJECTS
    - ALL_PART_INDEXES
    - ALL_PART_KEY_COLUMNS
    - ALL_PART_TABLES
    - ALL_PROCEDURES
    - ALL_PROFILES
    - ALL_SCHEDULER_JOB_ARGS
    - ALL_SCHEDULER_PROGRAM_ARGS
    - ALL_SEQUENCES
    - ALL_SOURCE
    - ALL_SUBPARTITION_TEMPLATES
    - ALL_SUBPART_KEY_COLUMNS
    - ALL_SYNONYMS
    - ALL_TABLES
    - ALL_TAB_COLS
    - ALL_TAB_COLUMNS
    - ALL_TAB_COMMENTS
    - ALL_TAB_PARTITIONS
    - ALL_TAB_PRIVS
    - ALL_TAB_SUBPARTITIONS
    - ALL_TRIGGERS
    - ALL_TYPES
    - ALL_TYPE_ATTRS
    - ALL_TYPE_METHODS
    - ALL_USERS
    - ALL_VIEWS
    - AUDIT_ACTIONS
    - DBA_ALL_TABLES
    - DBA_ARGUMENTS
    - DBA_AUDIT_EXISTS
    - DBA_AUDIT_OBJECT
    - DBA_AUDIT_SESSION
    - DBA_AUDIT_STATEMENT
    - DBA_AUDIT_TRAIL
    - DBA_COLL_TYPES
    - DBA_COL_COMMENTS
    - DBA_COL_PRIVS
    - DBA_CONSTRAINTS
    - DBA_CONS_COLUMNS
    - DBA_ERRORS
    - DBA_INDEXES
    - DBA_IND_COLUMNS
    - DBA_IND_EXPRESSIONS
    - DBA_IND_PARTITIONS
    - DBA_IND_SUBPARTITIONS
    - DBA_METHOD_PARAMS
    - DBA_OBJECTS
    - DBA_OBJ_AUDIT_OPTS
    - DBA_PART_INDEXES
    - DBA_PART_KEY_COLUMNS
    - DBA_PART_TABLES
    - DBA_PROCEDURES
    - DBA_PROFILES
    - DBA_ROLES
    - DBA_ROLE_PRIVS
    - DBA_SCHEDULER_JOB_ARGS
    - DBA_SCHEDULER_PROGRAM_ARGS
    - DBA_SEGMENTS
    - DBA_SEQUENCES
    - DBA_SOURCE
    - DBA_STMT_AUDIT_OPTS
    - DBA_SUBPARTITION_TEMPLATES
    - DBA_SUBPART_KEY_COLUMNS
    - DBA_SYNONYMS
    - DBA_SYS_PRIVS
    - DBA_TABLES
    - DBA_TABLESPACES
    - DBA_TAB_COLS
    - DBA_TAB_COLUMNS
    - DBA_TAB_COMMENTS
    - DBA_TAB_PARTITIONS
    - DBA_TAB_PRIVS
    - DBA_TAB_SUBPARTITIONS
    - DBA_TRIGGERS
    - DBA_TYPES
    - DBA_TYPE_ATTRS
    - DBA_TYPE_METHODS
    - DBA_USERS
    - DBA_VIEWS
    - DBA_RECYCLEBIN
    - NLS_DATABASE_PARAMETERS
    - NLS_INSTANCE_PARAMETERS
    - NLS_SESSION_PARAMETERS
    - STMT_AUDIT_OPTION_MAP
    - USER_ALL_TABLES
    - USER_ARGUMENTS
    - USER_AUDIT_OBJECT
    - USER_AUDIT_SESSION
    - USER_AUDIT_STATEMENT
    - USER_AUDIT_TRAIL
    - USER_COLL_TYPES
    - USER_COL_COMMENTS
    - USER_COL_PRIVS
    - USER_CONSTRAINTS
    - USER_CONS_COLUMNS
    - USER_ERRORS
    - USER_INDEXES
    - USER_IND_COLUMNS
    - USER_IND_EXPRESSIONS
    - USER_IND_PARTITIONS
    - USER_IND_SUBPARTITIONS
    - USER_OBJECTS
    - USER_PART_INDEXES
    - USER_PART_KEY_COLUMNS
    - USER_PART_TABLES
    - USER_PROCEDURES
    - USER_PROFILES
    - USER_ROLE_PRIVS
    - USER_SEGMENTS
    - USER_SEQUENCES
    - USER_SOURCE
    - USER_SUBPARTITION_TEMPLATES
    - USER_SUBPART_KEY_COLUMNS
    - USER_SYNONYMS
    - USER_SYS_PRIVS
    - USER_TAB_SUBPARTITIONS
    - USER_TRIGGERS
    - USER_TABLES
    - USER_TABLESPACES
    - USER_TAB_COLS
    - USER_TAB_COLUMNS
    - USER_TAB_COMMENTS
    - USER_TAB_PARTITIONS
    - USER_TYPES
    - USER_TYPE_ATTRS
    - USER_TYPE_METHODS
    - USER_VIEWS
    - USER_RECYCLEBIN
    - ROLE_TAB_PRIVS
    - ROLE_SYS_PRIVS
    - ROLE_ROLE_PRIVS
    - DICTIONARY
    - DICT
  - Performance views
    - GV$LOCK
    - GV$CONCURRENT_LIMIT_SQL
    - GV$INSTANCE
    - GV$MEMORY
    - GV$MEMSTORE
    - GV$MEMSTORE_INFO
    - GV$OB_SQL_WORKAREA_MEMORY_INFO
    - GV$OUTLINE
    - GV$PLAN_CACHE_PLAN_EXPLAIN
    - GV$PLAN_CACHE_PLAN_STAT
    - GV$PLAN_CACHE_STAT
    - GV$PS_ITEM_INFO
    - GV$PS_STAT
    - GV$SERVER_MEMSTORE
    - GV$SESSION_WAIT
    - GV$SESSION_WAIT_HISTORY
    - GV$SESSTAT
    - GV$SQL_AUDIT
    - GV$SQL_WORKAREA
    - GV$SQL_WORKAREA_ACTIVE
    - GV$SQL_WORKAREA_HISTOGRAM
    - GV$SYSSTAT
    - GV$SYSTEM_EVENT
    - GV$GLOBAL_TRANSACTION
    - V$INSTANCE
    - V$MEMORY
    - V$MEMSTORE
    - V$MEMSTORE_INFO
    - V$NLS_PARAMETERS
    - V$OB_SQL_WORKAREA_MEMORY_INFO
    - V$PLAN_CACHE_PLAN_EXPLAIN
    - V$PLAN_CACHE_PLAN_STAT
    - V$PLAN_CACHE_STAT
    - V$PS_ITEM_INFO
    - V$PS_STAT
    - V$SESSION_WAIT
    - V$SESSION_WAIT_HISTORY
    - V$SESSTAT
    - V$SQL_AUDIT
    - V$SQL_WORKAREA
    - V$SQL_WORKAREA_ACTIVE
    - V$SQL_WORKAREA_HISTOGRAM
    - V$SYSSTAT
    - V$SYSTEM_EVENT
    - V$VERSION
    - V$SQL_MONITOR_STATNAME
    - V$RESTORE_POINT
    - V$GLOBAL_TRANSACTION
    - V$TIMEZONE_NAMES
    - V$LOCK
    - V$ENCRYPTED_TABLES
    - V$ENCRYPTED_TABLESPACES
- System variables
  - System variable overview
  - auto_increment_increment
  - auto_increment_offset
  - autocommit
  - character_set_client
  - character_set_connection
  - character_set_results
  - character_set_system
  - collation_connection
  - interactive_timeout
  - last_insert_id
  - max_allowed_packet
  - sql_mode
  - time_zone
  - tx_isolation
  - version_comment
  - wait_timeout
  - binlog_row_image
  - character_set_filesystem
  - connect_timeout
  - datadir
  - debug_sync
  - div_precision_increment
  - group_concat_max_len
  - identity
  - lower_case_table_names
  - net_read_timeout
  - net_write_timeout
  - read_only
  - sql_auto_is_null
  - sql_select_limit
  - timestamp
  - tx_read_only
  - version
  - sql_warnings
  - max_user_connections
  - init_connect
  - license
  - net_buffer_length
  - system_time_zone
  - query_cache_size
  - query_cache_type
  - sql_quote_show_create
  - max_sp_recursion_depth
  - ob_default_replica_num
  - ob_proxy_partition_hit
  - ob_log_level
  - ob_max_parallel_degree
  - ob_query_timeout
  - ob_read_consistency
  - ob_enable_transformation
  - ob_trx_timeout
  - ob_enable_plan_cache
  - ob_enable_index_direct_select
  - ob_proxy_set_trx_executed
  - ob_enable_aggregation_pushdown
  - ob_last_schema_version
  - ob_global_debug_sync
  - ob_proxy_global_variables_version
  - ob_enable_trace_log
  - ob_enable_hash_group_by
  - ob_enable_blk_nestedloop_join
  - ob_bnl_join_cache_size
  - ob_org_cluster_id
  - ob_plan_cache_percentage
  - ob_plan_cache_evict_high_percentage
  - ob_plan_cache_evict_low_percentage
  - recyclebin
  - ob_capability_flag
  - ob_stmt_parallel_degree
  - is_result_accurate
  - error_on_overlap_time
  - ob_compatibility_mode
  - ob_create_table_strict_mode
  - ob_sql_work_area_percentage
  - ob_route_policy
  - ob_enable_transmission_checksum
  - foreign_key_checks
  - ob_enable_truncate_flashback
  - ob_tcp_invited_nodes
  - sql_throttle_current_priority
  - sql_throttle_priority
  - sql_throttle_rt
  - sql_throttle_network
  - auto_increment_cache_size
  - ob_enable_jit
  - ob_temp_tablespace_size_percentage
  - ob_timestamp_service
  - plugin_dir
  - undo_retention
  - ob_sql_audit_percentage
  - ob_enable_sql_audit
  - optimizer_use_sql_plan_baselines
  - optimizer_capture_sql_plan_baselines
  - parallel_max_servers
  - parallel_servers_target
  - ob_early_lock_release
  - ob_trx_idle_timeout
  - ob_reserved_meta_memory_percentage
  - nls_date_format
  - nls_timestamp_format
  - nls_timestamp_tz_format
  - nls_language
  - nls_territory
  - nls_sort
  - nls_comp
  - nls_characterset
  - nls_nchar_characterset
  - nls_date_language
  - nls_length_semantics
  - nls_calendar
  - nls_nchar_conv_excp
  - nls_numeric_characters
  - tracefile_identifier
  - transaction_isolation
  - ob_trx_lock_timeout
  - ob_trace_info
  - secure_file_priv
  - ob_pl_block_timeout
  - performance_schema
  - transaction_read_only
- System configuration items
  - Overview of system configuration items
  - all_cluster_list
  - enable_unit_balance_resource_weight
  - plan_cache_high_watermark
  - plan_cache_low_watermark
  - sql_audit_queue_size
  - tenant_cpu_variation_per_server
  - max_stale_time_for_weak_consistency
  - sql_work_area
  - auto_leader_switch_interval
  - auto_delete_expired_backup
  - autoinc_cache_refresh_interval
  - audit_sys_operations
  - audit_trail
  - balancer_idle_time
  - balancer_log_interval
  - balancer_timeout_check_interval
  - balancer_task_timeout
  - balancer_tolerance_percentage
  - balancer_emergency_percentage
  - balance_blacklist_failure_threshold
  - balance_blacklist_retry_interval
  - backup_concurrency
  - backup_dest
  - backup_net_limit
  - backup_recovery_window
  - backup_region
  - builtin_db_data_verify_cycle
  - bf_cache_miss_count_threshold
  - bf_cache_priority
  - cache_wash_threshold
  - clog_cache_priority
  - clog_sync_time_warn_threshold
  - clog_disk_usage_limit_percentage
  - clog_transport_compress_all
  - clog_transport_compress_func
  - clog_persistence_compress_func
  - clog_max_unconfirmed_log_count
  - cluster
  - cluster_id
  - cpu_count
  - cpu_quota_concurrency
  - cpu_reserved
  - config_additional_dir
  - data_copy_concurrency
  - data_dir
  - datafile_disk_percentage
  - dtl_buffer_size
  - datafile_size
  - default_compress_func
  - default_compress
  - default_progressive_merge_num
  - default_row_format
  - devname
  - data_disk_usage_limit_percentage
  - disk_io_thread_count
  - dead_socket_detection_timeout
  - enable_clog_persistence_compress
  - election_cpu_quota
  - enable_one_phase_commit
  - enable_sys_unit_standalone
  - enable_pg
  - enable_smooth_leader_switch
  - election_blacklist_interval
  - enable_election_group
  - enable_auto_leader_switch
  - enable_global_freeze_trigger
  - enable_manual_merge
  - enable_merge_by_turn
  - enable_perf_event
  - enable_rebalance
  - enable_record_trace_log
  - enable_record_trace_id
  - enable_early_lock_release
  - enable_rereplication
  - enable_rich_error_msg
  - enable_rootservice_standalone
  - enable_sql_audit
  - enable_sql_operator_dump
  - enable_async_syslog
  - enable_syslog_recycle
  - enable_syslog_wf
  - enable_upgrade_mode
  - enable_separate_sys_clog
  - enable_ddl
  - enable_major_freeze
  - enable_rebuild_on_purpose
  - enable_log_archive
  - enable_monotonic_weak_read
  - external_kms_info
  - freeze_trigger_percentage
  - flush_log_at_trx_commit
  - fuse_row_cache_priority
  - force_refresh_location_cache_interval
  - force_refresh_location_cache_threshold
  - get_leader_candidate_rpc_timeout
  - global_major_freeze_residual_memory
  - global_write_halt_residual_memory
  - ignore_replay_checksum_error
  - global_index_build_single_replica_timeout
  - high_priority_net_thread_count
  - ignore_replica_checksum_error
  - ignore_replay_checksum_error
  - index_cache_priority
  - index_clog_cache_priority
  - index_info_block_cache_priority
  - internal_sql_execute_timeout
  - large_query_worker_percentage
  - large_query_threshold
  - leak_mod_to_check
  - lease_time
  - location_cache_cpu_quota
  - location_cache_expire_time
  - location_cache_priority
  - location_cache_refresh_min_interval
  - location_fetch_concurrency
  - location_refresh_thread_count
  - log_archive_checkpoint_interval
  - log_archive_concurrency
  - log_restore_concurrency
  - major_freeze_duty_time
  - max_kept_major_version_number
  - max_string_print_length
  - max_syslog_file_count
  - merge_stat_sampling_ratio
  - major_compact_trigger
  - memory_chunk_cache_size
  - memory_limit
  - memory_limit_percentage
  - memory_reserved
  - merge_thread_count
  - merger_check_interval
  - merger_completion_percentage
  - merger_switch_leader_duration_time
  - merger_warm_up_duration_time
  - max_px_worker_count
  - migration_disable_time
  - min_observer_version
  - minor_deferred_gc_time
  - minor_freeze_times
  - minor_warm_up_duration_time
  - mysql_port
  - minor_merge_concurrency
  - multiblock_read_gap_size
  - multiblock_read_size
  - micro_block_merge_verify_level
  - migrate_concurrency
  - minor_compact_trigger
  - memstore_limit_percentage
  - net_thread_count
  - obconfig_url
  - ob_enable_batched_multi_statement
  - partition_table_check_interval
  - partition_table_scan_batch_count
  - plan_cache_evict_interval
  - px_task_size
  - px_workers_per_cpu_quota
  - replica_safe_remove_time
  - resource_hard_limit
  - resource_soft_limit
  - rootservice_async_task_queue_size
  - rootservice_async_task_thread_count
  - rootservice_list
  - rootservice_ready_check_interval
  - row_compaction_update_limit
  - row_purge_thread_count
  - rpc_port
  - rpc_timeout
  - restore_concurrency
  - rootservice_memory_limit
  - rebuild_replica_data_lag_threshold
  - schema_history_expire_time
  - ssl_client_authentication
  - server_check_interval
  - server_data_copy_in_concurrency
  - server_data_copy_out_concurrency
  - server_permanent_offline_time
  - server_temporary_offline_time
  - stack_size
  - server_balance_critical_disk_waterlevel
  - server_balance_disk_tolerance_percent
  - system_memory
  - server_balance_cpu_mem_tolerance_percent
  - server_cpu_quota_max
  - server_cpu_quota_min
  - sql_audit_memory_limit
  - sys_bkgd_io_high_percentage
  - sys_bkgd_io_low_percentage
  - sys_bkgd_io_timeout
  - sys_bkgd_net_percentage
  - sys_bkgd_migration_change_member_list_timeout
  - sys_bkgd_migration_retry_num
  - syslog_level
  - switchover_process_thread_count
  - system_cpu_quota
  - sys_cpu_limit_trigger
  - system_trace_level
  - syslog_io_bandwidth_limit
  - tablet_size
  - tableapi_transport_compress_func
  - tenant_task_queue_size
  - tenant_groups
  - trace_log_slow_query_watermark
  - trace_log_sampling_interval
  - trx_2pc_retry_interval
  - trx_try_wait_lock_timeout
  - trx_force_kill_threshold
  - tde_method
  - token_reserved_percentage
  - unit_balance_resource_weight
  - user_block_cache_priority
  - user_row_cache_priority
  - user_tab_col_stat_cache_priority
  - user_iort_up_percentage
  - use_large_pages
  - virtual_table_location_cache_expire_time
  - workers_per_cpu_quota
  - wait_leader_batch_count
  - writing_throttling_maximum_duration
  - writing_throttling_trigger_percentage
  - weak_read_version_refresh_interval
  - workarea_size_policy
  - zone
  - zone_merge_concurrency
  - zone_merge_order
  - zone_merge_timeout
  - ob_ssl_invited_common_names
  - ssl_external_kms_info
  - ob_event_history_recycle_interval
  - backup_backup_dest
  - plsql_ccflags
  - plsql_code_type
  - plsql_debug
  - plsql_optimize_level
  - plsql_v2_compatibility
  - plsql_warnings
  - recyclebin_object_expire_time
  - log_archive_batch_buffer_limit
  - clog_disk_utilization_threshold
  - backup_backup_archive_log_batch_count
  - backup_backup_archivelog_retry_interval
  - backup_backupset_batch_count
  - backup_backupset_retry_interval
  - open_cursors
  - location_cache_refresh_rpc_timeout
  - location_cache_refresh_sql_timeout
  - backup_dest_option
  - backup_backup_dest_option
  - backup_zone
  - backup_log_archive_option
  - enable_tcp_keepalive
  - tcp_keepidle
  - tcp_keepintvl
  - tcp_keepcnt
- Reserved keywords
FAQ
- Product FAQ
- Deployment FAQ
- SQL FAQ
- Storage FAQ
- FAQ about primary and secondary databases
Version release records
- V2.2
  - OceanBase Database V2.2.77

Download PDF

OceanBase, A Highly Scalable Database for Transactional, Analytical, and AI Workloads.

Company

About OceanBase Contact Us Partner

Product

OceanBase Cloud OceanBase Enterprise Quick Start Download Pricing

Resources

Docs Blog White Paper Trust Center

Document Feedback

Built-in functions

Last Updated：2023-08-18 09:26:34 Updated

This topic describes the compatibility of OceanBase Database in Oracle mode with the native Oracle Database in terms of built-in functions.

Oracle Database supports 117 built-in functions, while OceanBase Database supports 103. For more information about the supported functions, see Function overview.

Data functions

SN	Oracle Database	OceanBase Database
1	ABS	Supported
2	ACOS	Supported
3	ASIN	Supported
4	ATAN	Supported
5	ATAN2	Supported
6	BITAND	Supported
7	CEIL	Supported
8	COS	Supported
9	COSH	Supported
10	EXP	Supported
11	FLOOR	Supported
12	LN	Supported
13	LOG	Supported
14	MOD	Supported
15	NANVL	Supported
16	POWER	Supported
17	REMAINDER	Supported
18	ROUND (number)	Supported
19	SIGN	Supported
20	SIN	Supported
21	SINH	Supported
22	SQRT	Supported
23	TAN	Supported
24	TANH	Supported
25	TRUNC (number)	Supported
26	WIDTH_BUCKET	Supported

String functions

SN	Oracle Database	OceanBase Database
1	CHR	Supported
2	CONCAT	Supported
3	INITCAP	Supported
4	LOWER	Supported
5	LPAD	Supported
6	LTRIM	Supported
10	REGEXP_REPLACE	Supported
11	REGEXP_SUBSTR	Supported
12	REPLACE	Supported
13	RPAD	Supported
14	RTRIM	Supported
16	SUBSTR	Supported
17	TRANSLATE	Supported
18	TRIM	Supported
19	UPPER	Supported
20	ASCII	Supported
21	INSTR	Supported
22	LENGTH	Supported
23	REGEXP_COUNT	Supported
24	REGEXP_INSTR	Supported

Date functions

SN	Oracle Database	OceanBase Database
1	ADD_MONTHS	Supported
2	CURRENT_DATE	Supported
3	CURRENT_TIMESTAMP	Supported
4	DBTIMEZONE	Supported
5	EXTRACT (datetime)	Supported
6	FROM_TZ	Supported
7	LAST_DAY	Supported
8	LOCALTIMESTAMP	Supported
9	MONTHS_BETWEEN	Supported
10	NUMTODSINTERVAL	Supported

Type conversion functions

SN	Oracle Database	OceanBase Database
1	CAST	Supported
2	HEXTORAW	Supported
3	RAWTOHEX	Supported
4	TO_CHAR (character)	Supported
5	TO_CHAR (datetime)	Supported
6	TO_CHAR (number)	Supported
7	TO_CLOB	Supported
8	TO_DATE	Supported
9	TO_MULTI_BYTE	Supported
10	TO_NCHAR (character)	Supported

SN	Oracle Database	OceanBase Database
1	COALESCE	Supported
2	LNNVL	Supported
3	NANVL	Supported
4	NULLIF	Supported
5	NVL	Supported
6	NVL2	Supported

Aggregate functions

SN	Oracle Database	OceanBase Database
1	AVG	Supported
2	CORR	Supported
4	COUNT	Supported
5	COVAR_POP	Supported
6	COVAR_SAMP	Supported
7	CUME_DIST	Supported
8	MAX	Supported
9	MIN	Supported
10	PERCENT_RANK	Supported
11	RANK	Supported
12	STDDEV	Supported
13	STDDEV_POP	Supported
14	STDDEV_SAMP	Supported
15	SUM	Supported

Previous topic

SQL data types

Last

Next topic

System views