Exploring Bigfile Tablespaces with Default Settings in oracle 23ai

BIGFILE Tablespace

A bigfile tablespace is a tablespace with a single, but large datafile. Traditional small file tablespaces, in contrast, typically contain multiple datafiles, but the files cannot be as large. Making SYSUAX, SYSTEM, and USER tablespaces bigfile by default will benefit large databases by reducing the number of datafiles, thereby simplifying datafile, tablespace, and overall global database management for users.

Oracle 23ai: AI-Driven SELECT – Vector Search

Introduction

For decades, databases have excelled at storing and retrieving structured data, but they struggled when it came to understanding similarity, context, or meaning. Traditional SQL queries rely on exact matches and predefined relationships, which makes searching unstructured or semantically rich data difficult. Oracle 23ai changes this model by introducing native AI Vector Search directly inside the database. With vector embeddings stored and indexed alongside traditional data, Oracle enables similarity-based searches that go beyond keywords and exact values. This allows applications to find results based on meaning, relevance, and proximity in vector space, making it possible to search text, documents, images, and other complex data types in a far more intuitive and powerful way, all using SQL.

AI Vector Search

Instead of searching for exact words, Vector Search allows you to search for concepts. By using the new VECTOR data type, you store "embeddings"—mathematical representations of data. This allows you to perform a "similarity search." For example, if you search for "staffing issues," the database can find documents about "low headcount" or "recruitment delays" because it understands they are conceptually related.

Example:

Assume you have a table called docs that stores documents along with their vector embeddings. 

To see the power of Vector Search, I need a larger dataset. With only one row, the "closest" result is always the same one! Here is a script to quickly populate my docs table with 100 rows of synthetic data. I’ve used a CONNECT BY loop and DBMS_RANDOM to generate unique titles and random vector coordinates so that the similarity search actually has something to compare.

INSERT INTO docs (id, title, embedding)
SELECT 
    level + 1, 
    'Tech Manual Part ' || (level + 1),
    -- Generating a random 3-dimensional vector string like '[0.12, -0.45, 0.88]'
    '[' || 
        ROUND(DBMS_RANDOM.VALUE(-1, 1), 2) || ',' || 
        ROUND(DBMS_RANDOM.VALUE(-1, 1), 2) || ',' || 
        ROUND(DBMS_RANDOM.VALUE(-1, 1), 2) || ']'
FROM dual
CONNECT BY level <= 100;

COMMIT;

Let’s take a look at the table structure with this query:

set line 200;
col ID for 9999;
col TITLE for a20;
col EMBEDDING for a50;
set pagesize 5000;
select * from docs;

Instead of writing a complex text search, you can ask the database to find documents that are conceptually similar to a given idea.

What is the following query doing

• The vector [0.1, 0.5, -0.2] represents the concept you are searching for (for example, something related to staffing or workforce challenges).
• VECTOR_DISTANCE calculates how close each document’s embedding is to that concept using cosine similarity.
• Documents with the smallest distance are the most closely related in meaning.
• The query returns the top 3 documents that best match the idea, not based on keywords, but on similarity.

-- Searching for documents similar to a specific concept
SELECT title
FROM docs
ORDER BY VECTOR_DISTANCE(embedding, '[0.1, 0.5, -0.2]', COSINE)
FETCH FIRST 3 ROWS ONLY;

In practice, this means a search for “staffing issues” could return documents talking about low headcount, hiring delays, or resource shortages, even if the exact phrase never appears. The database understands the relationship between these ideas.

Conclusion

AI Vector Search in Oracle 23ai represents a major shift in how databases handle modern data workloads. By embedding vector storage, indexing, and similarity search directly into the database engine, Oracle eliminates the need for external vector stores or separate AI infrastructure. This keeps data secure, reduces architectural complexity, and improves performance by allowing vector queries to run where the data already lives. As a result, Oracle 23ai enables developers and data teams to build smarter, AI-driven applications using familiar SQL tools while unlocking semantic search capabilities that were previously difficult or costly to implement. Vector search is no longer an add-on; it is now a core database capability.

Oracle 23ai: SQL Productivity Power-Up – Streamlined SELECT Syntax

 

Introduction

Oracle 23ai clearly shows that Oracle is putting developers first when it comes to SQL. For a long time, writing Oracle SQL meant putting up with extra clutter—things like always using FROM DUAL or repeating the same expressions again and again in GROUP BY clauses. It worked, but it often made simple queries feel unnecessarily complicated.

With the latest releases, Oracle is clearly trying to make SQL easier and more pleasant to use. The focus is on writing cleaner, more natural code with less repetition. In this blog, I will look at four small but powerful improvements that remove a lot of that old hassle and let you focus on the data and logic instead of outdated syntax rules.

SELECT without FROM

Previously, Oracle required a FROM clause for every SELECT statement, forcing developers to use the DUAL to retrieve constants, system dates, or perform calculations. Now, you can simply SELECT what you need. It is cleaner, shorter, and matches the behavior of PostgreSQL and SQL Server.

Example 

In this example, we retrieve the current system date, add a simple label, and calculate a projected value, all without using DUAL.

SELECT 
    SYSDATE AS current_time, 
    'HR_REPORT' AS report_tag, 
    (100 * 1.15) AS projected_value;

VALUES Clause Inside SELECT

The VALUES clause used to be limited to INSERT statements, but Oracle 23ai elevates it into a powerful table constructor. You can now use VALUES directly inside a SELECT, effectively creating a small in-memory table on the fly.

This is especially useful when you need a temporary lookup set without creating a table, using UNION ALL, or relying on temporary tables.

Example: 

In this example, the VALUES clause is used to create a temporary, in-memory table directly inside the query. Think of it as a small dataset that exists only for the duration of this SELECT. Each row in the VALUES list represents an employee ID paired with a performance rating.

The alias v(emp_id, rating) gives names to the two columns created by the VALUES clause. This is important because it allows Oracle to treat the result just like a normal table, with clearly defined column names.

The query then joins this temporary table to the EMPLOYEES table using a standard JOIN. The join condition matches employees.employee_id with v.emp_id, ensuring that only employees listed in the VALUES clause are returned.

As a result, the query displays each employee’s last name alongside the temporary rating assigned in the VALUES list. 

SELECT e.last_name, v.rating
FROM employees e
JOIN (VALUES (100, 'A+'), (101, 'B'), (102, 'A')) AS v(emp_id, rating)
  ON e.employee_id = v.emp_id;
  
---The following is another syntax for it with the same output
  SELECT e.last_name, v.rating
FROM employees e,  (VALUES (100, 'A+'), (101, 'B'), (102, 'A')) AS v(emp_id, rating)
  where e.employee_id = v.emp_id;

GROUP BY ALL

Complex reports often require grouping by many columns, which means copying those same column names into the GROUP BY clause. This is tedious and error-prone. GROUP BY ALL solves this by automatically grouping on every non-aggregated column in the SELECT list.

Example

This query calculates the average salary for each combination of department, job, and manager. It uses GROUP BY ALL to automatically group by all the non-aggregated columns in the SELECT list, so you don’t have to list them again manually.

The HAVING clause then filters the grouped results, keeping only those groups where the average salary is greater than 5,000. In addition, this example shows that in Oracle 23ai, you can use a column alias (avg_sal) directly in the HAVING clause, which makes the query shorter and easier to read.

SELECT 
    d.department_name, 
    e.job_id, 
    e.manager_id,
    ROUND(AVG(e.salary), 2) AS avg_sal
FROM employees e , departments d 
 Where  e.department_id = d.department_id
GROUP BY ALL
Having avg_sal >5000;

Conclusion

Oracle 23ai and 26ai make it clear that Oracle is focusing more on developers and their day-to-day work. These updates don’t change the power of Oracle SQL, but they make it much easier and nicer to use. By removing extra syntax, writing SQL becomes simpler, clearer, and less error-prone.

Whether you’re creating reports, running analytics, or just querying data, these improvements will quickly feel natural. After using them, the older ways of writing SQL will seem outdated, and that’s a good thing.

How to Roll Forward a Standby Database Using RMAN Incremental Backup After Adding a Datafile to Primary

Rolling forward a standby database involves applying incremental backups to synchronize changes made to the primary database. This process becomes more complicated when a new datafile is added to the primary database. In this article, I will outline the essential steps and considerations involved in rolling forward a standby database using RMAN incremental backups after the addition of a datafile on the primary side.

Enhancing Query Performance: Leveraging In-Memory Optimized Dates in Oracle 23ai

In-Memory Column Store

Unlike traditional row-based storage, The In-Memory Column Store (IM column store) stores tables and partitions in memory using a columnar format optimized for rapid scans. This columnar format is optimized for analytical workloads, allowing for efficient scanning of specific columns without needing to read entire rows. 

In-Memory Optimized Dates

To enhance the performance of DATE-based queries DATE components (i.e. DAY, MONTH, YEAR) can be extracted and populated in the IM column store leveraging the In-Memory Expressions framework. This approach enables faster query processing on DATE columns, significantly improving the performance of date-based analytic queries.

Oracle 23ai: New DELETE and MERGE Enhancements with Practical Examples

Introduction

For a long time, working with DELETE and MERGE in Oracle meant dealing with awkward syntax and unnecessary complexity, especially when joins or feedback from DML operations were involved. Oracle 23ai finally removes many of those pain points.

In this blog, I will look at three practical enhancements: join-based deletes using DELETE … FROM, capturing affected rows with DELETE … RETURNING, and retrieving results directly from MERGE operations using RETURNING.

Oracle 23ai: New UPDATE Statement Features and Practical Examples

 

Introduction

Oracle Database 23ai brings meaningful improvements to the UPDATE statement, making everyday data changes simpler and more intuitive. Long-standing limitations—such as complex join updates and extra queries to retrieve updated values—are now addressed with cleaner, more expressive SQL. Features like UPDATE … FROM enable direct join-based updates, while UPDATE … RETURNING allows immediate access to modified data. Native BOOLEAN support and the DEFAULT ON NULL clause further reduce workarounds and conditional logic. Together, these enhancements help developers write clearer, safer, and more maintainable UPDATE statements that better reflect real-world data operations.