This article explains SQL’s declarative nature, the difference between its syntactic and execution order, core concepts like table references, joins, derived tables, GROUP BY, and common keywords, providing ten clear steps and examples to help developers master SQL queries.
Oracle’s UPDATE and MERGE statements can both modify data, but depending on indexing, join conditions, and query complexity their execution speed varies dramatically; this article explains when each is appropriate, outlines their drawbacks, and provides real‑world examples with execution plans and optimization techniques.
This guide explains why a numeric column turned null, compares using the volatile v$sql view with a permanent logging solution, and details step‑by‑step how to create a tablespace, logging table, stored procedure, and scheduled job to capture all Oracle SQL statements.
This tutorial details the complete process of moving a single‑node DB2 v9.7 PHSDB database to K‑DB11G, covering schema‑to‑user mapping, object conversion, constraint handling, identity column emulation, manual adjustments for views/functions, and the export‑import scripts required for data migration.
This article compiles a comprehensive set of Oracle SQL queries that monitor wait events, rollback segment contention, tablespace and file system I/O ratios, SGA hit rates, dictionary cache efficiency, MTS activity, fragmentation, and other critical performance metrics, often with recommended threshold values.
This article examines the common reasons MySQL performance may lag—outdated hardware, sub‑optimal database design, and poorly written application code—and provides concrete guidance on CPU, memory, storage, schema, and query optimizations to improve response times.
This article compares relational SQL statements with MongoDB’s document‑oriented commands, presenting a side‑by‑side mapping of concepts and CRUD operations, illustrated with example documents and images to help developers quickly grasp MongoDB’s approach.
This article explains Oracle trigger fundamentals, syntax, and a series of practical examples—including preventing weekend updates, auto‑incrementing IDs, logging DML actions, aggregating department salaries, capturing deletions, view‑based inserts, salary change alerts, and tracking CREATE/DROP operations—complete with full SQL code snippets.
This guide outlines concrete strategies to reduce I/O and CPU overhead in Oracle queries, covering scope reduction, partition pruning, join simplification, hint usage, bind variables, and common pitfalls like Cartesian products and inefficient filters.
This article introduces MySQL 5.7's sys schema, explains its purpose and components—including views, functions, and stored procedures—demonstrates practical queries for redundant indexes, unused indexes, and full table scans, and discusses potential future extensions.
This guide explains how to use MySQL’s MERGE storage engine to merge identically structured MyISAM tables into a single logical table, covering creation, querying, INSERT handling, adding new members, and important caveats such as avoiding primary keys.
This article compares NoSQL and SQL databases, highlighting NoSQL’s strengths in scalability, flexible schemas, and performance, while also discussing its lack of unified standards, operational complexity, and why SQL remains essential for strong consistency and transactional integrity in critical systems.
This article explores the major enhancements in Oracle 12c's optimizer, including session‑level statistics for global temporary tables, new DBMS_STATS reporting procedures, adaptive features, advanced join elimination methods, incremental and concurrent statistics collection, and automatic column‑group detection, providing practical guidance for DBAs and developers.
This guide lists frequent MySQL error messages, explains their underlying causes, and provides step‑by‑step solutions—including configuration tweaks, permission adjustments, and server‑side fixes—to help developers quickly resolve database connectivity and query issues.
This guide explains step‑by‑step how to offline Oracle datafiles or tablespaces, copy them to a new directory, rename them, recover if needed, bring them back online, and safely shut down and restart the database to complete the migration.
This guide presents twelve practical Hive tuning techniques—including avoiding MapReduce, limiting string concatenation, steering clear of subqueries, choosing the right file formats, managing vectorization, sizing containers, enabling statistics, and optimizing joins—to dramatically improve query speed on Hadoop.
This article explains Oracle's parallel execution features—including parallel query, DML, and DDL—covers object, session, and statement level settings, provides practical SQL examples, discusses common pitfalls, and presents performance test results that demonstrate significant speed improvements for large data migration tasks.
This article explains how the Oracle oerr utility works as a shell script, shows how it maps facilities to components, locates the appropriate message files, and demonstrates using sed and awk to extract detailed error descriptions, causes, and actions for any ORA code.
This article highlights thirteen often‑overlooked MySQL details—including case‑insensitive column names, LIKE pattern rules, REPLACE and INSERT ON DUPLICATE KEY syntax, EXPLAIN usage, index limits, replication methods, partitioning, slow‑query logging, binlog, advanced objects, and useful client tools.
Oracle profiles let administrators enforce password policies and resource limits such as CPU time, I/O, session count, and idle periods, with detailed parameters for both kernel resources and password rules, and the article provides a complete example of creating a custom profile using SQL.
This article compiles 17 common C++ backend interview questions covering algorithms, data structures, language nuances, Linux process handling, SQL, networking, and system design, providing concise explanations and complete code examples to help candidates prepare effectively.
This guide explains Oracle's auditing capabilities, covering its purpose, three audit types, how to enable auditing via AUDIT_TRAIL, defining audit policies, controlling record generation, locating audit records, special considerations, and provides practical SQL examples.
This guide provides Oracle SQL queries to list currently running statements, retrieve historical SQL, identify high‑CPU or high‑disk‑usage queries, find slow statements, and detect uncommitted transactions, enabling DBAs to quickly diagnose performance issues.
This guide explains how to use SQL's GROUP BY clause with examples covering basic grouping, GROUP_CONCAT, aggregate functions, HAVING filters, multi‑column grouping, WITH ROLLUP, and inner, left, and right joins, illustrating each technique with clear query results.
This article explains Oracle's idle and non‑idle wait events, shows how to query the v$event_name view, counts and classifies events, introduces related performance views, and provides detailed descriptions and parameters for 33 frequently encountered wait events.
This guide provides step‑by‑step instructions for connecting to MySQL, creating and managing users and permissions, displaying, creating and deleting databases and tables, performing backups and restores, and executing various query operations such as selecting, updating, and aggregating data.
This guide shows how NULL values break numeric calculations in PostgreSQL and demonstrates using the COALESCE function to substitute default numbers, with step‑by‑step SQL examples that turn unexpected NULL results into accurate sums.
Discover six powerful techniques—including B‑tree and bitmap indexes, materialized views, result‑set caching, and clever query rewrites—to dramatically reduce logical reads and accelerate Oracle's SELECT COUNT(*) performance, with step‑by‑step examples and measurable improvements.
This report details a series of MySQL InnoDB experiments that examine transaction locking, lock types, gap and next‑key locks, and deadlock behavior across various SQL operations, providing practical insights for optimizing concurrency and preventing deadlocks.
This guide shows how to split a delimited string into a one‑dimensional array in Oracle SQL using a custom hierarchical query, avoiding the restrictions of DBMS_UTILITY.UNCL_ARRAY and allowing any delimiter for flexible data processing.
A comprehensive collection of Oracle SQL statements helps database administrators quickly retrieve tablespace sizes, data file details, rollback segment information, control and log files, usage statistics, object metadata, version data, long‑running queries, partition parameters, locked objects, resource‑intensive sessions, lock and wait statuses, SGA metrics, cached objects, and SQL area details.
This talk explores how Meipai designed a scalable, reliable database architecture that balances SQL and NoSQL technologies to support rapid product iteration and high‑peak traffic, highlighting key considerations for storage service reliability and performance optimization.
This article reveals why Oracle’s CREATE TABLE documentation spans dozens of pages and walks readers through essential Oracle database concepts such as tables, indexes, sequences, views, clusters, tablespaces, database links, and synonyms to help master the platform efficiently.
The article reviews eight expert presentations from the 2015 SDCC Database Practice Forum, covering distributed database architectures, performance tuning, high‑availability solutions, and practical case studies from leading Chinese internet companies.
This article examines why PostgreSQL claims to be the most advanced open‑source database by detailing its object‑relational model, extensive native data types, large‑scale size limits, robust ACID compliance, powerful indexing options, flexible virtual table features, built‑in functions, language extensions, and how these capabilities compare to MySQL, MariaDB and Firebird.
This article walks through practical Oracle SQL optimization methods—explaining execution plans, indexes, statistics, hints, and partitioning—using concrete examples and step‑by‑step case studies that show how to dramatically reduce query runtimes and CPU load.
This article examines a real‑world case from Alibaba’s Taobao Jushita platform, showing how traditional SQL queries struggle with multi‑dimensional, high‑volume data and how integrating Solr’s inverted‑index search engine—combined with Hive‑generated wide tables and custom QParser plugins—delivers millisecond‑level, scalable query performance for buyer analytics.
Oracle 12c’s In‑Memory Option (IMO) lets you store table, partition, and materialized view data in a columnar format within the SGA, offering faster query performance, configurable compression, priority settings, and detailed management via INMEMORY clauses, parameters, and specialized dictionary views.
This article explains why sparse data in Oracle tables hampers continuous time‑series reporting, introduces the Partitioned Outer Join syntax introduced in Oracle 10g, and demonstrates step‑by‑step how to transform one‑dimensional and multi‑dimensional gaps into dense datasets using practical SQL examples.
This article explains how to query sharded tables by routing single‑value and multi‑value lookups using hash functions, handling non‑sharded fields, and performing cross‑database joins, illustrating each case with SQL examples and diagrams of shard selection.
This article presents two Oracle SQL challenges from the 3rd ITPUB "Shengtou Media Cup" Tic‑Tac‑Toe contest, detailing how to generate every possible terminal game sequence for insertion into a table and how to compute forced win strategies for any given board using pure SQL techniques.
This article explains a practical method for performing pagination on a sharded table by querying each database up to the target offset, merging and sorting the results, and extracting the required page while discussing performance drawbacks and an optimization when data distribution is uniform.
This article presents the full specifications and solution approaches for a two‑part Oracle SQL challenge that requires inserting every possible terminal Tic‑Tac‑Toe game state into a table and writing a query to determine forced wins from any valid board configuration.
Presented are two Oracle‑SQL challenges from the 3rd ITPUB “Shengtou Media Cup” Tic‑Tac‑Toe contest: (1) generate every possible terminal move sequence and board state, inserting them into a TICTACTOE table without PL/SQL in WITH clauses, and (2) given any valid board, compute the forced winner and the minimal remaining moves using recursive SQL and minimax logic.
This article explains how the OraZ toolset streamlines Oracle database administration by automating routine tasks, providing intelligent hang analysis, offering comprehensive health checks, and delivering real‑time instance activity insights, ultimately reducing DBA workload and improving operational efficiency.
The article details the ITPUB "Shengtou Media Cup" SQL contest, presenting two Oracle‑based Tic‑Tac‑Toe problems—one to list every possible terminal game sequence and insert it into a table, and another to compute forced wins from any valid board—along with solution approaches and extra challenges.
This article explores the mysterious Oracle DUAL table, detailing its purpose, how it behaves during different instance startup phases, the performance‑boosting FAST DUAL feature, practical tests that reveal hidden bugs across 10g‑12c, and safe methods for recreating or modifying the table.
Discover ten practical PostgreSQL techniques—including CTEs, custom .psqlrc settings, pg_stat_statements, FDWs, array handling, materialized views, window functions, pivot shortcuts, PostGIS, and JSONB—to write cleaner queries, improve performance, and leverage advanced database features.
This guide explains how to create a dedicated MySQL database and trigger to continuously pull Zabbix alarm information, transform it, and store it in a format suitable for integration with external systems.
This article explains DB2 index scan methods, how predicates are applied on indexed columns, practical guidelines for designing efficient indexes, ways to avoid redundant or overly complex indexes, and step‑by‑step techniques for deep index cleanup using DB2 utilities and system catalog queries.
This article explains MySQL's query cache mechanism, covering its workflow, configuration parameters, cache‑management commands, internal data structures, experimental verification steps, and practical recommendations for when to enable or disable the feature.
This article explains essential database design principles—including normalization, denormalization, join avoidance, and various sharding techniques—while also covering scaling strategies such as vertical upgrades, horizontal partitioning, and the use of flash storage to boost performance.
Time‑series data, defined by a timestamp field, appears everywhere, and the article explains how to choose an appropriate time‑series database by comparing two schema models, their query patterns, performance trade‑offs, and why modern solutions like Elasticsearch, columnar stores, and Druid excel at real‑time massive aggregation.
The ITPUB community revived its SQL Database Programming Contest, outlining judges, prize tiers, and detailed rules, then presenting two Oracle‑SQL puzzles—one to generate every possible tic‑tac‑toe endgame board and another to determine a forced winning strategy for a given board—plus an optional extension for larger m,n,k games.
This article explains how implicit lock requests in PostgreSQL can lead to surprising lock waits, demonstrates debugging steps with lock_debug and a reporting script, and offers practical tips like lock_timeout and statement_timeout to prevent prolonged blocking scenarios.
This comprehensive guide explains the essence of Oracle SQL tuning, presents a step‑by‑step optimization roadmap, walks through real‑world case studies—including a high‑CPU query fixed with hints and query rewrites—and details how to capture and interpret accurate execution plans while addressing optimizer parameters and common pitfalls.
This guide explains two flash‑sale scenarios—single‑item seckill with a unique index and multi‑item seckill with quantity tracking—detailing table structures, SQL statements, concurrency handling, advantages, drawbacks, and an optimized version without locks, illustrated with step‑by‑step diagrams.
The announcement details three internal technical training sessions covering XML serialization fundamentals, .NET parallel programming basics, and essential SQL query concepts and optimization, including instructors, topics, locations, target audiences, and scheduled times for each course.
This article explains how MySQL versions up to 5.5 process and optimize views, outlines the exact conditions required for view merge rewriting, compares derived table handling with the SQL standard, and provides concrete test cases to illustrate performance pitfalls.
A father turned his daughter's third‑grade math challenge—finding a five‑digit number that becomes its reverse when multiplied by four—into a collaborative coding exercise, showcasing three distinct Oracle SQL solutions that generate and test digit permutations to reveal the unique answer.
This article explains why concurrent read‑write operations on a shared balance can cause inconsistencies, illustrates the problem with a purchase‑balance example, and shows how a Compare‑And‑Set (CAS) update clause guarantees atomicity and preserves data integrity.
This article explains why using SELECT * is problematic and demonstrates a simple technique—adding a computed column that triggers a divide‑by‑zero error—to block SELECT * queries while still allowing column‑specific selections.
This article examines three SQL pagination strategies for a 100‑million‑row favorites table, compares their correctness and performance using EXPLAIN analysis, and demonstrates why a GROUP BY approach with proper indexing yields the most reliable and fast results.
This article explains how MySQL's query cache works, why it uses the full SQL statement as a key, and lists ten typical situations—such as using SQL_NO_CACHE, volatile functions, or locks—that prevent a query from being cached.
This article explains how to translate a classic SQL many‑to‑many schema for articles and tags into a MongoDB design, showing document structures and practical queries for listing tags, retrieving articles, and filtering by specific tags.
This guide explains how to use the SQL EXPLAIN statement to analyze query execution plans, detailing each output column such as id, select_type, table, possible_key, key, key_len, type, rows, and extra, and interpreting their meanings for effective optimization.
This article explains the purpose of database normalization, outlines its benefits and drawbacks, and walks through a concrete demo that transforms an unnormalized employee table through the first, second, third, BC, and fourth normal forms, illustrating each step with diagrams.
Formatting SQL statements doesn't speed up execution, but it dramatically enhances readability, maintenance, and development efficiency, making testing easier and reducing errors through clear alias usage, leading commas, and line‑by‑line JOIN placement, as illustrated by practical examples.
This article compares SQL and NoSQL databases, outlining their core characteristics, advantages, and trade‑offs, and demonstrates practical schema designs for contact lists, social networks, and warehouse management, helping developers decide which technology best fits their project requirements.
This article explains why replacing OR conditions with UNION queries in MySQL often yields faster execution, using a concrete users table example, EXPLAIN analysis, and a comparison of type and ref values to illustrate the performance advantage of UNION over OR.
This article compares traditional relational tables with document‑oriented NoSQL, showing how JSON‑based storage simplifies handling multiple phone numbers, emails, addresses, and evolving contact data without schema changes or complex joins.
An in‑depth guide shows how to illustrate various SQL join types—including inner, full outer, left outer, and cross joins—using Venn diagrams and concrete table examples, highlighting result sets, null handling, and the pitfalls of Cartesian products for large tables.
This article explains two key relational database design techniques: using auto‑increment or UUID physical primary keys for foreign key relationships, and employing optimistic locking with a version column to safely handle concurrent updates, while also covering when locks can be omitted.
This guide explains how to use MySQL's show processlist command, interprets each column and common state values, and offers practical tips for diagnosing and resolving performance problems such as sleeping connections, locked queries, temporary tables, and query cache issues.
This article explains why using a physical primary key for foreign‑key relationships, how to implement optimistic locking with a version column for concurrent updates, and when it’s safe to update records without any locking, providing practical SQL examples for reliable database design.
This comprehensive guide explores MySQL's unique characteristics, hardware and OS tuning, configuration tweaks, schema and SQL optimization techniques, and provides actionable recommendations and tools to identify and resolve common performance bottlenecks.
This article chronicles the historical shift from early hierarchical data models to relational SQL, the rise of NoSQL, and the emergence of NewSQL solutions like Alibaba's DRDS, illustrating key concepts with diagrams, code examples, and a detailed Q&A.
This article explains the step‑by‑step order in which MySQL processes a SELECT statement, detailing how each clause creates a virtual table that feeds into the next operation until the final LIMIT result is returned to the user.
This article details a multi‑stage performance overhaul of a large‑scale chat application's SQLite database, covering trace‑based I/O analysis, view caching with Fragments, table‑splitting experiments, index size reduction, and the development of a PageTracer tool to quantify page‑level gains.
This guide shows how to use MySQL's information_schema tables to retrieve the total size of a database, individual table sizes, index sizes, and detailed per‑table statistics such as row count and combined data and index space, using concise SELECT statements.
MySQL’s Query Cache stores the results of SELECT statements by hashing the query, keeping the result set in memory, and invalidating cached entries whenever related tables change, allowing identical queries to be served instantly without re‑execution.
This article explains why GROUP BY requires sorting like ORDER BY, how a suitable index can satisfy both operations, and outlines the precise conditions—including single‑table queries, left‑most index prefixes, constant values, and limited aggregates—that allow MySQL to execute GROUP BY using an index scan.
Learn practical techniques to reduce nested loop iterations, prioritize inner-loop optimization, ensure join columns are indexed, and configure join buffers effectively, so your SQL joins run with minimal resource consumption and maximum performance.
MySQL uses only the Nested Loop Join algorithm, iterating each row of the left table and comparing it with every row of the right table, and when multiple tables are involved the intermediate results are repeatedly joined, making proper indexing crucial for performance.
This article presents a comprehensive set of MySQL performance optimization techniques for web applications, covering query caching, EXPLAIN analysis, LIMIT usage, indexing strategies, join type consistency, avoiding ORDER BY RAND(), selective column retrieval, proper primary keys, ENUM usage, prepared statements, unbuffered queries, IP storage, fixed‑length tables, vertical partitioning, batch deletions, column size reduction, storage engine selection, ORM benefits, and persistent connections.
This article outlines eight key MySQL index usage restrictions, including key length limits, unsupported data types, lack of functional and hash indexes, and conditions that prevent the optimizer from leveraging indexes, helping developers avoid performance pitfalls.
This article examines two database schema designs for a simple discussion forum, compares their table structures, highlights trade‑offs in query performance and storage, and explains why splitting tables can reduce data reads and improve overall efficiency.
This article reviews the evolution of big‑data management technologies, discusses the criteria for choosing appropriate big‑data benchmarks, compares existing benchmarks such as MapReduce tests, YCSB, BigBench and BigFrame, and provides an in‑depth analysis of the TPC‑DS benchmark and its certification status.
Discover twelve practical MySQL techniques—from avoiding dump file edits and tuning MyISAM block size to using EXPLAIN, query caching, and secure root password changes—that can boost performance, prevent data loss, and streamline database management for developers.
This article explains four situations where MySQL will ignore indexes—using a non‑leading column in a composite index, leading wildcards in LIKE queries, OR conditions with non‑indexed fields, and unquoted string literals—offering guidance to improve query performance.
This article compares MySQL client commands and features with their PostgreSQL psql equivalents, covering general command‑line options, SQL‑level operations such as showing tables, creating tables, listing databases, using databases, handling auto‑increment, process lists, global variables, and function‑level differences, plus practical code snippets.
This guide explains how to view MySQL temporary table statistics, interpret key metrics such as created_tmp_tables and created_tmp_disk_tables, and adjust tmp_table_size to keep temporary tables in memory, thereby improving query performance and reducing disk I/O.
This article explains the three types of SQL joins—inner, outer, and cross—detailing their variations such as equi‑join, non‑equi‑join, natural join, left/right/full outer joins, and provides clear example queries and result illustrations to help readers understand how to combine tables effectively.
Using LIMIT OFFSET for pagination becomes inefficient beyond thousands of rows, but by leveraging indexed ID ranges, delayed joins, or index‑only scans you can retrieve pages in milliseconds, dramatically improving performance for large datasets.
This article explains why MySQL ORDER BY may trigger a filesort when no suitable index exists, demonstrates the issue with a product query, and shows how adding a composite index on category_id and price lets the database return rows already sorted, removing the need for an extra sorting step.
This comprehensive guide explains what databases are, their evolution, key models, core DBMS components, and provides step‑by‑step instructions for installing, configuring, and securing MySQL on Linux, along with essential SQL commands and concepts.
This article provides a comprehensive overview of MySQL/MariaDB, detailing its architecture, core components, server and client programs, command-line options, data types, SQL language parts, metadata, and variable management, offering practical guidance for developers and administrators.
This article explains how to protect user privacy by using PostgreSQL's built-in pgcrypto extension to encrypt and decrypt sensitive fields such as phone numbers and ID numbers, showing practical SQL examples for creating tables, altering columns, storing ciphertext, and querying encrypted data.
This article compares two SQL counter queries, presenting a detailed MySQL version with CASE statements and variable counters alongside a concise PostgreSQL version using ROW_NUMBER, and includes an explanation of the differences and a brief author biography.
The article analyzes a slow PostgreSQL query that joins multiple tables to fetch flight numbers, compares several alternative query formulations with their execution plans, and presents a final solution using array aggregation to achieve sub‑millisecond performance despite existing indexes.
