This article walks through diagnosing a sluggish Oracle search query with many optional filters, removing unnecessary joins and function calls, applying conditional MyBatis joins, and creating composite indexes to dramatically lower the execution plan cost from 13902 to under 100.
This guide provides a comprehensive collection of Oracle SQL commands covering connection, query execution, variable handling, formatting, tablespace creation, modification, and external table operations, presented step‑by‑step for database administrators and developers.
When a sales employee's workflow records vanished after a WeChat ID change, the team traced the deletion to MySQL binary logs, extracted the relevant log entries, and used bulk UPDATE statements to replace the old user_id with the new one, fully restoring the missing data.
In a high‑pressure performance‑optimization project, a team dissected a massive, multi‑with‑clause SQL report into smaller temporary‑table queries, applied dimensional modeling, and achieved a 6‑second runtime reduction while handling complex reporting requirements across regions, contractors, and milestones.
The article compiles Meituan‑Dianping engineers' Q&A covering SQL book suggestions, relational versus NoSQL engine choices, performance tuning techniques, time‑series database uses, sharding versus partitioning strategies, query handling in distributed systems, proxy limitations, and practical advice on replication and pre‑database setups.
This article provides a detailed, side‑by‑side comparison of MySQL and PostgreSQL, covering their histories, licensing, feature sets, performance characteristics, extensions like PostGIS, indexing, clustering, transaction handling, and practical guidance on choosing the right database for specific workloads.
The article investigates why a seemingly indexed MySQL DELETE query still performs a full‑table scan, analyzes execution plans and statistics, demonstrates forcing the index, and finally rewrites the query to limit the date range, achieving a much faster operation.
This guide explains Oracle datafile concepts, how to query absolute and relative file numbers, create and add files to tablespaces, modify file size with autoextend or resize, change file availability, rename or move files, and safely delete files while observing key restrictions.
KSQL is an open‑source, distributed SQL engine for Apache Kafka that enables continuous, real‑time queries on streaming data, lowering the barrier for analysts to perform stream processing, monitoring, security checks, and analytics without writing code.
This article recounts a real‑world Oracle SQL performance case where a slow WM_CONCAT‑based query took over 30 minutes, the root cause was identified, and a rewritten SQL using window functions and proper aggregation reduced execution time to under three seconds.
This article introduces Flink’s background, architecture, and ecosystem, then demonstrates a step‑by‑step tutorial on using Flink SQL to clean and transform streaming data from Kafka, highlighting its advantages over Storm for real‑time ETL.
Learn practical techniques to reduce unnecessary I/O and CPU consumption in SQL queries, including narrowing scan ranges, using partition filters, optimizing joins, leveraging hints, bind variables, and proper use of MERGE, UPDATE, EXISTS/IN, pagination, temporary tables, and other performance‑tuning strategies.
This article recounts a year-long journey to become a data mining engineer, explaining the role’s value, required skills, tools such as Excel, Tableau, SQL, Python, Scala, Spark, and machine‑learning techniques, and offers practical steps for aspiring professionals.
The article explains how set‑based SQL queries outperform procedural, row‑by‑row approaches by showing concrete examples, AUTOTRACE statistics, and PL/SQL function alternatives, highlighting massive differences in consistent gets, recursive calls, and overall resource consumption for large production data sets.
After a disastrous ERP data loss caused by a careless drop, this article walks through the importance of backups and provides a detailed, practical MySQL recovery process using full backups, binlog extraction, and incremental restoration to retrieve lost tables.
This article compiles ten classic MySQL error scenarios—from connection limits and replication conflicts to charset issues and file‑open limits—explaining the root causes, diagnostic commands, and step‑by‑step solutions so beginners and seasoned DBAs can troubleshoot confidently.
This article teaches how to read and manipulate Oracle execution plans using hints, SQL rewrites, design features, and statistics tricks, providing step‑by‑step examples, common pitfalls, and practical scripts to achieve predictable performance without costly statistics collection.
This article shares five actionable SQL techniques—concatenating fields, removing duplicate rows, leveraging WHERE clauses, using COUNT flexibly, and selecting only necessary columns—to improve query readability and efficiency in relational databases.
At HBaseCon 2017 Asia, Alibaba showcased a series of groundbreaking HBase enhancements—including strong synchronous replication, SQL-on-HBase capabilities, cross‑cluster range data copy, and read/write path optimizations—that dramatically improve performance, reliability, and usability for large‑scale big‑data storage.
This article explains how Oracle's parallel processing feature can accelerate data‑intensive queries by configuring session, table, or hint‑based parallelism, shows the degree‑of‑parallelism formula, and presents experimental results that compare execution times under different settings.
This article lists ten classic MySQL error scenarios—from connection limits and replication conflicts to startup failures and charset issues—explaining each problem, showing the exact error output, and providing step‑by‑step commands and configuration tweaks to resolve them.
This article explains the fundamentals of Oracle SQL execution plans, how to read and interpret them, the role of statistics in plan selection, methods for gathering statistics, various techniques for obtaining execution plans, and practical tips for identifying inefficient SQL statements.
This article explains why relational database middleware is essential for scaling internet‑level workloads, describes the principles of horizontal sharding and distributed primary‑key generation, and provides a comprehensive overview of Sharding‑JDBC’s architecture, core modules, performance benchmarks, and future roadmap.
This article explains what an Oracle SQL execution plan is, how statistics guide the optimizer, various ways to retrieve plans, the classification of plan types, and practical tips for spotting and fixing inefficient plans using real‑world examples and code snippets.
Based on a 2017 Stack Overflow survey of 64,000 developers, this article explains why SQL remains the second‑most used programming language, detailing eight technical, community‑driven and practical reasons that keep relational databases indispensable despite the rise of newer technologies.
This article compiles a wide range of interview questions sourced from Glassdoor covering general topics, machine learning, statistics, programming, big‑data frameworks, SQL, and brain‑teasers, providing candidates with English translations and insights into the types of problems asked by companies such as Apple, Google, Microsoft, Uber, and many others.
This guide explains Oracle SEQUENCE objects—how they generate numeric IDs, the full CREATE SEQUENCE syntax, practical usage with NEXTVAL and CURRVAL, alteration options, querying via data‑dictionary views, and safe removal—complete with runnable SQL examples.
This article explains transaction isolation levels, dirty reads, and phantom reads, compares how major databases such as SQL Server, PostgreSQL, MySQL, Oracle, DB2, MongoDB, CouchDB, Couchbase, and Cassandra implement these concepts, and highlights the performance‑security trade‑offs of each isolation level.
This article explores three progressive implementations for a complex e‑commerce search interface, comparing a monolithic SQL solution, an optimized multi‑query approach, and a Redis‑based caching strategy that leverages sets and sorted sets for fast, scalable results.
This article walks through a real‑world Oracle pagination query that took over five seconds for just fifteen rows, analyzes why ORDER BY caused the slowdown, and demonstrates a step‑by‑step rewrite using a WITH clause and index‑driven access that reduces execution time to under one second.
This article walks through the concepts behind NoSQL databases, contrasts them with traditional SQL relational models, and provides a step‑by‑step Python implementation of a minimalist key‑value store, complete with command parsing, TCP/IP messaging, and code examples.
This article introduces Common Table Expressions (CTE) in SQL, explains how they improve query readability and enable writable temporary tables, demonstrates multi‑table modifications, advanced CRUD scenarios, implements a ring‑buffer using CTE, and discusses performance benefits and potential pitfalls.
This article walks through the step‑by‑step process of detecting, analyzing, and fixing DB2 lock‑timeout incidents in a production environment, covering configuration tweaks, script modifications, diagnostic file interpretation, and practical recommendations to prevent future deadlocks.
This comprehensive guide explains what a database is, distinguishes relational and non‑relational types, walks through MySQL client‑server architecture, details SQL syntax for creating, querying, altering, and dropping databases, tables, and data, and resolves common Chinese character‑set and collation issues with practical commands and examples.
A developer added a seemingly harmless filter to an Oracle SQL statement, causing the optimizer to switch the driving table and join method, which inflated execution time from 2 seconds to 90 seconds, prompting a deep dive into execution plans, indexing, and hint techniques.
Apache Kylin is an open‑source, distributed OLAP engine built on Hadoop that uses pre‑computed cubes to deliver sub‑second, high‑concurrency SQL queries on massive datasets, integrates with popular BI tools, offers a modular architecture, recent 1.5.x enhancements, and extensive deployment options.
This comprehensive SQLite tutorial walks beginners through what SQLite is, how to install it on Windows and Linux, essential syntax, CRUD operations, database objects, functions, and language-specific programming interfaces, providing step‑by‑step instructions and visual aids for practical mastery.
The article recaps Liulishuo engineers' experience at Spark Summit 2017, covering Spark 2.2's cost‑based optimizer, production‑ready Structured Streaming, deep‑learning support via UDFs, live demos recognizing James Bond, and insights from vendor booths and industry case studies.
This article explains why deterministic encryption like ECB is unsafe for sensitive data, introduces nondeterministic authenticated encryption, and provides a practical blind‑index technique with PHP and SQL examples for searchable encryption of fields such as Social Security numbers.
This guide provides comprehensive PostgreSQL best‑practice recommendations covering naming conventions, column design, constraints, index strategies, NULL handling, development standards, and administration tips to help users quickly adopt consistent and efficient database practices.
This article introduces SQLPad, a lightweight browser‑based SQL client that offers smooth autocomplete, instant table and column browsing, and query saving across multiple databases, and explains how to install and start it via npm for a more efficient SQL writing experience.
This article explains eight typical situations in which Oracle SQL statements cannot use indexes, illustrates each case with concrete examples and step‑by‑step rewrites, and offers practical solutions—including predicate adjustments, function indexes, and query‑transformation fixes—to improve database performance.
This article explains eight common reasons why Oracle SQL statements fail to use indexes, provides concrete rewrite techniques, shows how optimizer bugs can block index usage, and outlines practical steps for enforcing SQL development standards through automated audit tools.
This guide walks through creating an Oracle 12cR2 container database with CREATE DATABASE, explains OMF vs non‑OMF options, details the catcdb.sql/perl changes, resolves common Perl module errors, compares 10g/11g syntax differences, and provides practical fixes such as blocksize adjustments.
This article explains how Oracle's bind variable peeking influences execution plan selection, demonstrates the performance differences between full table scans and index scans with skewed data distributions, and provides practical methods—including 10046 events, v$sql_bind_capture, and AWR views—to capture and analyze bind variable values for better SQL tuning.
This article explains what NoSQL means, compares it with traditional SQL databases, and walks through creating a lightweight Python key‑value store with TCP/IP commands, illustrating schema design, indexing, query examples, and the full source code for a toy NoSQL system.
The article explains why a seemingly harmless delete statement in an Oracle RAC+DG environment can become extremely slow, covering bind variable peek, execution‑plan changes, histogram effects, AWR/ASH analysis, rolling invalidation, and clustering factor, and shows how to diagnose and mitigate each issue.
This article explains how to identify and analyze MySQL slow queries using tools like SHOW VARIABLES, EXPLAIN, mysqldumpslow, and profiling, and provides practical recommendations for configuring server variables, optimizing indexes, and improving query performance through systematic testing and best‑practice SQL design.
This article explains how Oracle uses various lock types—including DML, DDL, and internal locks—to protect shared resources, details the lock manager workflow, describes the three lock components and lock modes, and shows practical SQL examples of transaction handling and blocking detection.
This article provides a clear overview of the four main SQL query language categories—DDL, DML, DCL, and TCL—explaining their purposes, typical commands, practical examples, and why each is essential for effective database design and manipulation.
This article explains how to manage data conversion errors during Oracle data loading by using the new VALIDATE_CONVERSION function to identify problematic rows and the enhanced CAST (and TO_…) functions with ON CONVERSION ERROR defaults, providing step‑by‑step examples, SQL scripts, and practical tips for clean inserts.
This article explains how to use Oracle's driving_site hint to minimize network traffic in distributed DBLINK queries, demonstrates a banking case where execution time drops from over eight seconds to under one second, and provides step‑by‑step view‑based solutions for DML optimization.
This article explains Oracle's dynamic sampling feature, outlines typical scenarios such as temporary tables and complex filters, presents real CRM case studies with misestimated cardinalities, demonstrates step‑by‑step solutions using DBMS_STATS commands, and shows how 12c enhancements dramatically improve estimation accuracy for complex queries.
This article explains the logical and physical execution order of SQL statements, offers practical tips for returning only needed data, reducing redundant work, using temporary tables wisely, optimizing subqueries and indexes, and provides concrete code examples to improve query performance.
This article explains the root causes of MySQL character‑set mojibake, illustrates the encoding/decoding steps during data insertion and retrieval, and provides reliable methods to prevent and repair corrupted text.
SQLAdvisor, an open-source MySQL optimization utility from Meituan-Dianping, analyzes a given SQL statement—parsing its lexical structure, WHERE clauses, field selectivity, aggregations and multi-table joins—to automatically recommend missing indexes, supporting INSERT/DELETE/UPDATE/SELECT operations, filtering existing indexes, and inviting community contributions on GitHub.
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.
