23ai New Features practice questions

1000

Why it fails: This was the limit in older versions.

8192

Why it fails: This exceeds the current architecture limit.

2048

Why it fails: This is not the maximum limit.

ENABLE LOCK-FREE

Why it fails: Oracle provides no ENABLE LOCK-FREE clause; the syntax does not exist, so the statement fails to parse. It is tempting because the wording mirrors the feature's marketing name, and would be correct only if Oracle had implemented such a keyword, which it has not.

RESERVATIONS ON

Why it fails: RESERVATIONS ON is not Oracle table syntax; the clause does not exist, so the DDL raises an error. It is tempting because it names the feature directly, and would be correct if Oracle exposed reservations through a dedicated keyword rather than the actual clause used to enable them.

CONCURRENT DML

Why it fails: CONCURRENT DML is not valid table DDL syntax in Oracle; it appears in no CREATE or ALTER TABLE grammar. It is tempting because concurrent DML describes the workload lock-free reservations target, and would be correct if the feature were enabled through a DML-related clause rather than the actual one.

PARALLEL_DEGREE_LIMIT

Why it fails: This controls the degree of parallelism.

DML_PARALLEL_MODE

Why it fails: This is not a valid parameter.

PARALLEL_EXECUTION_ENABLED

Why it fails: This is not a valid parameter.

Result Cache

Why it fails: Result cache is inside the database instance.

Data Guard

Why it fails: Data Guard is for high availability and DR.

Oracle Connection Manager

Why it fails: This is for routing, not caching.

COSINE_SIM()

Why it fails: Not the correct function name.

DIST()

Why it fails: Too generic.

SIMILARITY()

Why it fails: Not the correct function name.

/*+ MAX_PARALLEL */

Why it fails: Not a valid hint.

/*+ SERIAL */

Why it fails: This forces serial execution.

/*+ DML_PARALLEL */

Why it fails: Not a valid hint.

DB_BUFFER_CACHE

Why it fails: This is for standard caching.

LARGE_POOL_SIZE

Why it fails: This is for general memory.

FAST_INGEST_BUFFER

Why it fails: Not a parameter.

It disables compression

Why it fails: Compression is supported.

Requires separate tablespaces

Why it fails: No such requirement.

It uses more storage due to padding

Why it fails: Oracle handles wide tables efficiently.

DROP INDEX

Why it fails: Technically yes, but the question asks for actions on the index structure capabilities.

PARTITION

Why it fails: Not directly supported on HNSW.

It validates data constraints

Why it fails: The flush operation forces buffered Memoptimized Write data from the memoptimize pool to persistent storage; it does not validate constraints, which Oracle enforces during DML regardless. It is tempting because flushing sounds like a consistency check, and it would be correct if the requirement were constraint enforcement rather than durability of buffered writes.

It clears the cache

Why it fails: Memoptimized Write's flush operation does not clear the cache; it forces buffered writes from the row store to persistent storage, ensuring durability at commit boundaries. Clearing cache describes invalidation or eviction, which is a different mechanism entirely and would not satisfy the write-durability requirement this operation exists to provide.

It deletes the data

Why it fails: Flush writes buffered rows to disk for durability; it never removes data, which is the role of delete or truncate. It is tempting because flushing a buffer can sound like clearing it, and it would be correct if the goal were purging rows, not persisting Memoptimized Write changes.

SQL_TRACE

Why it fails: For debugging.

PARALLEL_MAX_SERVERS

Why it fails: For parallelism.

MAX_COLUMNS

Why it fails: Not a parameter.

It must be a primary key

Why it fails: Not a requirement.

It must be in a partitioned table

Why it fails: Not a requirement.

It must be indexed

Why it fails: Not a requirement.

Only use for read-only data

Why it fails: DML is supported.

Disable all indexes

Why it fails: Indexes are needed for performance.

Use as many columns as possible

Why it fails: Always follow good design.

MAX_PARALLEL_THREADS

Why it fails: Not a parameter.

DB_PARALLEL_LIMIT

Why it fails: Not a parameter.

SESSION_PARALLEL_MAX

Why it fails: Not a parameter.

An exclusive server

Why it fails: Can share hardware.

Special SQL syntax

Why it fails: It is transparent.

A manual cache update script

Why it fails: Automatic.

No code changes required

Why it fails: Requires code updates.

Requires a new driver

Why it fails: Standard drivers work.

Complete rewrite of SQL

Why it fails: Just the vector-related parts.

Tables with large rows

Why it fails: It's about column counts.

Tables with long text

Why it fails: CLOBs are different.

Tables across multiple disks

Why it fails: That is partitioning.

Automatic translation

Why it fails: Duality Views do not translate between JSON and relational at runtime; they project existing relational data into JSON documents through defined mappings, with updates applied to the base tables. Automatic translation describes an ORM or API layer. Duality Views would be chosen to serve both document and relational consumers from one schema.

Lower licensing costs

Why it fails: Duality Views are a database feature included with the Oracle Database licence; they do not alter licensing metrics or reduce costs. Licensing depends on edition, processor counts and named-user plus options, not on how JSON is exposed. Lower cost would be the goal of consolidation or edition changes, not Duality Views.

Faster storage

Why it fails: JSON Duality Views expose the same underlying relational data as both JSON documents and relational tables, so no duplicate storage is created. Storage speed is unchanged. Faster storage is a hardware or tablespace concern, addressed by tuning disks or in-memory options, not by a data-modelling feature.

Use a database trigger to check for duplicates before insert

Why it fails: A trigger could be written to check for duplicates, but it is a procedural workaround, not a declarative constraint. It would require custom code and may have performance implications. The question asks for the approach to enforce uniqueness, and the native JSON schema validation is more appropriate and efficient.

Create a unique index on the JSON field using a JSON search index

Why it fails: A JSON search index is designed for full-text search and ad-hoc queries, not for enforcing uniqueness. It cannot enforce a unique constraint on a JSON field. While it can index JSON data, it does not provide the uniqueness enforcement required for the employeeId field.

Create a materialized view that computes uniqueness

Why it fails: A materialized view can be used to precompute and enforce constraints in some cases, but it is not designed for enforcing uniqueness on JSON documents. It would be an indirect and complex solution. The JSON schema validation is the intended feature for such requirements.

Vector indexes in 23ai support only exact nearest neighbor searches, not approximate searches, to guarantee accuracy.

Why it fails: Oracle Database 23ai vector indexes are designed for approximate nearest neighbor (ANN) searches, which trade a small amount of accuracy for significant performance gains. Exact searches can be performed without an index using full table scans, but the index itself is approximate.

The VECTOR data type stores vectors only in binary format, and you must use the TO_VECTOR function to convert numeric arrays before insertion.

Why it fails: The VECTOR data type can store vectors in several formats, including FLOAT32, FLOAT64, INT8, and BINARY. You are not limited to binary format, and while TO_VECTOR can be used for conversion, it is not mandatory for all insertions; you can also provide a string representation or bind variables.

A VECTOR column can be indexed only if it is declared with the FLEXIBLE keyword, which allows variable dimensions.

Why it fails: Vector columns can be indexed whether they have a fixed dimension or are flexible, but flexible columns (without a specified dimension) have restrictions on index creation. In fact, you typically specify the dimension to create an index; the FLEXIBLE keyword is not a requirement for indexing.