This article explains how to proactively identify non‑conforming date values during Oracle or MySQL data migrations by creating test tables, leveraging DBMS_ERRLOG for Oracle, using strict SQL_MODE and temporary tables for MySQL, and optionally applying regular‑expression checks.
The SQLE 2.2306.0‑pre3 release introduces Baidu Cloud RDS MySQL slow‑log scanning, SQL formatting, DB2 rollback generation and context‑aware auditing, along with numerous optimizations and bug fixes, and provides updated links to repositories, documentation, and demo environments for both community and enterprise editions.
Apache Doris is an open‑source MPP OLAP database that combines a minimalist architecture, ease of use, rich features such as partition‑bucket pruning, materialized views, and bitmap indexes, and provides high‑performance, scalable, and reliable data warehousing for big‑data analytics.
This guide explains MySQL transaction fundamentals, the ACID properties, how to use explicit and implicit transactions, savepoints, and demonstrates each isolation level with commands and examples to help developers write reliable, concurrent database code.
This article explains the evolution of Alibaba Cloud's MaxCompute platform into a lakehouse architecture that supports near‑real‑time incremental processing, detailing its development history, core design of transactional tables, five‑module technical stack, data ingestion methods, optimization services, transaction management, query capabilities, ecosystem integration, practical applications, future roadmap, and common user questions.
This article introduces Chat2DB, an open‑source AI‑enhanced multi‑database client, explains how to download, install and configure it with OpenAI keys, demonstrates creating data sources and sample tables, and showcases natural‑language‑to‑SQL, SQL explanation, and optimization features with practical examples.
This article introduces DBeaver, an open‑source, cross‑platform database management tool, outlines its key features, provides step‑by‑step usage instructions, and shares the project’s GitHub link, helping developers efficiently handle MySQL, PostgreSQL, Oracle and many other databases.
The SQLE 2.2306.0-pre2 release introduces three new MySQL audit rules, configurable operation‑record expiration, expanded DB2 rule support, numerous optimizations and bug fixes, and provides download links, documentation, and demo environments for both community and enterprise editions.
This article introduces twenty commonly used MySQL functions, explains their purposes, and provides clear SQL examples with expected results to help developers efficiently manipulate and process data, including string, numeric, date, and aggregate functions, each illustrated with sample queries and output.
The article details how Ctrip's train ticket business revamped its multi‑engine OLAP metrics platform by consolidating to the StarRocks MPP database, describing the new architecture, query workflow, data synchronization strategies, practical lessons, and the resulting dramatic improvement in query latency and reliability.
This article explains the concept of data skew in offline big‑data jobs, describes its symptoms and root causes, and provides practical optimization techniques for Hive and Spark—including partitioning strategies, map‑join usage, adaptive query settings, and monitoring approaches—to prevent performance degradation and runtime failures.
This article explains how database indexes work, covering storage fundamentals, the role of indexes in accelerating queries, different index types such as clustered and non‑clustered, common pitfalls, and practical SQL optimization tips to improve read performance while managing write overhead.
This article provides a comprehensive guide to MyBatis dynamic SQL, demonstrating how to use if, choose, trim, foreach, and bind tags for conditional queries, updates, inserts, batch operations, and database‑agnostic expressions, complete with Maven project setup, SQL scripts, and test cases.
The article explains how Apache Doris 2.0 introduces hot‑cold data tiering to move infrequently accessed data from expensive SSDs to cheaper object storage, dramatically reducing storage costs while maintaining query performance through automatic lifecycle management, storage policies, and cache mechanisms.
This article provides a systematic, interview‑focused overview of MySQL fundamentals—including normal forms, DML vs DDL, primary and foreign keys, storage engines, redo and binlog mechanisms, indexing strategies, transaction properties, MVCC, lock types, replication, and common performance pitfalls—helping candidates quickly master essential database knowledge for interview success.
This weekly newsletter curates top industry tech articles covering SQL deployment capabilities, MySQL replication issues, LLM‑to‑SQL benchmarks, connection limit errors, OceanBase tracing, and provides an open‑source SQL quality management platform update with development progress and upcoming plans.
The SQLE 2.2306.0-pre1 release introduces support for TDSQL InnoDB and Mycat data sources in enterprise edition, adds new features, UI optimizations, bug fixes, and provides detailed usage instructions and links to documentation, source code, and demo environments.
This article presents a comprehensive performance comparison of OceanBase with MySQL and TiDB, details step‑by‑step deployment of OceanBase (both single‑node and cluster), explains configuration and resource‑pool management, and shares practical migration techniques and troubleshooting tips for production workloads.
This article explains the concept of SaaS multi‑tenant architecture, compares three data‑isolation designs, and demonstrates how to implement elegant tenant data isolation in Java using MyBatis‑Plus’s tenant plugin, including configuration, code examples, and practical considerations.
This article explains why AUTO_INCREMENT is unsupported in OceanBase Oracle tenants, introduces four practical ways to implement auto‑increment columns using sequences, DML, DDL, triggers, and the GENERATED BY DEFAULT AS IDENTITY syntax, and provides step‑by‑step SQL examples for each method.
This article summarizes a technical presentation on innovative database practices, covering a preventive SQL defense framework, full‑SQL analysis for deep observation, and capacity prediction through simulated traffic testing, with detailed methods, challenges, and future plans for multi‑cloud environments.
The SQLE 2.2305.0 release introduces SQL‑execution abort, webhook notifications, intelligent DB2 and MySQL slow‑SQL scanning, rollback statement generation for Oracle and PostgreSQL, plus numerous UI improvements and bug fixes, providing a more robust and automated database audit experience.
This article explains the definition, data structures, advantages, disadvantages, and appropriate usage scenarios of MySQL indexes, details primary, unique, single‑column and composite index types, analyzes common performance bottlenecks with EXPLAIN, demonstrates index creation, invalidation cases, query and join optimizations, and shows how to configure and use the slow‑query log.
CrateDB 5.3.2 introduces numerous fixes and enhancements, including corrected handling of empty partition tables, improved default clause errors for OBJECT columns, resolved ON CONFLICT insert issues, fixed LIKE/ILIKE pattern bugs, snapshot restoration problems, Azure repository errors, and various optimizer and interval function improvements.
Designing a time axis for HR systems involves choosing between an interval model that stores start and end timestamps and an effective‑date model that uses effective dates and sequences, each affecting query simplicity, maintenance effort, common pitfalls, and ultimately enabling accurate historical and future employee data analysis.
This article explains the data query fundamentals of YouShu BI charts, introduces intelligent caching design, describes query merging and various optimization techniques—including partition filters, value acceleration, and SQL generation—and outlines performance diagnosis methods to improve BI chart responsiveness.
This article explains what indexes are, when they should be applied, and provides step‑by‑step MySQL commands—including single‑column, composite, and index‑hint examples—to improve query performance on large datasets.
This article examines the inherent difficulties of SQL when handling multi‑step, ordered, and object‑referencing queries, illustrates them with practical examples, and introduces the Structured Process Language (SPL) as a more expressive alternative for data processing tasks.
This article introduces Pandas AI, shows how to install it, connect to databases, run natural‑language queries and generate visualizations using OpenAI models, and explains how combining ChatGPT with Pandas transforms data interaction for Python developers and analysts.
The article explains why MySQL 8.x may ignore a primary‑key index and perform a full table scan for an IN subquery, describes the optimizer’s cost‑model reasoning, and shows how rewriting the query with an INNER JOIN restores index usage and dramatically improves performance.
The article explains the performance drawbacks of using OFFSET and LIMIT for pagination on large tables, illustrates the issue with full‑table scans, and presents a more efficient cursor‑based approach that leverages indexed primary keys to retrieve pages quickly.
This article introduces the MyBatis‑Plus‑Join library that adds join capabilities to MyBatis‑Plus, explains how to install it via Maven or Gradle, shows how to extend mapper and service interfaces, and provides detailed code examples for simple three‑table queries, pagination, and advanced custom SQL usage.
The article investigates a MySQL 8.0 instance where many sessions were stuck in the executing state, analyzes InnoDB thread‑concurrency settings, stack traces and source code to reveal that exhausted InnoDB tickets caused nanosleep‑based blocking, and proposes configuration and SQL‑optimisation solutions.
The SQLE 2.2305.0-pre3 release introduces Webhook-based work‑order status notifications, Oracle rollback statement generation for the enterprise edition, updated documentation links, a detailed feature list, bug fixes, and references to previous versions and further reading resources.
SQLite 3.42.0, the widely used public-domain SQL database engine, introduces features such as FTS5 secure-delete, JSON5 support, new sqlite3_config options, enhanced query planner optimizations, CLI improvements, additional SQL functions, extended PRAGMA integrity_check, subsecond time modifiers, and various bug fixes and performance enhancements.
This article details Tongcheng Travel’s production deployment of the StarRocks OLAP database, covering background, business scenarios, technical evaluation against ClickHouse and Greenplum, implementation with Flink SQL, real‑time analytics, offline reporting, CDP use cases, performance optimizations, and future cloud‑native plans.
This article explains why combining large language models with vector databases enables enterprises to create specialized, up‑to‑date chatbots, outlines the underlying principles, describes the ADB‑PG vector‑search capabilities, and provides step‑by‑step implementation details including data processing, indexing, and query examples.
The article explains what open‑source databases are, compares community and commercial editions, examines licensing models, reviews major vendors' adoption (Oracle, Microsoft, Amazon, Google), and discusses recent governance disputes and the impact of hybrid licensing on the database ecosystem.
This step‑by‑step tutorial shows how to set up Alibaba Cloud DataWorks and MaxCompute, bind them together, and use free trial resources to explore public big‑data datasets such as Alibaba e‑commerce, Github events, and custom data with SQL queries and visualizations.
The article explains why adding a LIMIT clause to MySQL DELETE statements is a recommended habit, detailing performance gains, reduced locking, safer error handling, and practical execution strategies for large‑scale deletions.
This article explains how to obtain distinct code entries together with their cdate and ctotal fields in MySQL, covering the use of DISTINCT, GROUP BY, handling ONLY_FULL_GROUP_BY mode, and alternative solutions such as GROUP_CONCAT with DISTINCT.
This article demonstrates how to generate, insert, and query ten‑million‑row MySQL tables, measures the latency of ordinary LIMIT pagination, analyzes the impact of offset size and result set size, and presents practical optimization techniques such as sub‑query pagination and ID‑range filtering to dramatically improve query speed.
This article explains how Apache Doris implements a high‑performance, column‑oriented inverted index to address the challenges of massive, real‑time log data storage and analysis, delivering dramatically higher write throughput, lower storage costs, and faster query performance than traditional Elasticsearch and Loki solutions.
This article walks through creating a 10‑million‑row MySQL table, measuring pagination query times under different offsets and result sizes, and presents practical optimizations such as sub‑queries, ID‑range filtering, and column selection to dramatically improve performance while explaining why using SELECT * is discouraged.
This article explains OceanBase's write throttling feature, describing the two main parameters, the internal implementation logic, source‑code analysis, configuration steps, and practical usage scenarios for controlling write speed and ensuring system stability.
This guide explains how to use MyBatis dynamic SQL tags such as foreach, if, where, choose, trim, and selectKey, providing practical examples for conditional queries, batch inserts, updates, and reusable SQL fragments to write flexible and error‑free MySQL statements in Java applications.
The SQLE 2.2305.0‑pre1 release introduces a MySQL‑instance‑based smart scan for slow queries, adds configurable data source defaults, fixes several MySQL rule and workflow bugs, and provides comprehensive documentation, download links, and usage examples for both community and enterprise editions.
This article provides a comprehensive overview of Apache Doris, detailing its origins, MPP‑based analytical capabilities, data‑lake integration techniques, recent architectural enhancements, performance optimizations, community growth, and upcoming development plans, while also addressing common user questions.
This guide walks you through installing DataGrip, configuring drivers, customizing settings, and using its powerful SQL editing, navigation, result‑set filtering, export/import, and shortcut features to streamline database development and administration tasks.
This article explains why correlated subqueries are a performance bottleneck, introduces the Apply (Correlated Join) operator and a systematic set of transformation rules that push Apply down and replace subqueries with efficient joins, covering scalar, existential, and quantified subqueries, aggregation handling, and set operations, all illustrated with TPC‑H examples.
This guide explains how to pinpoint the MySQL statements that use the most CPU by mapping OS thread IDs to MySQL sessions, querying performance_schema and information_schema, and reviewing the execution plan to identify optimization opportunities.
This comprehensive guide covers MySQL basics such as join types, normal forms, data types, indexing strategies, lock mechanisms, transaction isolation levels, replication, sharding, and practical optimization techniques for handling large tables and high‑traffic workloads.
This article explains MySQL index fundamentals, focusing on the leftmost prefix principle, common index failure scenarios, the impact of SELECT *, range queries, functions, LIKE patterns, OR, IN, ORDER BY, and provides practical examples and optimization tips.
This article explains how MySQL handles queries that involve two separate indexed columns, demonstrates the index_merge execution plan with real‑world examples, details the three index‑merge strategies (intersect, union, sort_union), and shows how to control or disable the feature for better performance.
This article examines the most frequent reasons MySQL queries become slow—including missing or ineffective indexes, deep pagination, massive tables, excessive joins, IN‑list overload, dirty pages, ORDER BY file‑sort, lock contention, and hardware limits—while offering concrete optimization techniques and best‑practice recommendations.
This article explains how MySQL implements partition pruning, describing the conditions under which the optimizer can skip irrelevant partitions, the internal data structures such as read_partitions and lock_partitions, the role of SEL_TREE and SEL_ARG, and provides a detailed example with code to illustrate the pruning process.
This article explains how to verify whether OceanBase's freeze mechanism and memstore dump processes are functioning correctly by querying internal tables, inspecting logs, and checking related metrics such as active_memstore_used, major_freeze_trigger, and MemTable reference counts.
This article presents a practical comparison between Elasticsearch and ClickHouse, detailing their architectures, Docker‑Compose deployment, data ingestion pipelines, a series of representative queries, and benchmark results that show ClickHouse generally outperforms Elasticsearch in basic search and aggregation scenarios.
This article explains why correlated subqueries are costly, introduces the Apply (correlated join) operator, and presents a series of systematic transformation rules that convert scalar, existential, and aggregate subqueries into efficient join‑based plans while preserving SQL semantics.
This guide walks you through downloading and installing the free, open‑source DBeaver tool, configuring connections to various databases such as PostgreSQL and Oracle via JDBC, testing those connections, and using the built‑in ER diagram feature to visualize database schemas.
Effective PostgreSQL performance tuning requires a holistic approach, covering hardware sizing, OS sysctl tweaks, filesystem choices, query and schema design, key PostgreSQL parameters, concurrency settings, cache management, and careful scheduling of automated maintenance tasks to avoid resource conflicts.
OpenMLDB is an open‑source machine‑learning database that delivers a production‑grade, consistent online‑offline feature platform for real‑time AI applications such as recommendation, risk control and fraud detection, offering millisecond‑level feature computation, dual SQL engines, extensive ecosystem integration, and a roadmap of new capabilities.
This article walks through MySQL 8.0.32 source code to explain how the server builds a tree for a WHERE clause, evaluates each condition using classes such as Item_cond_and, Item_cond_or, Item_func_gt, and Item_func_eq, and ultimately decides whether a row satisfies the query.
This article compares Elasticsearch and ClickHouse by describing their architectures, demonstrating a Docker‑compose test environment, executing equivalent queries via both systems, and presenting performance results that show ClickHouse generally outperforms Elasticsearch in basic search and aggregation scenarios for log data.
This article explains how SQL, built on relational algebra, became the standard analysis language for OLAP engines, covering its history, data models, syntax, functions, aggregation techniques, window functions, subqueries, and practical optimization considerations for modern data warehouses.
This article examines a MySQL deadlock caused by concurrent INSERT operations on a unique index, detailing the detection, reproduction, root cause analysis using InnoDB status, performance_schema data, and provides scripts and recommendations to trace and resolve such deadlocks.
This article introduces ClickHouse as an OLAP columnar database, explains its advantages over row‑based systems, details a real‑world migration from MySQL, shows performance benchmarks, shares practical installation and data‑sync methods, and highlights common pitfalls and solutions.
This weekly roundup curates the latest high‑quality technical shares on databases, featuring articles about PolarDB HTAP acceleration, MySQL datetime precision, new MySQL releases, ChatGPT as a database client, and the SQLE platform's recent enhancements and upcoming plans.
This article explains how MySQL 8.0.32 expands the '*' in a SELECT statement into all table columns, detailing the lexical parsing, query preparation, and the functions Item_asterisk::itemize, Query_block::prepare, Query_block::setup_wild, and insert_fields through source‑code excerpts.
The article analyses SQL’s fundamental shortcomings—its lack of ordered collections, incomplete set semantics, and missing object‑reference support—illustrates these issues with concrete query examples, and argues that the open‑source Structured Process Language (SPL) solves them with true ordering, full collection handling, and native reference mechanisms.
This article explains MySQL's index‑merge optimization, detailing how the EXPLAIN output indicates its use, the three underlying algorithms (intersect, union, sort‑union), practical query examples, configuration flags, and step‑by‑step guidance for testing with sample tables and indexes.
This guide explains how to use the OceanBase server_permanent_offline_time parameter to permanently offline a faulty node, rebuild its data, and restore normal operation, including preparation, command steps, verification, and recommended settings for production.
This article introduces Alibaba's Druid database connection pool, explains its core features and filters, shows how to add the Maven starter, configure properties and filters in Spring Boot, demonstrates the built‑in monitoring pages, slow‑SQL logging, Spring AOP integration, and provides methods to remove the default advertisement and retrieve monitoring data via code.
This guide introduces DataGrip, a JetBrains database client, covering download, installation, theme selection, driver management, connection setup, SQL editing shortcuts, data export/import, table creation, navigation, and various productivity features for efficient database development.
The article introduces the SQLE 2.2304.0-pre2 preview release, outlines the tool’s purpose for database users and administrators, details new enterprise‑level features such as affected‑row statistics and enhanced rule severity display, lists added MySQL audit rules, and provides complete release information and resource links.
This guide introduces DBeaver, a free Java‑based database management tool, covering its cross‑platform installation, how to create connections for databases like PostgreSQL via JDBC, and how to generate ER diagrams from existing schemas, all illustrated with step‑by‑step screenshots.
This article describes how Meituan addresses the rapid growth of Flink SQL jobs by introducing fine‑grained TTL and concurrency settings, an editable execution plan for state migration, pre‑analysis compatibility checks, and a bytecode‑instrumented debugging system that captures operator data and streams it to Kafka for analysis.
This article explains how OceanBase's recycle bin works, covering error causes when flashing back tables, methods to obtain a table's database, handling of multiple same‑name tables, visibility across tenants, and the ability to flash back tables or tenants even after the recycle bin is disabled.
This article details Meituan's implementation of Flink SQL at scale, covering fine‑grained job configuration, state‑TTL management, state‑migration techniques for job upgrades, a custom debugging tool for correctness issues, and future directions for Flink SQL enhancements.
The SQLE 2.2304.0-pre1 release introduces a preview version of the SQL audit and quality control tool with detailed project information, enhanced suggestion features for audit results, and a concise list of new functionalities and release details for database users and administrators.
This article presents a senior architect’s comprehensive MySQL optimization guide, covering execution process, naming conventions, storage engine choices, indexing strategies, query writing best practices, transaction handling, partitioning, and other performance‑tuning techniques to improve database efficiency and reliability.
This article explains how the default PL/SQL delimiter '/' in OceanBase Oracle tenants conflicts with division operators, causing syntax errors, and demonstrates fixing the issue by changing the delimiter to '//' with detailed code examples and execution results.
This article explains MySQL's date and time manipulation functions—including addtime, adddate, date_add, subtime, subdate, and date_sub—showing their syntax, usage with numeric and INTERVAL arguments, and providing example queries for adding or subtracting seconds, minutes, days, weeks, months, and years.
The 2023 DAMS China Data Intelligence Management Summit in Shanghai featured a technical presentation by Zhang Shenbo on an open‑source SQLE solution for SQL quality control, covering multi‑database auditing, automated review workflows, and practical tips to reduce DBA workload and cross‑department communication.
This article demonstrates how to generate millions of test rows in MySQL, analyzes the performance impact of deep pagination using LIMIT, explains why non‑clustered index lookups cause costly table scans, and presents two optimization approaches—sub‑query ID filtering and key‑set pagination—to dramatically reduce query latency.
This article explores MySQL single‑table row limits, demonstrates how to generate massive test data, explains InnoDB page and index structures, calculates theoretical maximum rows based on B+‑tree parameters, and provides practical tuning tips for handling large tables.
When a mistaken UPDATE statement overwrites an entire Oracle table, this guide explains four practical recovery techniques—including transaction rollback, flashback queries, LogMiner undo extraction, and RMAN backup restoration—detailing their prerequisites, commands, and trade‑offs.
This tutorial walks through setting up Liquibase in a Spring Boot project, creating a Maven plugin to generate XML/YAML changelogs, configuring the application, and performing common database operations such as creating tables, adding columns, creating indexes, initializing data, while also addressing a common includeAll path issue.
This article explains why developers often include the redundant condition “WHERE 1=1” in SQL statements, demonstrating through performance tests that it has no effect on execution time, and describing its practical use for simplifying the addition of further conditions in both static and dynamic queries.
SCQL, an open‑source Secure Collaborative Query Language built on multi‑party computation, enables SQL‑style privacy‑preserving data analysis for small‑to‑medium organizations by offering easy integration, fine‑grained column‑level access control, broad data‑source support, and optimized performance for collaborative queries.
This article explains MySQL index covering and how avoiding table lookups (back‑to‑table) can reduce retrieval steps, improve query performance, and provides practical examples, execution‑plan analysis, and optimization tips for large tables in production.
The article explains the concepts, differences, and practical usage scenarios of local (partition‑aligned) and global indexes in OceanBase, providing SQL examples for queries with and without partition keys and guidance on creating unique indexes using either index type.
This article examines how major SQL parsers like ANTLR and Calcite implement the LIKE operator, compares regex‑based, naïve, and finite‑state‑machine approaches, and presents optimizations that achieve linear‑time performance for most pattern matching scenarios.
The article reviews Apache Flink 1.17's major batch and streaming improvements, including new Delete/Update APIs, performance boosts, SQL client gateway, checkpoint and watermark enhancements, StateBackend upgrades, and practical use‑case scenarios for data engineers.
This article demonstrates how to generate two million MySQL rows, examines the performance impact of large OFFSET values in LIMIT clauses, explains why non‑clustered indexes cause back‑table lookups, and presents two optimization strategies—using sub‑queries to fetch IDs and employing key‑based pagination—to dramatically speed up deep page queries.
The article explains three ways to perform batch inserts with MyBatis—ordinary single-row inserts, foreach‑based bulk inserts, and ExecutorType.BATCH—analyzes their SQL logs, performance bottlenecks, MySQL limits, and provides practical recommendations for large‑scale data insertion.
This article outlines nine typical problems that arise when executing SQL scripts in production—such as missing scripts, syntax errors, wrong order, timing issues, wrong database selection, long‑running updates, lack of rollback, missing indexes, and field renaming—and provides practical solutions to prevent each pitfall.
This article walks through common SQL development scenarios on ODPS, examining why naïve UNION and COUNT DISTINCT can be slow, how to rewrite queries with GROUP BY, UNION ALL, JSON aggregation, and map‑join techniques, and shows the resulting execution‑plan improvements with concrete code and performance numbers.
This article provides a detailed tutorial on installing, configuring, and efficiently using JetBrains' DataGrip database client, covering connection setup, UI navigation, SQL editing shortcuts, data import/export, and advanced features for streamlined database management.
Apache Flink 1.17 introduces a suite of batch and streaming enhancements—including a new Streaming Warehouse API, significant TPC‑DS performance boosts, adaptive batch scheduling, improved checkpointing, expanded SQL capabilities, Hive connector upgrades, and broader filesystem support—while also delivering upgrades to FRocksDB, Calcite, and the token framework to strengthen its position as a leading unified data‑processing engine.
