This article compares how to create tables and perform upsert (insert‑or‑update) operations in MS SQL and Oracle, presenting five distinct Oracle techniques—including implicit cursor, exception handling, dual table, no_data_found, and MERGE—each with complete PL/SQL code samples.
This guide explains how to transform an ordinary Oracle table larger than 2 GB into a partitioned table using the online redefinition method (DBMS_REDEFINITION), detailing each step from feasibility checks to final cleanup with example PL/SQL code.
The article examines a high‑volume Oracle query scenario, outlines its inefficiencies when using multiple IN clauses, and compares three optimization approaches—including a single‑SQL join, UNION ALL views, and temporary tables—while providing detailed SQL examples, performance considerations, and practical recommendations.
This article examines the surprising behavior of Oracle when adding NOT NULL columns with default NULL values, explains how different Oracle versions handle the constraint, demonstrates test cases, and clarifies the underlying data‑dictionary mechanism that leads to unexpected query results.
This comprehensive guide explains every major SQL join type—including CROSS, INNER, OUTER, FULL, SEMI, ANTI, LATERAL, and MULTISET—detailing their logical behavior, practical use‑cases, alternative syntaxes, performance implications, and visual examples across PostgreSQL, Oracle, and SQL Server.
This article walks through a puzzling MySQL InnoDB deadlock case, showing the table schema, the conflicting transactions, how to read the SHOW ENGINE INNODB STATUS output, and the reasoning that reveals why the deadlock occurs and how to diagnose similar issues.
This guide explains why production SQL errors appear despite successful local tests, shows how to proactively log and monitor PHP‑MySQL errors, and provides concrete solutions for frequent MySQL error codes with example code and step‑by‑step troubleshooting tips.
Facing a tight September release deadline, the author tackled two critical Oracle SQL performance issues by analyzing execution plans, replacing a costly view, applying driver‑table hints, removing unnecessary CASTs and a random‑number function, ultimately cutting query time from 11 seconds to under 2 seconds.
A production page stalled because a complex SQL query took over 20 seconds, leading the author to dissect joins and nested subqueries, pinpoint the bottleneck, validate assumptions with quick tests, and share practical lessons on query optimization and table‑size effects.
This article explains Oracle Flashback technology—from basic flashback queries and version queries to transaction-level recovery—detailing required undo tablespace settings, essential parameters, SQL syntax, and step‑by‑step examples that demonstrate how to restore erroneous DML operations efficiently.
This article explains SQL join types, the three normal forms, table creation and alteration commands, data integrity constraints, practical query‑optimization tips, the roles and differences of clustered and non‑clustered indexes, and the distinctions between stored procedures and functions.
A developer encountered a painfully slow SELECT on an Oracle test server, discovered that an uncommitted INSERT left 852 data blocks allocated, causing massive redo size and logical reads, and learned how committing the transaction instantly restores normal query performance.
The article recounts a real‑world Oracle SQL case where a REGEXP_SUBSTR‑based IN subquery caused the optimizer to abandon an index range scan, explores why predicate pushdown fails with CONNECT BY recursion, documents attempted hints, rewrites, and the ultimate lessons for SQL performance tuning.
This guide walks through simulating a catastrophic Oracle database failure, performing incremental RMAN backups, verifying backup completeness, and executing a full restore and recovery—including spfile, controlfile, datafiles, and archived logs—so you can confidently recover a database after total data loss.
This guide explains how to calculate and interpret key Oracle hit ratios—including library cache, shared pool, buffer cache, data buffer, sort memory, and PGA—provides exact SQL statements for each metric, and offers tuning thresholds and actions to improve database performance.
This guide explains why MySQL INSERT operations can be slow, breaks down the time‑consuming factors, and provides a collection of practical techniques—such as multi‑row VALUES, LOAD DATA INFILE, table locking, and special modifiers like DELAYED, IGNORE, and ON DUPLICATE KEY UPDATE—to dramatically speed up bulk data insertion.
PostgreSQL's TOAST mechanism automatically compresses and, when necessary, moves oversized column values to an external toast table, using four storage strategies (plain, extended, external, main) that dictate compression and externalization behavior, with data exceeding roughly 2 KB triggering external storage regardless of strategy.
This article provides practical guidelines for designing efficient database schemas and optimizing SQL queries, covering row size limits, data type choices, index usage, common pitfalls in WHERE clauses, and best practices for temporary tables and transaction handling to improve overall database performance.
This article explains how to transform Dapper's parameterized SQL into the final executable statement by using a C# helper that replaces @ parameters, eliminating manual substitution and simplifying debugging of large stored procedures.
This article presents five useful Oracle SQL techniques—including string concatenation, DISTINCT, WHERE clauses, flexible COUNT usage, and selecting only necessary columns—to improve query readability, eliminate duplicate rows, and enhance overall database performance.
This article explains what execution plans are, why seemingly identical SQL statements can produce different plans, and offers practical tips—such as standardizing query syntax, simplifying nested queries, using temporary tables, bind variables, appropriate transaction scopes, and careful use of NOLOCK—to improve database performance and avoid common pitfalls.
This article walks through a real‑world MySQL performance case where a complex view caused slow queries, explains step‑by‑step analysis, shows how redundant joins and Cartesian products were eliminated with CTEs and EXISTS, and demonstrates how the execution time dropped from 2.5 s to 1.8 s.
This guide explains the two main database career tracks—DBA (database administration) and database development—covers the challenges for fresh graduates, discusses certification value, and offers practical methods for mastering database theory and hands‑on skills.
This article presents a comprehensive collection of practical SQL performance tuning guidelines, covering index usage, query rewriting, avoiding costly operators, proper data types, efficient joins, and view creation to help developers write faster, more resource‑efficient database queries.
A detailed Oracle case study shows how a seemingly harmless view modification triggered a cascade of performance regressions, how nested UNION ALLs, missing indexes, and optimizer choices prevented predicate pushdown, and the step‑by‑step rewrites—including hints, index creation, and scalar subqueries—that finally restored fast query execution.
This article explains how to represent several status flags for a single record—such as hot, new, and sale—by using binary bitmask fields, compares traditional association‑table and delimited‑string approaches, and shows Oracle SQL and Java techniques for querying and manipulating these bit‑encoded values.
This article compiles frequently used MySQL SQL commands—including data migration, multi‑table updates, conditional updates, aggregation inserts, and various ALTER TABLE operations such as adding indexes, dropping and adding columns—to serve as a practical reference for database developers.
Greenplum is an open‑source, massively parallel processing (MPP) database built on PostgreSQL, offering ANSI‑SQL compliance, distributed ACID transactions, linear scalability, polymorphic storage, advanced optimizers, and extensive ecosystem integrations, making it suitable for large‑scale data warehousing, analytics, and big‑data workloads.
After a typhoon delayed work, the author tackled a 347‑line Oracle SQL that took over four minutes to return results, dissected its massive subqueries and views, applied three rounds of restructuring with WITH clauses and driver‑table tactics, and ultimately reduced the total runtime to just 3.5 seconds while dramatically speeding up deep data extraction.
During high‑concurrency flash‑sale events, Oracle’s log file sync became a performance bottleneck; the article analyzes storage, OS, and Oracle Disk Manager factors, presents AWR metrics, demonstrates tuning steps—including disabling adaptive log file sync and enabling ODM—and shows measurable latency reductions.
This article examines a high‑volume Oracle query scenario, critiques the current multi‑IN approach, and presents three alternative solutions—including a single‑SQL join, a UNION ALL view, and the use of global temporary tables—detailing their performance implications and practical trade‑offs.
This guide explains multiple methods for creating MySQL users, deleting them, assigning granular permissions, revoking privileges, and understanding when permission changes take effect, complete with command examples and a comprehensive list of MySQL privilege types.
This guide compiles a comprehensive set of Oracle SQL statements and explanations for detecting fragmented tables, index fragmentation, high clustering factor tables, session and process mapping, DML lock analysis, DDL lock inspection, active SQL tracking, resource usage statistics, and various performance‑related metrics, helping DBAs diagnose and tune database behavior efficiently.
A real‑world case study shows how a seemingly simple intersect query that took 20 seconds was optimized to 10 seconds by analyzing execution plans, rewriting the SQL, exploring parallel execution, and ultimately redesigning the business logic with a new timestamp column to enable incremental checks.
This guide provides a comprehensive checklist and scripted procedure for safely downgrading Oracle Database 12c (Standard or Enterprise Edition) to prior releases such as 11.2.0.3, 11.2.0.2, or 11.1.0.7, covering prerequisites, command‑line steps, post‑downgrade validation, and common pitfalls.
This article compiles over forty practical Oracle SQL queries covering date manipulation, server information, data dictionary lookups, database administration, performance monitoring, and utility functions, providing developers with ready-to-use commands to streamline everyday database tasks.
This guide explains how to create MySQL users, assign and manage privileges with GRANT, FLUSH, and REVOKE commands, and provides comprehensive best‑practice recommendations for naming, table design, indexing, and SQL optimization to secure and streamline database operations.
This article enumerates Oracle's most common wait events, explains the underlying reasons each event occurs, lists the associated parameters, provides sample queries for inspection, and offers practical guidance on diagnosing and mitigating performance issues caused by these waits.
The article recounts a real‑world incident where a complex SQL query took over 20 seconds, explains how to pinpoint whether the slowdown originates from joins or nested subqueries, and shares practical steps and insights for diagnosing and fixing such performance issues.
A detailed case study describes how a rapidly growing edit‑log feature caused query times to soar to 30 seconds, and walks through the step‑by‑step investigation, identification of a custom function bottleneck, data‑volume analysis, and the eventual implementation of partitioning, mandatory time filters, and composite indexing to restore acceptable performance.
This article compiles the most discussed DBA questions from multiple WeChat groups in November, covering DB2 query latency, Oracle import/export challenges, MySQL monitoring, SQL performance nuances, partition table design, and practical solutions shared by experts.
A detailed Oracle performance case study shows how adding selective predicates and a supporting index to an UPDATE that runs hourly can reduce redo log generation from over 100 MB to under 1 MB and cut logical reads by more than 95%, with step‑by‑step measurements and code examples.
This article provides a detailed, hands‑on walkthrough of Oracle Data Guard switchover in normal operation and the subsequent steps to convert the original primary to a standby, including all necessary SQL commands, instance restarts, and verification queries.
This guide explains the most commonly used Oracle permission‑view tables, shows how to list role assignments, object privileges, system privileges, session privileges, and role memberships, and demonstrates the impact of the WITH ADMIN OPTION through practical SQL examples.
This guide explains Oracle's lock mechanisms—including shared, exclusive, row‑level, pessimistic, and optimistic locks—through step‑by‑step SQL sessions that illustrate update‑lost problems, lock contention, and deadlock detection, helping developers choose the right strategy for concurrency control.
This guide walks through MySQL fundamentals—including viewing, creating, and using databases, managing users and privileges, creating and altering tables, inserting, updating, deleting, and querying data, as well as index types, best‑practice tips, and execution‑plan analysis—providing concrete commands and examples for each step.
This guide explains Oracle datafile concepts, how to query file numbers, create and add files, adjust sizes and auto‑extend settings, toggle online/offline status, rename files, and safely drop them while observing limits and recovery procedures.
Phoenix adds a SQL layer to HBase, enabling easy table creation, data import, and complex queries via JDBC, with features like secondary indexes and integration with Spark, Hive, and more, illustrated through step‑by‑step examples and sample code.
This article explains Hive as a SQL‑based interface for Hadoop, shows why it simplifies large‑scale data analysis, provides practical command‑line examples for table creation, data loading, and queries, and details how HiveQL is internally converted into MapReduce jobs.
This guide presents a concise, experience‑based checklist for optimizing SQL SELECT statements, covering index verification, dataset reduction, column selection, unnecessary table removal, outer join elimination, and computed column cleanup, each illustrated with real‑world examples and visual aids.
This article walks through a real Oracle 11gR2 datafile loss case, explains how backup‑software snapshots caused mismatched SCNs, and details step‑by‑step recovery methods—including offline drop, header SCN editing, and forcing the database open with SCN adjustment—to successfully restore the instance.
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.
