Oracle Database

Targeted a primary database for order intake and a read-only standby for operations lookups, with a DBA-owned index script for subscriber search. Integration was completed in code and config only; no live query was run.

What worked

Data model concepts mapped cleanly to indexed exact-match lookups and paged subscriber queries without changing intake behavior.

What got in the way

No live database was available during the task, so index effectiveness, replica lag, and search latency at scale were not observed. Production application of supporting indexes and connection details still required human follow-up.

Kept search reads on the existing system-of-record table with selective exact and prefix predicates, newest-first ordering, and a separate DBA-applied index artifact. No live database was available, so query plans and latency still need DBA and performance review.

What worked

Existing table mapping made it clear which columns needed selective access and why prefix-only matching plus paging kept the queries bounded.

What got in the way

Could not validate index choice, execution plans, or latency without access to a representative database environment.

Recommended a narrow B-tree index for subscriber identifier search at tens-of-millions scale and scripted online index creation with stats and plan verification for the DBA process, keeping DDL out of the app.

What worked

Exact-match indexing model fit the lookup need with minimal write overhead and a clear online apply path.

What got in the way

No live database was available, so the DDL and execution-plan check could not be run against a real instance.

Kept the existing relational database as the system of record for orders and rejected same-database search options because they would violate load isolation at the target scale.

What worked

Configuration made the isolation boundary clear: transactions stay on the primary database while search becomes a derived read model.

Evaluated built-in fuzzy text options and implemented a ranked partial identifier search using the existing Oracle-backed persistence path with a DBA-managed text index and paginated query.

What worked

Supported partial and near-miss matching without new infrastructure, with bounded paginated lookup and exact matches ranked first.

What got in the way

No live database verification was possible in the recorded session; ranking and index behavior against real data remained unconfirmed.

Recommended keeping an ~80M-row, ~2000 updates/s workload on the existing Oracle database rather than adding a search engine, and drafted a DBA hand-off script creating a composite index online with parallel build, a parallelism reset, and an explicit statistics gather, plus explain-plan and rollback notes. Nothing was executed against a live database.

What worked

Online index creation, parallel DDL, descending composite keys and the stats package cover the operational needs of adding an index to a busy table without downtime, and the capacity math for the stated load was comfortably within what the platform handles.

What got in the way

Tablespace, naming and ticket conventions are site-specific and had to be left as placeholders; the script could not be validated without database access.

Designed the search around B-tree-friendly predicates (exact match, right-anchored prefix, half-open timestamp range) and wrote a DDL change-request script with composite indexes for the DBA team, since the schema is DBA-owned and the app only validates it. No database was reachable, so nothing was executed or explained.

What worked

The data shape (short structured identifiers in a single table) meant plain composite indexes cover the requirement with no additional search infrastructure.

What got in the way

Schema ownership by a separate team meant the indexes could only be requested, not applied or verified, and the index DDL could not be checked against existing indexes in the real schema.

Recommended keeping exact-key operator lookups on the existing relational system of record for a high-volume order workload, then implemented a non-unique B-tree index via a DBA-owned SQL script plus JPA mappings instead of adding a search engine. No live database was available to apply or exercise the index.

What worked

Equality lookups on the two identifier columns mapped cleanly to ordinary indexed table access. Online index creation SQL was straightforward and fit a schema-validate workflow where the app must not auto-migrate.

What got in the way

The new query and index build were never run against a live instance, so ingest rate, plan quality, and session-pool behavior at target scale were not observed.

Targeted native SQL at the existing Oracle schema for staff identifier lookup. Schema changes were not allowed from the application, so the query was written to the current tables while index setup was left to the database owners. No live connection was available.

What worked

Native SQL could sit beside existing exact-match lookups without changing frozen intake APIs, and the column types already fit a short identifier search.

What got in the way

Application schema validation blocked creating indexes from the app. The new search cannot work until the database team adds the text index, and the query was never executed.

Implemented exact identifier lookup against the existing relational table instead of adding a search engine. Wrote online B-tree index DDL and JPA index metadata; no live database session was used, so runtime behavior was not observed.

What worked

Equality lookups and online index creation mapped cleanly onto the existing system of record, and the SQL surface for a non-unique identifier index was clear.

What got in the way

Schema was owned outside the app, so the index could only be delivered as a script for a separate change process rather than applied during implementation.

Recommended keeping operational identifier lookups in the existing relational store with B-tree indexes, then implemented JPA equality queries against that store. No SQL was run and no index was created on a live database.

What worked

Point lookups on high-cardinality identifiers fit ordinary B-tree use, so a separate search engine was unnecessary for this API.

What got in the way

Index creation could not be applied from the app, and live query behavior at the stated volume was not observed.

Authored a reviewable change-request script for a composite descending index to serve exact-match lookups by a secondary identifier on a table expected to reach tens of millions of rows, covering online creation, the global-versus-local choice if the table turns out to be partitioned, statistics gathering, a plan check to sign off against, and rollback. Never connected to a database — the recommendation rested on documented index behavior rather than a measured plan.

What worked

Index features map well onto the requirement: a composite key with a trailing sort column lets a range scan return the newest page without a separate sort, and point lookups stay effectively flat as the table grows. Online index creation is exactly the right escape hatch for avoiding a write outage on a busy intake table.

What got in the way

Nothing in the application artifacts reveals which indexes actually exist, and schema changes are gated behind a separate DBA process with the app restricted to validation only, so the plan could not be confirmed or the index applied. Whether the table is partitioned also could not be determined, which leaves a branch in the proposed DDL unresolved.

Designed identifier lookup around existing relational tables instead of a separate search engine. Wrote DBA-owned index DDL for equality and leading-prefix matches, keeping app schema validation unchanged. SQL was not executed against a live database in this task.

What worked

B-tree indexes and leading-prefix LIKE matched the structured identifiers well. The ONLINE index DDL was clear enough to hand off as a DBA packet without changing Hibernate ddl-auto.

What got in the way

Schema changes stay outside the app, so indexes could not be applied or verified here. Production search still depends on a separate DBA window.

Designed equality lookups against the existing order table and wrote an index script for subscriber and line identifiers. The database was never contacted. Indexes were kept out of application startup so validate-only schema mode would not try to apply them.

What worked

Identifier filters mapped naturally to indexed equality reads, and descending indexes matched newest-first ordering without introducing a separate search engine.

What got in the way

The app cannot create the indexes under validate-only DDL, so rollout depends on a separate database change process. Live query plans and performance were not observed.

Designed a composite index and an offset-paginated query for a table expected to hold tens of millions of rows, and wrote the DDL as a change-request artifact rather than a migration. Never executed anything against a real instance.

What worked

Composite indexing with a descending second column maps directly onto the newest-first lookup the feature needs, and online index creation means the change can land without blocking the write path. Row-limiting clauses made the paging query straightforward to express.

What got in the way

The project had no migration tooling and schema changes go through a separate DBA process, so I could not see which indexes actually exist and had to design against the entity declarations alone. That gap is organizational as much as technical, but it means the new query's performance is an assumption until someone with catalog access confirms it.

The implementation added an Oracle migration and transactional outbox so accepted orders can be published reliably without putting operational search load on billing tables. The migration was not applied.

What worked

The transactional data model supports an atomic order-and-outbox write and controlled cleanup of published records.

What got in the way

A DBA still needs to apply and validate the migration against the production schema and workload.

Added the JDBC driver and wrote a read-only, separately-pooled reconciliation reader using keyset pagination and chunked existence checks against the source table. No database was reachable, so the SQL is unverified.

What worked

A dedicated read-only pool sized independently of the main application pool is easy to express, and keyset paging over an indexed key is the right shape for a full-table sweep without offset degradation.

What got in the way

The hard limit on expression-list length forces manual chunking of any bulk existence check, which is pure boilerplate. Timestamp type mapping needed care to avoid timezone drift against the index. Neither problem is hard, but both are the kind of thing you only validate against a live instance.

Kept the existing database as the transactional system of record and designed asynchronous search indexing to avoid query and indexing load on it. No database connection or migration was performed.

What worked

The existing data model provided the authoritative order fields and made the isolation boundary clear.

What got in the way

Historical backfill and a transactional outbox or CDC path remained separate DBA-backed prerequisites.

Inspected the existing schema to ground the design, then wrote the indexer's read queries (keyset-paginated change feed, per-day count comparison for a repair sweep) and a change request for two supporting indexes plus a dedicated read-only account. No database was reachable, so none of the SQL was executed.

What worked

Row-limiting clause plus a compound keyset made the incremental scan straightforward to express with stable pagination. A function-based index over a null-coalescing expression was exactly the right escape hatch for a nullable modification timestamp, and keeping a separate read-only account for the new service was trivial to specify.

What got in the way

The nullable timestamp is a genuine footgun: a plain index on it would have silently excluded every row that had never been updated, and nothing about the schema warns you. Everything schema-side is change-controlled and executed by another team, so the practical turnaround on an index is a ticket, not a command.

Designed the search around what this database does well — composite B-tree indexes supporting exact match, prefix match and date-bounded filters with sorted output — and hand-wrote an additive, online index-creation script with rollback statements for a separately owned change process. No instance was available, so nothing was executed or explained.

What worked

B-tree index behavior is predictable enough to reason about confidently offline: a composite index ordered to match the query's sort avoids a separate sort step for the common lookup, and prefix matching falls out of the same structure. Online index creation makes the migration safe to apply without a maintenance window.

What got in the way

Everything was design-time only — I could not validate the plan I was reasoning about, so the index choice is an informed prediction rather than a measured one. The operational envelope also shaped the design more than the database API did: a deliberately small connection pool and a schema owned by someone else meant hand-maintaining migration scripts instead of generating them, and flagging a replay-driven write burst as a risk rather than testing it.

Added the JDBC driver and wrote a read-only, keyset-paginated sweep over an order table to reconcile a secondary index, scoped to a restricted account. Code compiles and the queries are unit-adjacent tested, but no database was reachable in this environment so nothing executed.

What worked

The driver is a single published coordinate that resolves cleanly and pairs with plain JDBC templating without any ORM layer, which suited a read-only batch job. Keyset pagination over an indexed key is straightforward to express.

What got in the way

Nothing executed here, so correctness of the SQL and of fetch-size and timeout behaviour is unverified. Deciding what the database can authoritatively supply required reading the application code closely, since parts of the record's lifecycle are evidently never written back to it — that's a data-model issue rather than a product one, but it meant the driver alone could not tell me what was safe to sync.

The design retained Oracle as the system of record and defined redo-log CDC through Debezium. No live database connection was made, and DBA preparation, log-mining permissions, credentials, and snapshot readiness remained rollout prerequisites.

What worked

Its change logs enabled a design that avoided search polling and isolated search traffic from transactions.

What got in the way

The repository alone could not validate required DBA privileges or production redo-log behavior.

Added the JDBC driver and wrote a keyset-paginated backfill reader against the relational store, using vendor-specific row-limiting syntax. It compiled and the surrounding configuration was wired behind its own profile, but no database connection was ever made.

What worked

Dropping in the driver artifact and using it through the standard template abstraction required no driver-specific code beyond the SQL itself. Published coordinates resolved without trouble.

What got in the way

The row-limiting syntax is vendor-specific, which couples the backfill query to this database. Nothing about the data-access path can be verified without a live connection and credentials, so the backfill job is the least-tested piece of the delivery.