OCP 23ai New Features — 15 Questions

Question 1

You are migrating a legacy application to use Wide Tables in 23ai. What is the maximum number of columns supported by a Wide Table?

Accepted Answer

4096. Oracle 23ai Wide Tables support up to 4096 columns, a significant increase over the traditional 1000-column limit for standard tables. This allows schemas with many sparse attributes (common in IoT, telemetry, and JSON-like data) to be stored natively without resorting to EAV models or JSON columns. The 4096 limit is the documented maximum for Wide Tables in 23ai.

Answer

1000

Answer

8192

Answer

2048

Question 2

To enable lock-free reservations on a table, which clause must be used in the CREATE or ALTER TABLE statement?

Accepted Answer

LOCK-FREE RESERVATIONS. Oracle's lock-free reservations feature is enabled by adding the LOCK-FREE RESERVATIONS clause to a CREATE TABLE or ALTER TABLE statement. This clause activates the reservation mechanism that lets sessions reserve rows without holding row-level locks, improving concurrency for high-contention workloads. The exact syntax is required — no other keyword combination activates the feature.

Answer

ENABLE LOCK-FREE

Answer

RESERVATIONS ON

Answer

CONCURRENT DML

Question 3

When configuring Parallel DML in 23ai, which parameter must be set to 'ALWAYS' to ensure parallel execution for DML statements without hints?

Accepted Answer

ENABLE_PARALLEL_DML. Setting ENABLE_PARALLEL_DML to ALWAYS ensures that all DML operations are executed in parallel if possible.

Answer

PARALLEL_DEGREE_LIMIT

Answer

DML_PARALLEL_MODE

Answer

PARALLEL_EXECUTION_ENABLED

Question 4

Which component allows True Cache to provide high performance for read-only workloads by caching data on the application tier?

Accepted Answer

True Cache. True Cache is a read-only cache that resides in the application tier to offload the primary database.

Answer

Result Cache

Answer

Data Guard

Answer

Oracle Connection Manager

Question 5

Which SQL function is used to calculate the similarity between two vectors?

Accepted Answer

VECTOR_DISTANCE(). VECTOR_DISTANCE is the standard operator for similarity search in 23ai.

Answer

COSINE_SIM()

Answer

DIST()

Answer

SIMILARITY()

Question 6

You are configuring Parallel DML and want to ensure the session uses the maximum available resources. Which hint should you use?

Accepted Answer

/*+ PARALLEL(table, n) */. The PARALLEL hint allows you to specify the degree of parallelism for a statement.

Answer

/*+ MAX_PARALLEL */

Answer

/*+ SERIAL */

Answer

/*+ DML_PARALLEL */

Question 7

Which parameter controls the behavior of the Memoptimized Write buffer size?

Accepted Answer

MEMOPTIMIZE_POOL_SIZE. The MEMOPTIMIZE_POOL_SIZE parameter manages the memory allocated for the fast ingest buffer.

Answer

DB_BUFFER_CACHE

Answer

LARGE_POOL_SIZE

Answer

FAST_INGEST_BUFFER

Question 8

When creating a Wide Table, what is the physical storage impact compared to a standard table?

Accepted Answer

It is more space-efficient for sparse data. Wide Tables are more space-efficient for sparse data because they use a compressed storage format that efficiently handles nulls and repeating values, reducing the storage footprint compared to a standard table with many columns. This makes them ideal for scenarios where many attributes are optional or sparsely populated.

Answer

It disables compression

Answer

Requires separate tablespaces

Answer

It uses more storage due to padding

Question 9

Which THREE actions can be performed on an HNSW vector index in 23ai?

Accepted Answer

ALTER INDEX ... REBUILD. In Oracle Database 23ai, an HNSW (Hierarchical Navigable Small World) vector index is created with the CREATE INDEX statement using the VECTOR indexing clause, so option C (CREATE INDEX) is correct. The index can be rebuilt to refresh its structure after data changes or parameter adjustments using ALTER INDEX ... REBUILD, making option B correct. HNSW indexes are also usable by the VECTOR_DISTANCE function for similarity searches, so option E is correct. Option A (DROP INDEX) is not among the marked correct answers here, and option D (PARTITION) is not a supported action for HNSW vector indexes in 23ai, as partitioning applies to other index types rather than HNSW vector indexes.

Answer

DROP INDEX

Answer

PARTITION

Question 10

When configuring Memoptimized Write, what is the role of the 'flush' operation?

Accepted Answer

It moves data to the data files. In Memoptimized Write, the flush operation moves buffered data from the in-memory write buffer to the persistent data files on disk. It ensures durability by writing the buffered rows to the database's datafiles, making them permanent and recoverable.

Answer

It validates data constraints

Answer

It clears the cache

Answer

It deletes the data

Question 11

Which TWO parameters are essential for configuring the environment for Wide Tables?

Accepted Answer

DB_BLOCK_SIZE. Wide tables rely on specific database settings to handle the large number of columns.

Answer

SQL_TRACE

Answer

PARALLEL_MAX_SERVERS

Answer

MAX_COLUMNS

Question 12

When configuring Lock-Free Reservations, what must be done to the column being updated?

Accepted Answer

It must be numeric. The column must be a numeric type enabled for reservations.

Answer

It must be a primary key

Answer

It must be in a partitioned table

Answer

It must be indexed

Question 13

In the context of Wide Tables, what is a recommended best practice?

Accepted Answer

Maintain logical data grouping. Maintaining logical data grouping is a recommended best practice for Wide Tables because it improves query performance and manageability by clustering related columns together, which can reduce I/O and improve compression. Wide Tables in Oracle are designed to store many columns, but grouping related data logically helps the database optimize storage and access paths.

Answer

Only use for read-only data

Answer

Disable all indexes

Answer

Use as many columns as possible

Question 14

When using parallel DML, which system parameter limits the degree of parallelism?

Accepted Answer

PARALLEL_DEGREE_LIMIT. PARALLEL_DEGREE_LIMIT restricts the degree of parallelism.

Answer

MAX_PARALLEL_THREADS

Answer

DB_PARALLEL_LIMIT

Answer

SESSION_PARALLEL_MAX

Question 15

Which of these is a requirement for using True Cache?

Accepted Answer

Support from the connection pool. True Cache requires a connection pool that supports True Cache features.

Answer

An exclusive server

Answer

Special SQL syntax

Answer

A manual cache update script