One endpoint. Many databases. Smarter scale.

Oracle Autonomous Database (ADB) now supports ProxySQL-based statement Routing — a powerful new capability that lets you present multiple ADBs (and the data they hold) as if they were one logical system. Instead of hard‑coding dozens of connect strings, copying data everywhere, or wiring up fragile database links, you register your back‑end ADBs with a Router ADB and define routing rules. Applications connect once to the router endpoint; the router sends each statement to the right target.

This post introduces the feature, shows where it shines, and walks through three high‑impact use cases:

  1. Build a Logical Warehouse from Siloed Databases.
  2. “Family Plan” Elastic Pool for Bursty Databases.
  3. Segregated Customer Data with Shared Dimension Data (SaaS multi‑tenant).
  4. Value‑Based Data Tier Routing (High‑Value Local, Low‑Value Routed)

Why statement Routing Matters Now

Modern data platforms rarely live in a single monolithic database. Historic warehouses, finance marts, click/event stores, partner feeds, and regional copies each exist for good reasons — governance, performance, org history. But every new database adds friction:

  • Different connect strings & wallets per environment
  • Security policies that drift
  • ETL or DB links to stitch data for analytics
  • Over‑provisioned compute because “what if the big job runs?”

ProxySQL Routing hides that sprawl behind one stable endpoint while preserving isolation, scale choices, and billing boundaries behind the scenes.

How ProxySQL Routing Works

Note: All databases (Router + Targets) must be in the same Elastic Pool and same region.

At a high level you deploy (or repurpose) an Autonomous Database that acts as a Router. You register other Autonomous Databases as Targets. Then you define mapping rules using the DBMS_PROXY_SQL PL/SQL package — pointing specific schemas or tables to the proper Target ADB. When a client issues a SQL statement against the Router endpoint, ProxySQL evaluates the mappings and forwards the statement to the mapped Target. When no mapping matches, the statement runs locally in the Router ADB.

Key ideas:

  • Single client connect point (JDBC/SQL*Net) into Router.
  • Rule‑based forwarding: schema or object.
  • Local + remote mix: Some objects live in the Router DB; others are transparently routed.
  • Security still applies: Auth terminates at Router; Router authenticates to Targets using stored credentials.
  • No fragile DB links needed.

Note: Routing mappings are currently administrator‑defined (explicit).

Use Case 1 — Build a Logical Warehouse from Siloed Databases

Problem
 Data lives in many places — historic warehouse, finance mart, regional data copy, marketing datawarehouse. Each has its own connect string, auth, and performance profile. Analysts want one SQL surface. Engineering doesn’t want fragile DB links or nightly copy jobs.

Solution
 Put a Router Autonomous Database (ADB) with ProxySQL routing in front. You register each “back‑end” warehouse (one or more ADBs) and map schemas or tables to the right target. Applications connect once — to the router endpoint — and the router “stitches” the silos into a Logical Warehouse.

ProxySQL

Benefits

  • One endpoint for all analytics tools.
  • Reduce (or eliminate) cross‑DB ETL copy jobs.
  • Let each back‑end scale its own compute/storage independently.

Use Case 2 — “Family Plan” Elastic Pool for Bursty Databases

Problem
 You own several databases that see short, heavy spikes (payroll runs, quarter‑close, holiday analytics, audit fetches) but sit mostly idle the rest of the month. Buying and running each system at its peak ECPU wastes money. Managing multiple connect strings and wallets across apps makes it worse. Finance keeps asking why you’re paying for idle compute 90% of the time.

Solution
 Put an always‑on Router ADB in front and enable ProxySQL routing. Place the bursty databases in a shared Elastic Pool. Each member runs at a small baseline size most of the time and scales up only during its spike window. Because Elastic Pools bill on utilized ECPUs (metered usage, rounded) rather than fully provisioned peak, all members effectively share one “family data plan.” Apps still connect to a single endpoint; routing rules (DBMS_PROXY_SQL.ADD_MAPPING) steer large batch jobs to the right backend when it scales up.

Benefits

  • Pay for spikes when they happen, not all month.
  • Central endpoint simplifies app config, wallets, secrets.

Use Case 3 — Segregated Customer Data with Shared Dimension Data (SaaS multi‑tenant).

Problem
 You operate a SaaS platform that must keep each customer’s data isolated — in its own Autonomous Database — for regulatory and “data residency” reasons. Yet all tenants need access to common, read‑mostly reference data: product catalog, currency tables, lookup tables, etc. Shipping copies of these shared tables into every tenant DB causes version drift, patch overhead, and storage waste.

Solution
 Run a Router ADB with ProxySQL routing in front of a pool of per‑customer ADBs (each scaled small and billed on utilized ECPUs). Keep the shared reference datasets centrally in the Router DB. Map tenant‑specific schemas to their own pool member using DBMS_PROXY_SQL.ADD_MAPPING, while joins to the shared catalog resolve locally in the Router DB. Apps connect once; routing enforces tenant isolation while still giving all customers a single dimension data.

ProxySQL

Benefits

  • Strong per‑tenant isolation w/ shared read catalogs.
  • One endpoint simplifies onboarding new customers from application perspective.
  • Update reference data once; all tenants see consistent values.

Use Case 4 Value‑Based Data Tier Routing (High‑Value Local, Low‑Value Routed)

Problem
 Not all data is worth the same performance spend. Operational/financial data drives the business and needs fast response. High‑volume log, trace, and audit exhaust must be queryable but rarely needs top‑tier CPUs.

Solution
 Stand up a performance‑sized Router ADB that holds your high‑value, frequently queried tables (orders, customers, finance metrics). Create one or more low‑CPU Target ADBs in the same Elastic Pool to store low‑value / high‑volume data — logs, debug events, raw clicks.

ProxySQL

Benefits

· Isolation: of high-profile workload from low profile workload.

· Spend where it matters: Keep mission‑critical data on the Router’s higher compute; move bulky, low‑value data to cheaper Targets so you aren’t paying premium resources for infrequent log reads.

· Single endpoint simplicity: Apps, BI tools, and scripts still connect once; routing hides where low‑value data lives, reducing wallet sprawl and config drift.

· Independent lifecycle ops: Log Targets can be patched, resized, or purged on their own schedule without touching critical transactional objects that stay in the Router.

Routing in Action

Routing isn’t magic — it’s metadata plus a handshake. Your router ADB owns a tiny table of rules (owner/object → target OCID). Each target ADB explicitly says “yep, I’ll take traffic for that owner from this router.” After that, every SQL you send to the router is quietly forwarded (or run locally) based on those rules.

Flip the switch (once)

BEGIN
DBMS_PROXY_SQL.ENABLE_ROUTING;
END;
/

That marks this ADB as the router and turns on automatic statement routing.

Tell the router “what goes where”

Schema-wide:

BEGIN
  DBMS_PROXY_SQL.ADD_MAPPING(
  object_owner => 'DW_USER',
  object_name => '*', - asterisk means whole schema
  database_ocid => 'TARGET1_DATABASE_OCID');
  END;
/

Or single object:

BEGIN
DBMS_PROXY_SQL.ADD_MAPPING(
object_owner => 'DW_USER',
object_name => 'INVENTORY',
database_ocid => 'TARGET2_DATABASE_OCID');
END;
/

Note: database_ocid must be uppercase. Get it on a target with:

SELECT JSON_VALUE(cloud_identity,'$.DATABASE_OCID') AS db_ocid
FROM v$pdbs;

Let the targets accept

On each target that should receive DW_USER traffic:

BEGIN
  DBMS_PROXY_SQL.ACCEPT_MAPPING(
  object_owner => 'DW_USER',
  router_database_ocid => 'ROUTER_DATABASE_OCID');
  END;
/

One accept per owner covers any number of mapped objects for that owner.

What happens at runtime

Your app connects only to the router endpoint. ProxySQL checks the SQL text, finds the first matching mapping, and forwards the statement to that target using the same service level (HIGH/MEDIUM/etc.). No match? It runs locally on the router. Wallet sprawl and DB links stay out of the picture

Putting It All Together

ProxySQL Routing lets you scale out logically without forcing all data into one giant database. Whether you’re unifying silos, smoothing cost spikes across an Elastic Pool, or meeting strict per‑tenant isolation rules while sharing common data, the Router + Rules pattern keeps apps simple and governance intact.

Try it with one domain first. Measure. Then extend. When your next data domain shows up tomorrow (it always does), just add a mapping — no app re‑wiring.

Next Steps & Resources