Unlock SQL Speed: Real-World Optimization Tricks and Techniques

The author shares hands‑on Oracle SQL optimization techniques drawn from real‑world cases, aiming to help readers experience the joy of faster queries.

1. Key Terminology

Understanding terms such as execution plan, index, and statistics is essential, much like learning the basic moves in a martial art before mastering advanced techniques.

1.1 Execution Plan

An execution plan describes the path and steps the database takes to run a SQL statement. It is cached in the shared pool and regenerated only when tables change, statistics become stale, the pool is flushed, or the database restarts.

1.2 Index

An index is a separate physical structure that maps column values to data pages, similar to a book's table of contents.

1.3 Statistics

Statistics describe table and index size, row count, data distribution, etc. The Oracle cost‑based optimizer uses these numbers to estimate the cost of different access paths and choose the cheapest plan. If statistics are missing, dynamic sampling is performed.

2. Can a SQL Statement Be Optimized?

DBAs usually start by checking current performance, execution plan, wait events, table size, index presence, selectivity, and statistics. Often, a poorly performing SQL that runs for minutes or hours can be sped up significantly if the business truly needs to scan only a small data set.

3. Case Studies (The "Swords")

3.1 First Sword – Effective Index Use

Table INS_USER_571 has two indexes: IDX_INS_USER1_571 on BILL_ID and IDX_INS_USER2_571 on EXPIRE_DATE. The query below benefits from the highly selective BILL_ID index:

select * from SO1.INS_USER_571 M where M.BILL_ID = '13905710000' and M.EXPIRE_DATE > sysdate;

Because BILL_ID returns only one row, using IDX_INS_USER1_571 is much faster than scanning by EXPIRE_DATE.

3.2 Index Can Be Slower

Creating an index on CREATE_DATE did not improve performance because the optimizer chose a full table scan. Adding a hint forces a full scan to prove the optimizer’s choice:

SELECT /*+full(M)*/ COUNT(*) FROM SO1.INS_USER_571 M WHERE M.CREATE_DATE >= TO_DATE('20110101','YYYYMMDD') AND M.CUST_TYPE='1';

SELECT /*+INDEX(M,IND_INS_USRE4_571)*/ COUNT(*) FROM SO1.INS_USER_571 M WHERE M.CREATE_DATE >= TO_DATE('20110101','YYYYMMDD') AND M.CUST_TYPE='1';

When the data volume to be scanned is large, an index can be more costly than a full scan.

3.3 Common Pitfalls (Checklist)

Functions on indexed columns prevent index usage (e.g., to_char(create_date,'YYYY-MM-DD HH24:MI:SS')).

Implicit datatype conversion can block index use (e.g., comparing a VARCHAR2 column to a number).

Composite indexes must have the most selective column first.

Even small tables benefit from indexing join columns.

Avoid creating too many indexes; they hurt DML performance.

3.4 Reducing Data Accumulation

A reporting table processed 10,000 threads per city caused CPU pressure. Creating a global index on a highly selective column reduced execution time from 1.4 s to 0.002 s and allowed the thread count to be lowered.

3.5 Second Sword – Logical Refactoring

Replacing IN/EXISTS subqueries with proper joins and adding indexes on join columns reduced a 2‑hour job to under 10 seconds.

3.6 Third Sword – Partition Pruning

By adding the DONE_DATE filter to a query on a partitioned table, the optimizer could prune partitions, cutting the timeout from 50 s to 3 min and eventually to a few seconds after further tuning.

3.7 Fourth Sword – Efficient UPDATE

Instead of looping updates, create temporary tables with unique indexes and perform a set‑based update. This reduced a day‑long update to a 10‑minute job and eliminated CPU spikes.

create table jf.tmp_tab1 parallel 10 nologging as select distinct user_number from jf.dr_ggprs_jx_20150203 where charging_characteristics='04';

create unique index jf.ind_user_number_1 on jf.tmp_tab1(user_number) nologging;

update (select a.status from bossmdy.user_fenleijx_temp_20150203 a, jf.tmp_tab1 b where a.user_number = b.user_number and a.status = '3') set status = '0';

4. Hints, Parallelism, and SQL Profiles

Using optimizer hints such as /*+parallel(a,6)*/, /*+APPEND NOLOGGING*/, and index hints can steer the execution plan in high‑load scenarios. SQL Profiles can capture a good plan and bind it to a statement, preventing costly dynamic sampling.

SELECT /*+parallel(a,4) dynamic_sampling(a,0)*/ ...

5. Summary

SQL optimization is a blend of understanding execution plans, statistics, indexing, partitioning, and business logic. There is no universal formula; each case requires careful analysis of system behavior, data distribution, and workload characteristics. With diligent investigation and the right techniques, most performance problems can be resolved.

Source: Article originally published by DBA+ community (author: Lu Jinwen, senior DBA at Xinju Network).