This guide demonstrates how to use the ass109.awk script and Oracle's oradebug tool to capture, parse, and analyze trace files, quickly identifying lock and deadlock sessions in Oracle 11gR2 without resorting to costly restarts.
This guide compiles 34 practical Oracle SQL optimization techniques—from selecting the optimal table order and rewriting WHERE clauses to leveraging indexes, avoiding costly operators, and fine‑tuning execution plans—helping developers dramatically reduce query execution time and resource consumption.
This article explores Oracle's Hint feature, reviewing its classification, demonstrating how improper use can degrade performance, presenting real‑world negative cases, and summarizing Oracle's official stance and best‑practice recommendations for when and how to apply Hints effectively.
A developer recounts how a seemingly trivial MySQL UPDATE statement—‘UPDATE table_name SET sum = sum + 5 WHERE id = 1’—proved thread‑safe for aggregating file counts across a multi‑threaded module, highlighting the importance of knowledge reserves and cautious evaluation before implementing complex synchronization solutions.
This guide introduces MySQL 8.0’s new JSON aggregation functions, demonstrates how to create tables, insert data, and use JSON_ARRAYAGG and JSON_OBJECTAGG to combine rows into JSON arrays or objects, and provides a real‑world product‑attribute example that returns complete JSON structures.
This article explains why database roles are essential for efficient permission handling, outlines the benefits of using roles, and provides step‑by‑step instructions with screenshots for creating and assigning roles in MariaDB, while noting MySQL's current lack of support.
This guide explains MySQL's numeric, temporal, and string data types, their storage implications, and how to choose appropriate types, while detailing index types, creation rules, and optimization tips such as prefix lengths and B+Tree versus HASH strategies for faster queries.
This article explains how Oracle function‑based indexes work, why deterministic keywords are required, the risks of changing function definitions without rebuilding the index, and provides concrete examples illustrating common mistakes and their impact on query results.
This guide walks through Oracle's memory architecture, explains SGA and PGA components, details essential memory parameters with recommended values, and provides step‑by‑step SQL checks to diagnose and tune performance on both 32‑bit and 64‑bit systems.
This guide explains what MySQL's slow query log is, how to enable and configure its key variables, how to control what gets recorded, and how to analyze the log efficiently using the mysqldumpslow utility with practical command examples.
This tutorial explains how to create, register, and deploy custom scalar, aggregation, and window functions for the Presto distributed query engine using Java annotations, the Presto plugin mechanism, and code examples that illustrate UDF development, plugin packaging, and state handling for aggregation functions.
The article walks through a hands‑on experiment that shows how inserting the same Chinese string into an Oracle 10g database from clients using different character sets (ZHS16GBK vs AL32UTF8) leads to different stored encodings and results in garbled output, then explains the underlying conversion process and how to avoid it.
This article provides a comprehensive guide to Oracle hierarchical queries, covering fundamental concepts, key clauses and operators, practical examples with the s_emp table, advanced features like SYS_CONNECT_BY_PATH, CONNECT_BY_ISLEAF, NOCYCLE, and real‑world use cases such as reporting, pruning branches, and calculating aggregates in tree structures.
The article dissects a moderately complex Oracle SQL query from a data‑warehouse, shows how changing a parallel hint forces a full table scan that halves execution time, and then explores partitioning strategies and broader lessons on OLAP performance tuning.
This article explains how Oracle’s Cost‑Based Optimizer can produce incorrect row estimates for queries with multiple column predicates, demonstrates the problem with a test table, and shows that gathering multi‑column statistics resolves the misestimation, improving execution plans.
This article examines a recurring ORA-00600 crash caused by Oracle MERGE statements, walks through reproducing the issue, analyzes the impact of bound variables and table size, identifies a version‑specific bug, and offers practical work‑arounds and recommendations for DBAs.
The article lists eight common complaints about MySQL—from deep‑rooted bugs and inflexible relational schemas to confusing forks, storage‑engine chaos, profit‑driven licensing, weak native JSON support, and proprietary extensions—highlighting why many developers consider alternatives.
This guide demonstrates how to switch Oracle Database 11g between Automatic Shared Memory Management (ASMM) and Automatic Memory Management (AMM), covering environment setup, parameter adjustments, spfile/pfile handling, restart procedures, and troubleshooting, with concrete SQL commands and shared‑memory observations.
This guide walks through practical MySQL optimization steps, including status checks, variable inspection, slow‑query and general‑log configuration, essential management commands, detailed mysqldump usage, mysqldumpslow analysis, and the mysqlsla utility for performance diagnostics.
This guide demonstrates how to simulate a blocking situation in MySQL using two client connections, set a long lock wait timeout, execute conflicting UPDATE queries, and then query the InnoDB lock wait tables to identify blocked and blocking threads along with wait duration.
Oracle 11g’s Flashback Data Archive lets you retain UNDO information for extended periods, enabling point-in-time queries of tables; this guide walks through creating an ASSM tablespace, setting up a flashback archive, assigning tables, and using SCN-based queries to retrieve past data.
During performance testing, an Oracle SQL using bind variables showed two child cursors with identical execution plans; the article explains this phenomenon by detailing Oracle’s rolling cursor invalidation mechanism, the role of the ROLL_INVALID_MISMATCH flag, and how to reproduce and observe it.
This article walks through Oracle’s massive SQL reference, explains essential database concepts, details the original set of commands in version 6, and then chronicles the new DDL/DML features introduced in each subsequent release up to 12c, helping readers master Oracle’s syntax evolution.
This article explains how Oracle protects shared resources using enqueue queues, details the naming of queue identifiers like TM, describes the creation and composition of resource structures in the SGA, and examines the hash‑bucket mechanism that locates these structures while highlighting relevant parameters and diagnostic queries.
This guide explains why Oracle 11g's automatic optimizer statistics collection can affect 24‑hour systems, shows how to check its status, and provides step‑by‑step commands to disable, re‑enable, and verify the task, including differences from Oracle 10g.
This guide explains how to monitor Oracle UNDO tablespace usage, adjust automatic UNDO management parameters, interpret V$UNDOSTAT statistics, and safely reclaim space by creating a new UNDO tablespace and dropping the old one, helping DBAs avoid ORA‑01555 errors and space shortages.
This comprehensive presentation explores SQL’s history, its enduring relevance amid NoSQL and NewSQL trends, explains the execution order and optimizer mechanics, offers practical optimization rules and step‑by‑step execution processes, and illustrates concepts through multiple real‑world case studies and a detailed Q&A.
This article provides a comprehensive overview of SQL Server transaction concepts, ACID properties, lock types and escalation, blocking analysis using system DMVs, isolation level configurations, and deadlock detection and resolution, illustrated with practical code examples and diagrams.
This guide explains how to check the character set of an Oracle database and its client, demonstrates queries for server, instance, and session parameters, describes the risks of altering the server charset with ALTER DATABASE CHARACTER SET, provides safe conversion steps, and shows how to adjust client settings on Linux and Windows to avoid garbled Chinese text.
This guide presents thirty practical SQL optimization techniques—ranging from index usage and avoiding costly operators to rewriting expressions and minimizing temp objects—to help developers prevent full table scans and boost database performance.
Learn ten practical strategies—from careful schema design and precise SELECT statements to query caching, index creation, and smart use of JOINs, LIMIT, UNION, and wildcards—that dramatically improve database speed and reduce server load on high‑traffic websites.
This article compiles a series of lesser‑known MySQL details—from case‑insensitive column names and fuzzy LIKE patterns to REPLACE INTO, INSERT … ON DUPLICATE KEY UPDATE, EXPLAIN usage, index length limits, replication methods, partitioning, slow‑query monitoring, binlog importance, advanced objects like views and stored procedures, and recommended client tools—helping developers avoid common pitfalls and write more efficient queries.
This article examines Oracle's Reference Partition feature, demonstrating through a series of SQL experiments how it reduces query costs, improves execution plans, and streamlines partition management, while also highlighting its limitations when indexes are involved.
This article walks through Oracle 11g's Reference Partition feature, demonstrating table creation, handling ORA‑14652 errors, inserting data, and analyzing partition alignment between master and detail tables to reveal performance and management benefits.
This guide demonstrates how to create MySQL tables with JSON columns, insert JSON data, perform queries using JSON functions, and update JSON fields, covering essential functions like JSON_CONTAINS, JSON_SEARCH, JSON_EXTRACT, JSON_MERGE, and JSON_SET.
The article explains why traditional LIMIT OFFSET pagination becomes inefficient on large MySQL tables, analyzes the performance impact of counting rows and using SQL_CALC_FOUND_ROWS, and presents several optimized techniques—including index‑based counting, keyset pagination, page‑number caching, and temporary‑table approaches—to achieve fast and scalable pagination.
The article explains how a company is retiring Oracle due to cost and scalability, outlines a staged move to NoSQL and cloud storage, argues that SQL remains vital, and shares practical examples of Spark SQL rewrites and JDBC‑based Hive integration.
This tutorial demonstrates how to create a MySQL database and table, import data from /etc/passwd, add an auto‑increment primary key, export selected records to a file, and perform common SQL queries, conditions, and table‑record manipulations such as insert, update, and delete.
This article provides a step-by-step guide on creating and managing MySQL indexes (including INDEX and UNIQUE), defining primary keys with AUTO_INCREMENT, establishing foreign key relationships with cascade updates/deletes, and configuring the default storage engine, illustrated with practical SQL commands and examples.
This article explains how to define data elements, choose meaningful names, apply ISO‑11179 naming patterns, select appropriate SQL data types, and use domains, constraints, and default values to build clear, maintainable, and high‑performance relational database schemas.
A step‑by‑step guide shows how to reinstall Oracle 11g, adjust environment variables, copy essential files, recreate Oracle services, and verify the database connection to restore a Windows‑based Oracle database that was damaged by antivirus deletions.
The article offers a comprehensive, step‑by‑step tutorial on MySQL administration, covering password setting, database creation, selection, deletion, table inspection, time‑function queries, and various table‑structure modifications such as adding, renaming, and dropping columns.
This article explains two practical techniques for identifying Oracle hidden parameters—querying internal views like GV$PARAMETER and X$ tables, and using AUTOTRACE to inspect execution plans—so DBAs can reveal undocumented settings, their defaults, and current values.
This article explains InnoDB row lock types, including shared and exclusive locks and GAP variants, describes how locks split, inherit, and migrate during B‑tree operations, provides code examples and SQL demonstrations, and discusses related bugs and lock compatibility.
The article explains how a newly created Oracle user with the RESOURCE role inherits the UNLIMITED TABLESPACE privilege, allowing table creation in any tablespace, and how the 11gR2 deferred segment creation feature creates the illusion of permission, while providing methods to disable it globally or per‑session to enforce true tablespace restrictions.
The article explains the internal steps Oracle follows to acquire buffers, identifies the conditions that trigger buffer busy waits such as hot blocks and freelist limits, and provides practical tuning methods and diagnostic SQL queries to mitigate these performance issues.
An in‑depth case study of a sluggish SSRS report for Siemens China's MES system reveals how missing indexes, full‑table scans on billion‑row tables, and misguided partitioning cripple performance, and demonstrates practical SQL refactoring, stored‑procedure redesign, and indexing strategies that cut query time from hours to seconds.
This guide explains how to locate rows with identical values across one or several columns using self‑joins, GROUP BY, DISTINCT, and rowid, and provides step‑by‑step SQL statements to safely delete the extra duplicate entries from a table.
This article analyzes a poorly performing MES reporting SQL query that scans billions of rows, identifies indexing and partitioning shortcomings, and proposes a redesign using stored procedures, temporary tables, and query‑writing best practices to achieve sub‑second response times.
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.
