BestHub
Sign in
Tagged articles

SQL

2900 articles · Page 28 of 29
ITFLY8 Architecture Home
ITFLY8 Architecture Home
Dec 24, 2016 · Databases

Why Your SQL Execution Plans Fail and How to Optimize Them

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.

Bind VariablesSQLSQL Server
0 likes · 13 min read
Why Your SQL Execution Plans Fail and How to Optimize Them
Practical DevOps Architecture
Practical DevOps Architecture
Dec 8, 2016 · Databases

SQL Query Optimization Tips and Best Practices

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.

Query OptimizationSQLdatabase tuning
0 likes · 11 min read
SQL Query Optimization Tips and Best Practices
dbaplus Community
dbaplus Community
Nov 30, 2016 · Databases

Why Updating a View Can Kill Oracle Performance—and How to Fix It

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.

OraclePredicate PushdownSQL
0 likes · 13 min read
Why Updating a View Can Kill Oracle Performance—and How to Fix It
Architects' Tech Alliance
Architects' Tech Alliance
Nov 19, 2016 · Databases

An Overview of Greenplum Database Architecture and Core Components

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.

Data WarehousingGreenplumMPP
0 likes · 15 min read
An Overview of Greenplum Database Architecture and Core Components
dbaplus Community
dbaplus Community
Nov 15, 2016 · Databases

How I Cut a 4‑Minute Oracle Query to 3.5 Seconds: A Step‑by‑Step SQL Optimization Tale

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.

Driver TableOracleQuery Optimization
0 likes · 12 min read
How I Cut a 4‑Minute Oracle Query to 3.5 Seconds: A Step‑by‑Step SQL Optimization Tale
dbaplus Community
dbaplus Community
Nov 14, 2016 · Databases

Why Oracle Log File Sync Bottlenecks Appear and How to Eliminate Them

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.

Log File SyncODMOS Tuning
0 likes · 18 min read
Why Oracle Log File Sync Bottlenecks Appear and How to Eliminate Them
ITPUB
ITPUB
Nov 14, 2016 · Databases

Optimizing Large‑Scale Oracle Queries with Temporary Tables: Strategies and Trade‑offs

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.

Index ScanOracleQuery Optimization
0 likes · 10 min read
Optimizing Large‑Scale Oracle Queries with Temporary Tables: Strategies and Trade‑offs
ITPUB
ITPUB
Nov 14, 2016 · Databases

How to Create, Manage, and Revoke MySQL Users and Permissions

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.

MySQLSQLpermissions
0 likes · 9 min read
How to Create, Manage, and Revoke MySQL Users and Permissions
ITPUB
ITPUB
Nov 11, 2016 · Databases

Essential Oracle SQL Queries for Performance Monitoring and Troubleshooting

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.

AdministrationOracleQueries
0 likes · 26 min read
Essential Oracle SQL Queries for Performance Monitoring and Troubleshooting
dbaplus Community
dbaplus Community
Nov 10, 2016 · Databases

Doubling SQL Speed: From Execution Plans to Business Logic Overhaul

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.

Performance OptimizationSQLbusiness logic
0 likes · 10 min read
Doubling SQL Speed: From Execution Plans to Business Logic Overhaul
ITPUB
ITPUB
Nov 9, 2016 · Databases

Step‑by‑Step Guide to Downgrade Oracle Database 12c to Earlier Releases

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.

12cOracleSQL
0 likes · 13 min read
Step‑by‑Step Guide to Downgrade Oracle Database 12c to Earlier Releases
ITPUB
ITPUB
Nov 8, 2016 · Databases

40+ Essential Oracle SQL Queries Every DBA Should Know

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.

AdministrationOracleQueries
0 likes · 16 min read
40+ Essential Oracle SQL Queries Every DBA Should Know
ITPUB
ITPUB
Nov 8, 2016 · Databases

Master MySQL User Permissions: Creation, Grants, and Best Practices

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.

Database SecurityMySQLSQL
0 likes · 15 min read
Master MySQL User Permissions: Creation, Grants, and Best Practices
ITPUB
ITPUB
Nov 7, 2016 · Databases

Understanding Oracle’s Top 30 Wait Events: Causes, Parameters, and Tuning Tips

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.

OracleSQLWait events
0 likes · 26 min read
Understanding Oracle’s Top 30 Wait Events: Causes, Parameters, and Tuning Tips
ITPUB
ITPUB
Nov 3, 2016 · Databases

Why Nested Subqueries Slow Down Your SQL Queries and How to Diagnose Them

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.

OptimizationSQLdatabase
0 likes · 6 min read
Why Nested Subqueries Slow Down Your SQL Queries and How to Diagnose Them
dbaplus Community
dbaplus Community
Nov 3, 2016 · Databases

Taming a Million‑Row Log Table: Real‑World SQL Performance Optimization

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.

PL/SQLPartitioningSQL
0 likes · 12 min read
Taming a Million‑Row Log Table: Real‑World SQL Performance Optimization
ITPUB
ITPUB
Nov 1, 2016 · Databases

How to Slash Redo Log Generation and Logical Reads in Oracle UPDATE Statements

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.

Logical ReadsOracleSQL
0 likes · 8 min read
How to Slash Redo Log Generation and Logical Reads in Oracle UPDATE Statements
ITPUB
ITPUB
Oct 28, 2016 · Databases

Step‑by‑Step Oracle Data Guard Switchover and Failover Guide

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.

Data GuardOracleSQL
0 likes · 6 min read
Step‑by‑Step Oracle Data Guard Switchover and Failover Guide
ITPUB
ITPUB
Oct 27, 2016 · Databases

Mastering Oracle Permission Views: Query Roles, Privileges, and Sessions

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.

DBAOracleSQL
0 likes · 7 min read
Mastering Oracle Permission Views: Query Roles, Privileges, and Sessions
ITPUB
ITPUB
Oct 26, 2016 · Databases

Mastering Oracle Locks: Pessimistic, Optimistic, and Deadlock with Real‑World SQL demos

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.

Optimistic LockOraclePessimistic Lock
0 likes · 10 min read
Mastering Oracle Locks: Pessimistic, Optimistic, and Deadlock with Real‑World SQL demos
dbaplus Community
dbaplus Community
Oct 25, 2016 · Databases

Master MySQL: Essential Database Operations, Table Management, and Index Optimization

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.

MySQLSQLTable
0 likes · 14 min read
Master MySQL: Essential Database Operations, Table Management, and Index Optimization
MaGe Linux Operations
MaGe Linux Operations
Oct 20, 2016 · Databases

Boost Your SQL SELECT Performance: Proven Checklist & Practical Tips

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.

Query OptimizationSQLdatabases
0 likes · 6 min read
Boost Your SQL SELECT Performance: Proven Checklist & Practical Tips
ITPUB
ITPUB
Oct 19, 2016 · Databases

Recovering Lost Oracle Datafiles: SCN Adjustment and Open‑Database Tricks

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.

Backup FailureDatafile LossOracle
0 likes · 12 min read
Recovering Lost Oracle Datafiles: SCN Adjustment and Open‑Database Tricks
ITPUB
ITPUB
Oct 17, 2016 · Databases

Quickly Diagnose Oracle Lock Contention with AWK and ass109.awk

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.

Database LocksOracleSQL
0 likes · 9 min read
Quickly Diagnose Oracle Lock Contention with AWK and ass109.awk
ITPUB
ITPUB
Oct 12, 2016 · Databases

34 Proven Oracle SQL Tricks to Boost Query Performance

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.

OptimizationOracleSQL
0 likes · 19 min read
34 Proven Oracle SQL Tricks to Boost Query Performance
dbaplus Community
dbaplus Community
Oct 11, 2016 · Databases

Mastering Oracle Hints: Benefits, Risks, and Best‑Practice Guidance

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.

OracleSQLdatabase optimization
0 likes · 14 min read
Mastering Oracle Hints: Benefits, Risks, and Best‑Practice Guidance
ITPUB
ITPUB
Oct 11, 2016 · Databases

How a Simple MySQL UPDATE Solved a Multi‑Threaded Counting Problem

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.

MySQLSQLThread Safety
0 likes · 5 min read
How a Simple MySQL UPDATE Solved a Multi‑Threaded Counting Problem
MaGe Linux Operations
MaGe Linux Operations
Oct 9, 2016 · Databases

How to Simplify Permission Management with MariaDB Roles

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.

Database RolesMariaDBPermission Management
0 likes · 3 min read
How to Simplify Permission Management with MariaDB Roles
ITPUB
ITPUB
Oct 9, 2016 · Databases

Master MySQL Data Types and Indexes to Boost Performance

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.

Data TypesInnoDBMySQL
0 likes · 11 min read
Master MySQL Data Types and Indexes to Boost Performance
dbaplus Community
dbaplus Community
Oct 9, 2016 · Databases

When Custom Function Indexes Fail: Pitfalls and Best Practices in Oracle

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.

DatabaseOptimizationFunctionBasedIndexOracle
0 likes · 7 min read
When Custom Function Indexes Fail: Pitfalls and Best Practices in Oracle
ITPUB
ITPUB
Sep 30, 2016 · Databases

Master Oracle Memory Tuning: Key SGA & PGA Parameters Explained

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.

Memory TuningOraclePGA
0 likes · 14 min read
Master Oracle Memory Tuning: Key SGA & PGA Parameters Explained
Liulishuo Tech Team
Liulishuo Tech Team
Sep 24, 2016 · Backend Development

Developing Custom Presto SQL Functions (UDF) with Java Plugins

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.

AggregationJavaSQL
0 likes · 11 min read
Developing Custom Presto SQL Functions (UDF) with Java Plugins
ITPUB
ITPUB
Sep 21, 2016 · Databases

Why the Same Chinese Text Becomes Garbled in Different Oracle Sessions – A Character Set Deep Dive

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.

OracleSQLcharacter set
0 likes · 13 min read
Why the Same Chinese Text Becomes Garbled in Different Oracle Sessions – A Character Set Deep Dive
dbaplus Community
dbaplus Community
Sep 18, 2016 · Databases

Master Oracle Hierarchical Queries: From Basics to Advanced Techniques

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.

CONNECT BYHierarchical QueryOracle
0 likes · 25 min read
Master Oracle Hierarchical Queries: From Basics to Advanced Techniques
ITPUB
ITPUB
Sep 12, 2016 · Databases

Why Oracle’s Optimizer Misestimates Multi‑Column Filters and How to Fix It

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.

Multi-Column StatisticsOracleSQL
0 likes · 8 min read
Why Oracle’s Optimizer Misestimates Multi‑Column Filters and How to Fix It
dbaplus Community
dbaplus Community
Sep 7, 2016 · Databases

Why Oracle MERGE Triggers ORA-00600 Errors and How to Diagnose Them

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.

Database BugORA-00600Oracle
0 likes · 12 min read
Why Oracle MERGE Triggers ORA-00600 Errors and How to Diagnose Them
ITPUB
ITPUB
Sep 3, 2016 · Databases

8 MySQL Gotchas That Will Make You Rethink Relational Databases

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.

Database BugsJSONMySQL
0 likes · 9 min read
8 MySQL Gotchas That Will Make You Rethink Relational Databases
ITPUB
ITPUB
Sep 2, 2016 · Databases

How to Switch Oracle 11g Between ASMM and AMM – Step-by-Step

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.

AMMASMMOracle
0 likes · 8 min read
How to Switch Oracle 11g Between ASMM and AMM – Step-by-Step
dbaplus Community
dbaplus Community
Sep 1, 2016 · Databases

Master MySQL Optimization: Essential Commands, Logs, and Tools

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.

MySQLOptimizationSQL
0 likes · 8 min read
Master MySQL Optimization: Essential Commands, Logs, and Tools
ITPUB
ITPUB
Aug 25, 2016 · Databases

Unlock Historical Table Snapshots with Oracle Flashback Data Archive

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.

Flashback Data ArchiveOracleSQL
0 likes · 9 min read
Unlock Historical Table Snapshots with Oracle Flashback Data Archive
ITPUB
ITPUB
Aug 23, 2016 · Databases

Uncover Oracle’s Evolving SQL Commands: From Version 6 to 12c

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.

DDLDMLOracle
0 likes · 13 min read
Uncover Oracle’s Evolving SQL Commands: From Version 6 to 12c
ITPUB
ITPUB
Aug 17, 2016 · Databases

Decoding Oracle Enqueue Locks: Queues, Resource Structures, and Hash Buckets

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.

Database InternalsEnqueue LocksOracle
0 likes · 13 min read
Decoding Oracle Enqueue Locks: Queues, Resource Structures, and Hash Buckets
ITPUB
ITPUB
Aug 17, 2016 · Databases

How to Disable and Re‑Enable Oracle 11g Automatic Statistics Collection

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.

DBMS_AUTO_TASK_ADMINOracleSQL
0 likes · 5 min read
How to Disable and Re‑Enable Oracle 11g Automatic Statistics Collection
ITPUB
ITPUB
Aug 16, 2016 · Databases

Mastering Oracle UNDO Tablespace: Monitoring, Tuning, and Space Reclamation

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.

OracleSQLdatabase administration
0 likes · 18 min read
Mastering Oracle UNDO Tablespace: Monitoring, Tuning, and Space Reclamation
dbaplus Community
dbaplus Community
Aug 15, 2016 · Databases

Unlocking SQL: Why It Remains the Key to Databases and How to Optimize It

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.

Case StudiesSQLdatabase optimization
0 likes · 23 min read
Unlocking SQL: Why It Remains the Key to Databases and How to Optimize It
ITPUB
ITPUB
Aug 3, 2016 · Databases

How to View and Change Oracle Server and Client Character Sets Safely

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.

LinuxNLS_LANGOracle
0 likes · 11 min read
How to View and Change Oracle Server and Client Character Sets Safely
ITPUB
ITPUB
Jul 29, 2016 · Databases

30 Essential SQL Query Optimization Tips to Avoid Full Table Scans

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.

Query OptimizationSQLindexes
0 likes · 11 min read
30 Essential SQL Query Optimization Tips to Avoid Full Table Scans
ITPUB
ITPUB
Jul 24, 2016 · Databases

10 Proven Techniques to Supercharge Your 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.

CachingQuery DesignSQL
0 likes · 9 min read
10 Proven Techniques to Supercharge Your Database Performance
ITPUB
ITPUB
Jul 23, 2016 · Databases

Hidden MySQL Tricks You Probably Missed

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.

Database TipsMySQLReplication
0 likes · 7 min read
Hidden MySQL Tricks You Probably Missed
ITPUB
ITPUB
Jul 18, 2016 · Databases

How Reference Partition Boosts Oracle Query Performance and Simplifies Management

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.

Database ManagementOracleReference Partition
0 likes · 10 min read
How Reference Partition Boosts Oracle Query Performance and Simplifies Management
Qunar Tech Salon
Qunar Tech Salon
Jul 8, 2016 · Databases

Optimizing MySQL Pagination for Large Datasets

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.

MySQLSQLdatabase optimization
0 likes · 8 min read
Optimizing MySQL Pagination for Large Datasets
Practical DevOps Architecture
Practical DevOps Architecture
Jul 1, 2016 · Databases

SQL Data Import/Export and Table Operations Tutorial

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.

Data ExportMySQLSQL
0 likes · 10 min read
SQL Data Import/Export and Table Operations Tutorial
ITPUB
ITPUB
Jun 27, 2016 · Databases

How to Quickly Recover a Corrupted Oracle 11g Database on Windows

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.

OracleOracle11gSQL
0 likes · 5 min read
How to Quickly Recover a Corrupted Oracle 11g Database on Windows
ITPUB
ITPUB
Jun 23, 2016 · Databases

How to Uncover Oracle Hidden Parameters with Views and Trace

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.

Database InternalsHidden ParametersOracle
0 likes · 5 min read
How to Uncover Oracle Hidden Parameters with Views and Trace
Architect
Architect
Jun 22, 2016 · Databases

Introduction to InnoDB Row Locks, Splitting, Inheritance, and Migration

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.

Database InternalsInnoDBLock Splitting
0 likes · 8 min read
Introduction to InnoDB Row Locks, Splitting, Inheritance, and Migration
ITPUB
ITPUB
Jun 19, 2016 · Databases

Why Oracle User Can Create Tables in Unauthorized Tablespaces

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.

Database_SecurityDeferred_Segment_CreationOracle
0 likes · 8 min read
Why Oracle User Can Create Tables in Unauthorized Tablespaces
ITPUB
ITPUB
Jun 17, 2016 · Databases

Why Do Oracle Buffer Busy Waits Occur and How to Resolve Them?

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.

OracleSQLbuffer busy waits
0 likes · 10 min read
Why Do Oracle Buffer Busy Waits Occur and How to Resolve Them?
21CTO
21CTO
Jun 14, 2016 · Databases

Why Your SSRS Report Is Slowing Down: Real-World SQL Optimization Lessons

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.

Database IndexingPartitioningSQL
0 likes · 10 min read
Why Your SSRS Report Is Slowing Down: Real-World SQL Optimization Lessons
Architecture Digest
Architecture Digest
Jun 7, 2016 · Databases

10 Simple Steps to Fully Understand SQL

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.

DeclarativeJoinsSQL
0 likes · 23 min read
10 Simple Steps to Fully Understand SQL
ITPUB
ITPUB
May 30, 2016 · Databases

When to Use UPDATE vs MERGE in Oracle: Performance Tips & Pitfalls

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.

OptimizationOracleSQL
0 likes · 5 min read
When to Use UPDATE vs MERGE in Oracle: Performance Tips & Pitfalls
ITPUB
ITPUB
May 27, 2016 · Databases

How to Log Every Executed Oracle SQL Statement with v$sql and Automated Jobs

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.

Database LoggingJob SchedulingOracle
0 likes · 5 min read
How to Log Every Executed Oracle SQL Statement with v$sql and Automated Jobs
ITPUB
ITPUB
May 20, 2016 · Databases

Step-by-Step Guide: Migrating DB2 v9.7 PHSDB to K-DB11G

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.

DB2K-DBSQL
0 likes · 10 min read
Step-by-Step Guide: Migrating DB2 v9.7 PHSDB to K-DB11G
ITPUB
ITPUB
May 20, 2016 · Databases

Essential Oracle Monitoring Queries for Performance Tuning

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.

Database MonitoringOracleSGA
0 likes · 10 min read
Essential Oracle Monitoring Queries for Performance Tuning
ITPUB
ITPUB
May 4, 2016 · Databases

Master Oracle Triggers: Real‑World Examples and Step‑by‑Step Guide

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.

OraclePL/SQLSQL
0 likes · 12 min read
Master Oracle Triggers: Real‑World Examples and Step‑by‑Step Guide
ITPUB
ITPUB
May 3, 2016 · Databases

Practical Tips for Optimizing Oracle SQL Execution Plans

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.

JoinsOracleSQL
0 likes · 8 min read
Practical Tips for Optimizing Oracle SQL Execution Plans
ITPUB
ITPUB
May 2, 2016 · Databases

How to Combine Multiple MyISAM Tables with MySQL’s MERGE Engine

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.

Database TablesMerge EngineMySQL
0 likes · 5 min read
How to Combine Multiple MyISAM Tables with MySQL’s MERGE Engine
dbaplus Community
dbaplus Community
Apr 28, 2016 · Databases

Unlock Oracle 12c Optimizer: New Stats, Adaptive Features & Advanced Techniques

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.

12cDBMS_STATSOracle
0 likes · 23 min read
Unlock Oracle 12c Optimizer: New Stats, Adaptive Features & Advanced Techniques
ITPUB
ITPUB
Apr 25, 2016 · Databases

Common MySQL Errors and How to Fix Them: A Practical Guide

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.

Database TroubleshootingMySQLMySQL configuration
0 likes · 25 min read
Common MySQL Errors and How to Fix Them: A Practical Guide
ITPUB
ITPUB
Apr 25, 2016 · Databases

How to Move Oracle Tablespaces and Datafiles to a New Location

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.

DatafileOracleSQL
0 likes · 4 min read
How to Move Oracle Tablespaces and Datafiles to a New Location
ITPUB
ITPUB
Apr 24, 2016 · Big Data

12 Essential Hive Performance Tips for Faster Hadoop Queries

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.

HadoopHiveOptimization
0 likes · 7 min read
12 Essential Hive Performance Tips for Faster Hadoop Queries
ITPUB
ITPUB
Apr 22, 2016 · Databases

Boost Oracle Performance: Master Parallel Execution Techniques

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.

OracleSQLparallel execution
0 likes · 10 min read
Boost Oracle Performance: Master Parallel Execution Techniques
ITPUB
ITPUB
Apr 22, 2016 · Databases

Unlock Oracle Error Codes with the oerr Shell Script: A Deep Dive

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.

OracleSQLdatabases
0 likes · 7 min read
Unlock Oracle Error Codes with the oerr Shell Script: A Deep Dive
ITPUB
ITPUB
Apr 21, 2016 · Databases

13 MySQL Tips You Probably Missed: From Case Sensitivity to Binlog

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.

Database TipsMySQLReplication
0 likes · 7 min read
13 MySQL Tips You Probably Missed: From Case Sensitivity to Binlog
ITPUB
ITPUB
Apr 21, 2016 · Databases

Master Oracle Profiles: Limits, Parameters, and Example Creation

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.

OracleProfilesSQL
0 likes · 5 min read
Master Oracle Profiles: Limits, Parameters, and Example Creation