Oracle Address Search Optimization (ES Alternative)

Home → Engineering & Data Projects → Oracle Address Search Optimization

This case describes an address search optimization project for broadband service. Instead of adding an external Elasticsearch layer, the team stayed on Oracle and achieved sub-second search by redesigning tables and indexes, rewriting SQL, and feeding better statistics to the optimizer.

Tech Stack: Oracle SQL / PL/SQL Function Indexes Composite Indexes Query Tuning

Focus: Address Search ES Alternative Performance Tuning Cost Reduction

Dashboard

Data refresh: On-demand (performance tests)

What you’re seeing: response time distribution, index hit ratios, and query plans before and after optimization. The dashboard makes it easy to see how table/index redesign and SQL rewrite translate into real latency gains and server savings.

How to read & interact

Compare before vs. after: review response times and execution plans to see exactly how tuning changed the workload.
Look at index usage: check which composite / function indexes are actually used, and where full table scans still appear.
Link to cost: translate performance improvements into server savings, simpler architecture, and lower operational risk.

Notes & methods

Source: broadband address tables, installation records, and reference dictionaries stored in Oracle.
Key actions: redesigned table structures and composite / function indexes, rewrote SQL to improve join and filter order, and simplified plans to minimize random I/O.
Optimizer support: refreshed statistics, added histograms where needed, and used stable hints only when the natural plan was unreliable.
Outcome: production search dropped to about 0.8 seconds — slightly better than the previous ES-based approach — with fewer servers and much lower switch-over complexity (commercial value ~¥800,000).

Data refreshed: 2025-11-23 · Page updated: 2025-11-23

YYC-Wander