Cutting a Slow Oracle Search Query from Cost 13902 to Under 100

A search page with twelve optional input fields generated a very slow Oracle query; in the worst case the generated SQL performed full‑table scans on large tables and had an execution‑plan cost of 13902.

1. Identify unnecessary table joins

By highlighting table aliases in Notepad++, the author discovered that the tj table was never used in WHERE or SELECT clauses, so its LEFT JOIN could be removed safely.

The pc and pw tables were one‑to‑one with ai and only contributed when corresponding filter parameters were present. Using MyBatis <if> conditions, these joins can be added only when needed.

<if test="mobilePhone != null and mobilePhone != ''.toString() or familyTel != null and familyTel != ''.toString()">
    join t_pc PC on AI.SERIALNO = PC.OBJECTNO
</if>
<if test="mobilePhone != null and mobilePhone != ''.toString()">
    and (PC.MOBILETELEPHONE = #{mobilePhone , jdbcType=VARCHAR} or PC.MOBILETELEPHONE2 = #{mobilePhone , jdbcType=VARCHAR})
</if>
<if test="familyTel != null and familyTel != ''.toString()">
    and PC.FAMILYTEL = #{familyTel , jdbcType=VARCHAR}
</if>

2. Simplify the SELECT clause

The inner SELECT invoked six custom functions, each executing a tiny query that produced about 10 000 rows. Because the outer query only needed the final 10 rows, moving these function calls after pagination eliminated ~60 000 unnecessary tiny queries.

3. Business‑driven column reduction

Fields from the bi table were not required for the search result; after discussion with stakeholders they were removed, allowing the ci and bi joins to be dropped and reducing the cost to 10130.

4. Leverage data characteristics

4.1 Simplify joins based on data distribution – the remaining major cost came from the ai – fd one‑to‑many join. Since every ai record has at least one fd row, the join can be deferred until after pagination, cutting the cost to about 2094 when phaseNo is absent.

4.2 Exploit skewed value distribution – the applystatus column has a few rare values (e.g., “申请中”) that are indexed, while the dominant values (“通过”, “否决”) cause full scans. Making the rare status a default dropdown improves selectivity.

5. Create composite indexes

Adding a composite index on (applystatus, inputDate) replaces the single‑column index on applystatus and brings the cost down to roughly 1082.

6. Use index‑filtering to avoid row‑lookups

Creating a new composite index (objectno, phaseno) on the fd table allows the fd.phaseno = #{phaseNo} predicate to be evaluated directly from the index, reducing the cost to 362. The previous index (objectno, objectType) forced a table lookup for every row, which was far less efficient.

7. Achieve optimal performance

Combine the above techniques: when phaseNo is not supplied, skip the fd join in the main query and attach it after pagination (cost < 100). When phaseNo is supplied, rely on the new composite index to filter rows (cost ≈ 362).

Conclusion

The optimization checklist includes removing unnecessary joins, trimming the SELECT clause, leveraging indexes (including composite ones), understanding data distribution, and conditionally applying joins based on user input. Applying all steps reduced the worst‑case cost from 13902 to under 100, ensuring acceptable performance for all query scenarios.

Source: CoderBee (https://coderbee.net/index.php/db/20170921/1570)