How to help AI models generate better natural language queries

Using natural language to query your data is an easy way to answer business questions. One question I’m often asked is, “how can this work on my data? Have you seen my table and column names? The names are meaningless.” Fear not! It is possible when you’re using Autonomous Database.

There is no magic. If your table and column names aren’t descriptive, you can help the large language model (LLM) interpret the meaning of tables and columns by using a built-in database feature called “comments”. Comments are descriptions or notes about a table or column’s purpose or usage. And, the better the comment, the more likely the LLM will know how to use that table or column to generate a the right query.

Adding Comments to your tables and columns

Let’s take an example. My database has 3 tables. The table names and columns are meaningless:

TABLE1	TABLE2	TABLE3
CREATE TABLE table1 ( c1 NUMBER, c2 VARCHAR2(200), c3 NUMBER )	CREATE TABLE table2 ( c1 TIMESTAMP, c2 NUMBER, c3 NUMBER, c4 NUMBER, c5 VARCHAR2(100), c6 NUMBER, c7 NUMBER )	CREATE TABLE table3 ( c1 NUMBER, c2 VARCHAR2(30) )

There is zero chance that a natural language query will know that these tables represent movies, genres and streams. We can fix that ambiguity by adding database comments:

TABLE1

COMMENT ON TABLE table1 IS 'Contains movies, movie titles and the year it was released';

COMMENT ON COLUMN table1.c1 IS 'movie ids. Use this column to join to other tables';

COMMENT ON COLUMN table1.c2 IS 'movie titles';

COMMENT ON COLUMN table1.c3 IS 'year the movie was released';

TABLE2

COMMENT ON TABLE table2 IS 'transactions for movie views - also known as streams';

COMMENT ON COLUMN table2.c1 IS 'day the movie was streamed';

COMMENT ON COLUMN table2.c2 IS 'genre ids. Use this column to join to other tables';

COMMENT ON COLUMN table2.c3 IS 'movie ids. Use this column to join to other tables';

COMMENT ON COLUMN table2.c4 IS 'customer ids. Use this column to join to other tables';

COMMENT ON COLUMN table2.c5 IS 'device used to stream, watch or view the movie';

COMMENT ON COLUMN table2.c6 IS 'sales from the movie';

COMMENT ON COLUMN table2.c7 IS 'number of views, watched, streamed';

TABLE3

COMMENT ON TABLE table3 IS 'Contains the genres';

COMMENT ON COLUMN table3.c1 IS 'genre id. use this column to join to other tables';

COMMENT ON COLUMN table3.c2 IS 'name of the genre';

 

That’s it! The meaningless table and column names can now be understood by the LLM using Select AI.

Set up your Select AI profile to use comments

A Select AI profile encapsulates the information needed to interact with an LLM. It includes the AI provider, the model to use, the source tables used for natural language queries – and whether comments should be passed to the model for SQL generation.

begin

  dbms_cloud_ai.create_profile(

    profile_name => 'myprofile',

    attributes =>       

        '{"provider": "azure",

          "azure_resource_name": "my_resource",                    

          "azure_deployment_name": "my_deployment",

          "credential_name": "my_credential",

          "comments":"true",  -- enable the use of comments

          "object_list": [

            {"owner": "moviestream", "name": "table1"},

            {"owner": "moviestream", "name": "table2"},

            {"owner": " moviestream", "name": "table3"}             

          ]          

          }'

    );

    dbms_cloud_ai.set_profile(

        profile_name => 'myprofile'

    );

end;

/

Run your queries

You can now start asking questions using natural language against your complex schema. Even though the table and column names are meaningless, the LLM is able to identify the appropriate tables and columns through the comments and generate a query:

Summary

There is no magic! Properly describing your data will help you use natural language to get answers. Comments not only help an LLM successfully formulate queries, they also help you understand your data as well!

Continue reading

Announcing Oracle Database 23ai: General Availability

General Availability

We are pleased to announce the general availability of Oracle Database 23ai. Over the last four years, Oracle Database Development has worked hard to deliver the next long-term support release of the Oracle Database, with a focus on AI and developer productivity. Given the focus on AI in this release of the database, we decided to change the database's name from Oracle Database 23c to Oracle Database 23ai. This reflects the focus of this release as well as the climate in which it is being released.

Game-Changing Innovations

Oracle Database 23ai has focused on three key areas.

• AI for Data
• Dev for Data
• Mission Critical for Data

In this Blog, we'll describe some of the critical features of this release that highlight our work on these important focus areas and provide details when you see them on your platform of choice.

AI for Data

Our goal with AI for Data is two-fold:

1. To enable app developers to add AI functionality into their data-driven applications more easily
2. To incorporate GenAI capabilities into our products so that all users of Oracle Database, from data analysts to app developers and DBAs, can be more productive.

This is achieved by employing AI to streamline Oracle's data management processes, offering AI algorithms for uncovering new insights from your data, facilitating natural language interactions for database queries, and empowering users to store AI-generated vectors for swift and efficient object similarity searches. Let's delve into these features further.

Augmenting a new generation of AI models

People are feeling the impact of AI Large Language Models (LLM), exemplified by offerings such as ChatGPT, Google Gemini, Cohere, and LLaMA. Whilst LLMs are having a profound impact on the way we perform everyday tasks, one of the challenges that these engines face is that they are restricted to answering questions in the specific time window of their creation. They also lack context and details of the data held within your organization. To make these and other LLMs useful, we need to augment their existing understanding with data relevant to the questions you ask about the data held within your database. 

In Oracle Database 23ai, we are introducing AI Vector Search, a powerful new technology that enables you to leverage a new generation of AI models to generate and store vectors. These vectors, sometimes referred to as embeddings, are multi-dimensional representations of documents, images, videos, sound, etc. By encoding these objects as vectors, you gain the ability to look for similarities between them using mathematical calculations. The real power of Oracle Database23ai's solution is that you can combine these similarity searches with searches on your business data using simple SQL. Anyone with a basic understanding of SQL can create a powerful statement combining similarity and other search criteria. These types of queries provide LLMs with additional context, augmenting their knowledge and making their responses more accurate and relevant to your customers' or organizations' questions. To enable this functionality, we've added a new data type, new vector indexes, and extensions to the SQL language to make it incredibly simple to query vectors alongside your existing business data by leveraging Oracle Database 23ai's advanced analytical capabilities.

Secure encoding of data

To enable the considerable benefits that LLMs offer, we first need to enrich our understanding of our datasets and the objects within them. The models to encode these objects are potentially large and complex pieces of code. As a result, it has become common practice to offload this processing to third-party services. This can require you to share potentially sensitive information with third parties with the inherent risks that entails.

To ensure the secure encoding of objects, Oracle Database 23ai allows you to load your own trusted AI models directly into the database through its support of the ONNX standard. This enables Oracle Database 23ai to encode objects as they are inserted into the database. This enables the secure near real-time inferencing of objects and ensures that data does not need to be extracted from the database and handed off to a third-party service.

Asking questions naturally

While SQL is an incredibly powerful language, it can be a little challenging to get started with. To enable a broader range of users to ask the most complex questions of the Oracle Database, we are integrating with LLMs such as Cohere and Llama to allow you to ask questions using natural language. So you can simply ask a question like "Show me the last 4 quarters' sales of products that are popular with the younger generation". Oracle Database 23ai will share the table's metadata to answer the question with the LLM along with the user's question. The LLM understands the meaning of "younger generation" and converts it into a data range as a filter in the SQL query, which it returns to the database. We, of course, aren't limiting Oracle Database 23ai to just allowing you to query the information within the database. You'll also have the ability to ask questions on all aspects of the Oracle Database. 

Finding new insights in data

Since the release of Oracle Database 9iR2 over 20 years ago, the database has featured built-in Machine learning (ML) algorithms, enabling you to quickly look for patterns and trends in data held within its tables and predict customer behaviors. These ML algorithms give you the ability to use sophisticated ML algorithms without the need for complex ETL operations to extract the data and write back results.  These models could then be used to classify, cluster, and predict as your data is inserted or loaded into the Oracle Database, providing tangible business benefits like customer recommendations and fraud detection. Since that release, we've improved and added new ML algorithms and capabilities to the Oracle Database, creating one of the industry's most sophisticated and capable data mining platforms.

Available in all editions of the Oracle Database

AI Vector Search is part of Oracle Database 23ai and is available at no additional charge in Enterprise Edition, Standard Edition 2, Database Free, and all Oracle Database cloud services.

Feedback from early testers

AI Vector Search generated a great deal of interest even before the launch, and many customers and partners were keen to try out and provide feedback on the latest capabilities like AI Vector Search. Here's a sample of the feedback we received.

“We are happy to see AI Vector Search added to Oracle Database. We appreciate that we can run AI Vector Search in the same Oracle Database as our other workloads, which allows us to provide a reliable and secure solution.”

Shinichiro Otsuka, NRI Certified IT Architect, Nomura Research Institute, Ltd.

“AI Vector Search changes the way we think of the business by bringing AI to your data: for instance we can use natural language to query our documentation to find answers to questions customers are asking. This makes the process of serving customers much faster and the quality of the service improves as well. Being able to use the power of Generative AI with our own data makes a huge difference."

Heli Helskyaho, CEO, Miracle Finland Oy

“We have been consulting on various business transformation projects using advanced digital technology. Oracle Database 23ai AI Vector Search is very simple to implement by adding a vector column to the current relational database system. I am thrilled that the vector data is within a single record with existing data so that similarity reordering can be done with a single SQL statement. This makes it easy to combine large language models and company data to create and operate enterprise-level retrieval augmented generation systems.”

Shigeru Urushibara, Chairman, UL Systems Inc.

“With the decreasing domestic workforce in Japan, XCAT‘s customers want to adopt labor-saving measures. Oracle Database 23ai’s AI Vector Search allows customers to use SQL for vector retrieval, making it an excellent tool for engineers with little or no AI knowledge to handle vector data. In addition, the ability to manage AI data at no additional cost is a major attraction for many Oracle Database customers."

Mitsunori Yamane, President, Cross Cat Co., Ltd.

Dev for Data

Oracle Database 23ai focus was to make the experience of developing applications simpler by removing the complexity associated with your database interaction. Removing complexity from the application development process means you get more opportunities to focus on the intricacies of creating elegant applications that meet your customer's requirements rather than getting bogged down in technical details. Moreover, reducing complexity can lead to faster development cycles, this is crucial in today's fast-paced digital landscape, where market demands can shift rapidly.

JSON or Relational, a difficult choice?

Oracle 23ai introduces a number of key technologies to reduce the complexity for developers. Perhaps the most innovative and important of them is the introduction of JSON Relational Duality. This technology allows you to leverage the power of relational and the simplicity of JSON development approaches all within a single app. JSON provides an elegant data modeling approach where all of the relevant data needed to answer a query is contained in a single object without the need to perform complex joins across tables. The relational approach provides a flexible, storage-efficient, consistent data model that is easy to query with languages such as SQL. Historically the problems come when you are forced to choose which database modelling approach to take at the start of your application lifecycle. Decisions made at this stage may have implications much later on as the business requirements change. The beauty of JSON Relational Duality is that you can benefit from both approaches without the need to use complex and unwieldy Object Relational Mapping frameworks (ORMs). 

By creating simple JSON Duality views on top of relational tables you can build applications that use documents to query and update the underlying data sets. Data duplication, a fundamental issue in document modeling, is avoided, and even the complexities of database-level concurrency control are hidden from you as Oracle Database 23ai manages document-level serializability on your behalf. You can continue to use simple HTTP PUT, POST, and GET operations or use language-specific API such as Oracle's SODA API or even MongoDB API directly against the Oracle database. With JSON Duality Views the old phrase of "You can't have your cake and eat it" no longer holds true.

SQL just got a little easier

SQL remains one of the most popular development languages in use with application developers today. Its versatility when asking the simplest or most complex questions of your business data has made it the cornerstone of data management, a position it is likely to hold for the foreseeable future. That is not to say there are areas where it could be improved. In Oracle Database 23ai we listened to common enhanced requests and looked at what could be made simpler or offer more compelling ways to interact with the database. Examples of just a few of the SQL improvements we've made include the addition of two new datatypes "Boolean" and "Vector", we added "Data Usecase Domains" to allow you to enrich the meaning of datatypes used in table definitions, the removal of the need for "from dual" in simple SQL statements to improve readability, making it easier to aggregate over the "interval" datatype, the introduction of table value constructors to make it easy to specify multiple rows in insert select or merge statements. These are just a few of the many improvements we've made to SQL.

As with AI Vector Search, we also appreciate receiving a lot of useful feedback from customers and partners who participated in the Oracle Database 23ai Beta program. Here's a sample of the comments we received -

“JSON Relational Duality Views are a game changer for application development by finally solving the problem of object-relational mapping. And, we at last have ACID-compliant transactions in a multi-tier or web architecture.”

Peter de Vaal, Principal Consultant, Transfer Solutions

“JSON Relational Duality gives you the best of both worlds: Relational experts can work on the same data as Document database experts without having to learn a new skill. App developers have the flexibility they need, and database management is simpler. Oracle has taken a massive leap beyond other databases.”

Patrick Barel, Sr. Oracle Consultant, Qualogy

“Oracle Database 23ai with JSON Relational Duality Views and Oracle APEX have allowed us to create full-featured, standards-compliant applications for working with JSON healthcare data.”

Rade Pobulic, Technical Director, VDEL Informationstechnik & Consulting GmbH

Property Graphs modeling complex relationships

Graph databases provide a fresh perspective on modeling intricate relationships found in real-world scenarios. Yet, until recently, developers grappled with proprietary languages and having to ship data to databases tailored for managing these relationships. This hindered the widespread acceptance of this groundbreaking technology. Fortunately, the industry has taken a collaborative approach to bring graphs to the masses, culminating in the establishment of standards. These standards empower developers with the versatility and familiarity of SQL, fostering a more accessible landscape. Oracle Database 23ai stands as the pioneering commercial database to embrace this newfound standard. With these advancements, we anticipate a significant surge in the integration of Graph databases within enterprise ecosystems.

As with JSON Duality Views, you can express your intent on how you would like to use your data held within existing relational tables or your JSON collections through the user of property graph views. With a simple view, you can enrich the data to model the potentially complex relationships that your data holds. A new SQL extension makes it much simpler to write powerful queries to find a deeper understanding of information that would have been hard to describe using standard SQL. We've also added a new set of sophisticated graph algorithms (shortest path, classification, connects, etc. ), which will be released shortly after GA.

The property graph Property Graph is available in all editions of Oracle Database 23ai.

Mission Critical for Data

Oracle Database is renowned for powering some of the world's most vital applications. Over the years, it has established itself as a stalwart guardian of businesses' most precious asset: data. Unrivalled in its breadth of features, Oracle Database excels in safeguarding critical systems against unforeseen downtime. A prime example is Oracle Real Applications Clusters (RAC), a cutting-edge technology that enables all-active, shared disk databases, ideal for the most mission-critical scenarios. Oracle Active DataGuard and Oracle GoldenGate offer unparalleled flexibility in crafting both homogenous and heterogeneous architectures for data replication. 

Shard better, faster, simpler

For use cases where businesses are looking to distribute their data over larger distances, either for regulatory reasons where the data of users must be stored in the country the customer resides in or to reduce the latency of access associated with globally distributed applications: We introduced support for database sharding in Oracle Database 12c. Sharding distributes a dataset across a number of Oracle databases but is managed as a single logical data set. This sharded architecture allowed the database to be scaled up or down by adding and removing shards and allowing Oracle to automatically rebalance the dataset. Oracle is able to do this online and with little impact on the business as the concept of sharding is baked directly into the database.

In Oracle Database 23ai our Globally Distributed Database technology introduces support for Raft replication. This is a consensus-based protocol that facilitates the automatic configuration of replication across all shards. With raft, replication holds copies called replication units across all of the shards. In the advent of a shard failure or reconfiguration, Oracle Database 23ai automatically rebalances the data. The new Raft protocol also enables sub-second failover to ensure continued business continuity. 

Oracle 23ai brings a number of improvements across the board for our high availability products, such as the new "Local Rolling Database Maintenance" to simplify the impact of rolling patches in a cluster by standing up another instance on the same node that is receiving the patch. We are also introducing support for a new Data Guard Per Pluggable Database (PDB) allowing Pluggable Database level disaster protection between two Container databases (CDB) with each CDB running an active workload. We've also improved the integration and management of PDBs within Oracle Clusterware.

Caching data made simple

Caching data in the application tier can offer considerable benefits. It is often used to make applications more responsive and reduce the load on the database, as queries are offloaded to the cache typically located closer to the code. However, to achieve these benefits, developers face a number of challenges; one of the biggest is ensuring that the cache is consistent with the data held within the backend database or risk serving stale and potentially incorrect data. Developers are often forced to create complex code to manage these scenarios and often have to resort to design patterns such as "time to live" where data is periodically flushed from the cache and reloaded. These and many other challenges often mean the cache becomes difficult and expensive to maintain and, in some instances, a bigger problem than the one it was designed to solve.

In Oracle Database 23c we are looking to solve the problems of caches built against the Oracle Database with a new feature called "True Cache". True Cache is a diskless Oracle Instance running in front of the primary database. The True Cache instances leverage Active Data Guard technology to ensure it is automatically updated as data changes in the primary database instance. One of the most important features of this new caching technology is its simple configuration and minimal code changes that are needed to leverage the benefits it offers. 

Making SQL execution safer

One of the challenges that organisations face is continued attempts by malicious actors to steal data or disrupt their day-to-day activities. In Oracle Database 23ai, to prevent the execution of unauthorised SQL, be they via SQL-Injection attacks or simply by unauthorised staff, we are introducing SQL Firewall. SQL Firewall allows you to train the Database on what SQL is permitted to be executed. The "allow list" of SQL can then be used to either log unauthorised SQL or block its execution. Additional criteria can be added to allow exceptions for IP ranges or authorised calling programs. The power of SQL Firewall is that it is part of the Oracle Database, and as a result, it imposes very little overhead on the execution of SQL. Setting up and training SQL Firewall is incredibly simple being able to be done from a GUI or via a few simple calls to stored procedures. 

Over 300+ new features

I discussed the cloud release of Oracle Database 23ai and described some of the 300+ new features. Some other notable features include 

• Java Script Stored Procedures
• Priority Transactions
• Data Usecase Domains
• Schema privileges
• Annotations
• Boolean datatype
• Developer Role
• Read-Only-Per-PDB Standby
• JSON Schema
• Real-Time SQL Plan Management
• Performance Improvements
• Lock Free Reservations
• Improved Microservice Support
• XML and JSON Search Index Enhancements
• And many many more

Platform Rollout

Oracle Database 23ai is now available on Oracle Exadata Cloud@Customer, OCI Exadata Database Service and OCI Base Database Service. It is also available in the Azure Oracle Database Service. For developers, Oracle Database 23ai is now available in Always Free Autonomous Database as well for download in the Autonomous Database 23ai Container Image and Oracle Database 23ai Free. We are also pleased to announce that Oracle GoldenGate 23ai and GoldenGate 23ai Free as also available for download. On-premises versions of Oracle Database 23ai for Linux (OL & RHEL 8/9) and Windows will be out shortly with other platforms rolling out over the coming months. For further details please check the Oracle Support note Doc ID 742060.1

The general availability of Oracle Database 23ai marks a new phase in this important release. Thousands of developers have contributed to its design, development and testing resulting in what we believe is the very best enterprise database in the market.

Source: oracle.com

Continue reading

Autonomous Health Framework (AHF) Version 24.2 Released

Oracle Autonomous Health Framework (AHF) works autonomously 24x7 to keep database systems healthy and running while minimizing human reaction time. Utilizing applied machine-learning technologies, AHF provides early warnings or automatically solves operational runtime issues faced by Database and System administrators in the areas of availability and performance.

It's the same technology Oracle uses in the cloud to keep the Autonomous Database running, and it's available for you at no extra cost as part of your existing software support agreement.

Insights in Diagnostic Collections

AHF now collects Insights data with diagnostic collections.

AHF Insights provides a bird's-eye view of the entire system. It helps spot problems, drill into the root cause and understand how to resolve.

AHF insights provides a view of:

• Configuration
• Environment topology
• Metric
• Logs

Data is viewable in reports covering:

• System Configuration
• System State
• Anomalies in Operating System
• Best Practice Compliance
• System Traces
• Root cause for issues and fixes in some anomalous cases

AHF diagnostic collection zips now include a sub-zip named <machine>_insights_<time>.zip containing the Insights report. This makes it quicker and easier to understand and resolve problems.

Insights for Single Instance Systems

AHF Insights are now available on single instance Linux systems.

AHF is on a mission to make it easier to maintain Oracle Databases.

Customers rely on AHF Insights to turn complex diagnostic data into simple actionable steps. However, until now it has only been available on Grid Infrastructure systems.

With AHF 24.2 Insights now adds support for single instance Linux systems, bringing this much-loved ease and simplicity to even more customers.

To generate an Insights report simply run:

ahf analysis create --type insights

Transfer the resulting zip to a system with browser support, extract it and open index.html.

Component Diagnostic Purging

AHF now supports component level diagnostic purging.

For many years, AHF has had automatic purging capability, which removes diagnostic collections and log files based on a purge policy.

However, there are some situations where diagnostics within one area grow faster than others, so it can be useful to be able to purge on a component-by-component basis for files that don’t meet the purge policy.

AHF has now extended the purge capability to enable cleaning at a component level, as well as to purge even if TFA is not running.

Available purge options can be seen with the command:

tfactl purge -h

Exadata Netdiag in Diagnostic Collections

AHF now captures Exadata netdiag output as part of the OS component in diagnostic collections.

Oracle Exadata systems have a tool called netdiag, which generates diagnostics for Nvidia/Mellanox. Output from netdiag is often required by Oracle Support to help resolve network specific issues.

AHF now collects Exadata netdiag output as part of the OS component of TFA collection. If the file modified date falls in the collection range, then the file will be included in the collection.

To ensure netdiag files are included within a collection use the command:

tfactl diagcollect -os -last 1h

Combine Diagnostics From All Machines in a Single Zip File

TFA diagcollect introduces the command option -singlearchive to combine zips from all nodes into a single diagnostic collection zip.

AHF has a long-standing ability to collect diagnostics from all Grid Infrastructure (GI) nodes using the tfactl diagcollect. This creates a series of zips, one for each node on the originating machine. However for customers with a large cluster, this can result in needing to provide many different zip files to Oracle Support.

AHF can now combine all files into a single zip archive on the initiating node by added the option -singlearchive.

For example:

tfactl diagcollect -last 1d -os -singlearchive

Source: oracle.com

Continue reading

Oracle Database 23ai Now Available in Cloud

We are pleased to announce the availability of Oracle Database 23ai on Exadata Database Service, Base Database Service, and Always Free Autonomous Database. Oracle Database 23ai is the latest long-term support release of Oracle Database, with a focus on AI, developer productivity, and mission-critical data.

Oracle Database 23ai enables developers to add AI functionality to data-driven applications more easily and incorporate GenAI capabilities so that all users of Oracle Database, from data analysts to developers and DBAs, can be more productive. Included are new features such as AI Vector Search, a powerful new technology that enables you to leverage a new generation of AI models to generate and store vectors.

Oracle Database 23ai Converged Database

Oracle Database 23ai also extends Oracle’s converged database approach with JSON Relational Duality and Operational Property Graph. These enhancements enable developers to use simple JSON document and graph APIs to access data yet store the data in a more efficient relational structure. With Oracle Database 23ai developers can more easily create new innovative applications that take your business to the next level.

Oracle Database 23ai further enhances mission-critical capabilities by improving response times with True Cache, helping protect databases against SQL injection attacks with SQL Firewall, and introducing RAFT replication in Oracle Globally Distributed Database to help achieve ultra-high scalability and availability. In addition to these highlights, there are over 300 major new features that are available with Oracle Database 23ai.

Oracle’s database services in the cloud are the best place to run Oracle Database 23ai. Cloud automation can quickly provision new databases, including all advanced options such as Oracle Real Application Clusters (Oracle RAC) and Oracle Active Data Guard. You can get started quickly with Always Free Autonomous Database enabling you to easily try the new features and develop new applications. If you need more resources and control, you can provision your databases on flexible VM shapes with Base Database Service or on dedicated, database-optimized infrastructure with Exadata Database Service. You can also deploy Oracle Database 23ai in an Oracle Cloud Infrastructure (OCI) public cloud region, in a hybrid cloud with Oracle Exadata Cloud@Customer, or a multicloud environment with Oracle Database@Azure.

Oracle Exadata is the preferred platform for running Oracle Database and has been enhanced with new features for Oracle Database 23ai. In addition to numerous performance features for OLTP, analytics, and mixed workloads, Exadata now includes new optimizations for accelerating AI vector database operations by up to orders of magnitude. Exadata delivers fast end-user response with higher numbers of users, larger volumes of data, and extremely low latency for AI Vector Search.

How does Exadata do this? Exadata uses both in-memory and storage-based vector indexes to deliver high performance AI Vector Search at any scale. AI Vector Search in Exadata uses a new storage protocol that is optimized to achieve low latency even with large volumes of vector data. Exadata further improves performance by offloading AI Vector Search functionality to the storage servers.

We are excited for you to use Oracle Database 23ai in the cloud and develop applications using its new features.

Source: oracle.com

Continue reading

Unveiling the Power of Oracle Globally Distributed Database: Oracle Database 23ai Advancements

In the realm of database technology, Oracle Database 23ai marks a transformative era by ushering in a wave of advancements. Among these advancements, a standout focus lies on augmenting the capabilities of the Oracle Globally Distributed Database. As we approach this release, our attention centers on the integration of pioneering features tailored for distributed data environments. This blog serves as a preview, shedding light on the exciting enhancements Oracle Database 23ai brings to the Oracle Globally Distributed Database. These new features are poised not only to address challenges but also to reshape the landscape of distributed database operations, bringing forth heightened performance and unparalleled efficiency. As we delve into the specifics of these transformative features, Oracle Database 23ai emerges as a pivotal milestone, empowering businesses with tools to harness the full potential of distributed data scenarios. This blog sets the stage for exploring these enhancements, offering insights into how Oracle Database 23ai elevates the capabilities of the Oracle Globally Distributed Database, propelling it into a new era of excellence.

New Features: Oracle Globally Distributed Database and 23ai

1. Raft Replication

One of the notable features introduced in Database version 23ai is Raft replication (Consensus based replication protocol), offering automatic configuration of replication across all shards. Raft replication is integrated and transparent to applications. Raft replication automatically reconfigures replication in case of shard host failures or when shards are added or removed from the sharded database. The system declaratively configures the replication factor to help maintain a specified number of replicas available at all times. In the event of a node failure, failover is swift, and all nodes remain active. Crucially, this feature encompasses an automatic sub-second failover mechanism, reinforcing data integrity and operational continuity. This capability suits organizations requiring a highly available and scalable database system.

Ensuring Data Integrity and Seamless Operations

Raft Replication brings streamlined management and heightened availability to distributed database environments. This proves pivotal in scenarios where high availability and data integrity are paramount. With rapid fail-over and consistent data across nodes, Raft Replication empowers organizations to deliver optimal user experiences and meet stringent SLAs. This benefit is especially crucial in critical applications where uptime and data consistency directly influence business success.

Raft Replication

2. Directory-Based Sharding Method

Directory-based sharding introduces a versatile user-defined approach, where the location of data records associated with a sharding key is specified dynamically at the time of insert based on user preferences. The process involves storing key location information within a directory, housing an extensive collection of key values, often numbering in the hundreds of thousands. This technique not only facilitates the dynamic movement of individual key values between locations but also allows for efficient bulk movements. The capability to adaptively shift key values offers a high degree of customization, enhancing data management, scalability, and load balancing across databases or clusters.

Key Value Grouping for Enhanced Data Organization

Directory-based sharding is useful when you need to group certain key values together into the same location or chunk for affinity purposes, and when needed this group can be moved together in an efficient manner

An example is a social network application, where grouping together customers who often exchange messages on the same shard minimizes the cross-shard traffic. The grouping must be preserved during re-sharding when data is moved between shards. On the other hand, if a member of a group starts communicating more with members of another group, their data must be moved to the appropriate group with minimal impact on the application.

3. Automatic Data Move on Sharding Key Update

Automatic data move on sharding key update is a potent attribute within Oracle Globally Distributed Database that streamlines data management within sharded tables. Sometimes a sharding key value needs updating. The previous solution was to delete the data associated with the old key value and re-insert it with a new key value. The goal of this enhancement is to allow row movement both within a shard and between shards.

When the sharding key value on a particular row of a sharded table is updated, Now moving the data to a new location is handled by Oracle Globally Distributed Database, whether it is in a different partition on the same shard or on a different shard.

This feature provides you with the flexibility to update the sharding key without worrying about the destination of the records, because this feature allows Oracle Globally Distributed Database to take care of the row movement regardless of the row destination, which could be on a different shard.

Streamlined Data Updates

In a global e-commerce platform with sharded tables, automatic data movement on sharding key updates helps ensure seamless transitions. For instance, when customers relocate, their order history effortlessly shifts to the new region's shard, eliminating manual intervention. This dynamic reallocation empowers businesses with efficient sharded table management, maintaining data integrity and uninterrupted user services amidst changes.

4. Fine-Grained Refresh Rate Control for Duplicated Tables

Fine-grained refresh rate control for duplicated tables is a powerful feature that enables refresh rate control for individual duplicated tables. This feature allows configuring varying refresh intervals during table creation or via the ALTER TABLE statement. By tailoring refresh rates to data change frequency, resource utilization optimization is achieved. This capability enhances system performance and efficiency by customizing refresh rates per duplicated table.

Customized Data Refresh

In complex data environments, where data change rates vary across tables, fine-grained refresh rate control proves indispensable. Imagine a sales database with high-frequency transaction tables and reference tables that change less often. With this feature, high-transaction tables can be set to refresh more frequently, allowing real-time insights. Simultaneously, reference tables refresh less often, conserving resources. This customization maximizes efficiency, streamlining resource allocation, and bolstering system performance.

5. Synchronous Duplicated Tables

The introduction of synchronous duplicated tables offers a robust mechanism for synchronizing duplicated tables across all shards. This feature introduces a new kind of duplicated table that is synchronized on the shards ‘on-commit’ on the shard catalog. The synchronization occurs when active transactions executing Data Manipulation Language (DML) operations on the duplicated tables in the shard catalog are committed. This feature provides impeccable data consistency and real-time synchronization across all shards, underscoring a heightened level of data integrity.

Ensuring Data Consistency in Real-time

In data-intensive environments, like financial systems, the synchronous duplicated table feature plays a crucial role. As transactions span multiple shards, this feature provides instant synchronization of duplicated table data across all shards upon transaction commitment. Financial data remains uniformly accurate, reducing disparities and delivering real-time insights. Particularly vital in precision-dependent sectors, this feature enhances data integrity and reliability in distributed systems.

6. Sharded Database Coordinated Backup and Restore Enhancements

Coordinated backup and restore functionality in Oracle Globally Distributed Database has been extended to include the following:

• Enhanced error handling and diagnosis for backup jobs
• Improved automation of sharded database restore
• Support for running RMAN commands from GDSCTL
• Support for using different RMAN recovery catalogs for different shards
• Encryption of backup sets
• Support for additional backup destinations: Amazon S3, Oracle Object Storage, and ZDLRA

The benefits of this functionality are:

• Easily diagnose problems in backup jobs
• Backups sets can be encrypted so that the data is secure
• Support for additional destinations other than on-disk storage
• Support for different RMAN catalogs and destinations to abide by data residency requirements

This feature enhances diagnosability and automation in backup and restore jobs while securing data with encryption and supporting additional backup destinations.

Robust Data Protection and Recovery

Imagine a scenario where a sharded e-commerce platform faces a catastrophic data loss event. By utilizing the enhanced coordinated backup and restore functionality, the system gains superior error handling, automated restoration processes, and broader backup destination options such as Amazon S3 and Oracle Object Storage. Additionally, encrypted backup sets enhance data security. This feature helps ensure swift diagnosis of backup issues, safeguarded data integrity through encryption, and efficient data restoration for minimal downtime and maintaining operational resilience.

7. Parallel Cross-Shard DML Support

The Oracle Globally Distributed Database query coordinator can run cross-shard updates and inserts in parallel on multiple shards.

This feature improves cross-shard DML performance by running updates and inserts in parallel rather than serially.

Online Retail Inventory Management

In an online retail business with a globally distributed database, Oracle's Parallel Cross-Shard DML Support plays a crucial role in managing inventory. When restocking products or updating prices across multiple geographical regions, the feature allows simultaneous updates and inserts into different shard locations. This means that inventory data remains synchronized, prices are adjusted in real-time, and customer orders are processed efficiently, providing a seamless shopping experience for customers while optimizing inventory management for the retailer.

8. Automatic Parallel Direct Path Load Using SQL*Loader

SQL*Loader enables direct data loading into the database shards for high speed data ingest. SQL*Loader can load data faster and easier into Oracle Database with automatic parallelism and more efficient data storage.

With this release, SQL*Loader client can automatically start a parallel direct path load for data without dividing the data into separate files and starting multiple SQL*Loader clients. Instead of preparing your tables manually for parallel loads and setting the PARALLEL parameter, you can perform the same task automatically by running SQL*Loader with just one command, setting the degree of parallelism using the DEGREE_OF_PARALLELISM parameter, and setting DIRECT=TRUE.

High-Volume Data Ingestion for Financial Analytics

In the context of a financial services organization, managing vast volumes of market data from various sources is paramount for timely and accurate analytics. With the Automatic Parallel Direct Path Load feature using SQL*Loader, the organization can seamlessly ingest massive datasets into its Oracle Globally Distributed Database. Instead of labor-intensive manual preparation, SQL*Loader simplifies the process and an organization achieves efficient, high-speed data ingestion for real-time financial analysis, enabling informed decision-making and competitive advantage.

To conclude, Oracle Database 23ai brings groundbreaking advancements to the world of database technology. These innovations, with a keen focus on optimizing the Oracle Globally Distributed Database, are set to revolutionize the world of distributed database operations.

From the dynamic capabilities of Raft Replication to the agility of Directory-Based Sharding, these features redefine how businesses manage data across regions. Automatic Data Move on Sharding Key Update provides seamless transitions in evolving scenarios, while Fine-Grained Refresh Rate Control optimizes resource utilization. The Synchronous Duplicated Tables' near real-time data consistency enhances reliability. Coordinated Backup and Restore Enhancements reinforce data protection, while Parallel Cross-Shard DML Support revolutionizes cross-shard data management by allowing parallel updates and inserts, boosting efficiency. Additionally, Automatic Parallel Direct Path Load Using SQL*Loader simplifies high-volume data ingestion, benefitting financial analytics and decision-making processes for organizations dealing with vast market data.

Oracle Database 23ai doesn't just mark an upgrade; it signifies a new era where these features empower businesses to embrace the potential of distributed data scenarios, all underpinned by the reliability of the Oracle Globally Distributed Database.

Source: oracle.com

Continue reading

OCI multicloud solutions with Megaport Cloud Router

Organizations are rapidly moving and scaling their applications into the cloud ecosystem. Enterprise customers who want to optimize and modernize their operations in this cloud ecosystem need interconnectivity to and between multiple cloud service providers (CSPs). While several architectures are available, demand from enterprise customers for split-stack multicloud deployments, where an analytics application runs on one cloud and the data resides on another cloud, are on the rise, especially to support data integration from disparate sources and reporting using the powerful Oracle Analytics Cloud (OAC) platform. The enterprise analytics application user or the line of business (LOB) decision-maker benefits from the modernized connecting and reporting across best-in-class cloud providers’ applications without having to incur expensive migration, data replication, and data redundancy costs.

The described scenario can be accomplished horizontally across any industry and between any CSP’s platform connecting to the OAC platform. This blog post provides a solution that drives economies of scale for the customer’s increasing analytics needs.

Megaport Cloud Router

The Megaport Cloud Router (MCR) is a virtual routing appliance, which delivers a fast, secure, and scalable solution to connect multiple cloud platforms through private network connections in a colocation facility. Coupled with private network solutions, such as Oracle FastConnect and Amazon Web Services (AWS) Direct Connect, the MCR bypasses the public internet and enables cloud-to-cloud connectivity, purpose-built for high throughput and predictable performance. Data moves between multiple public cloud platforms directly, reducing end-to-end latency between Oracle Cloud Infrastructure (OCI) and other cloud platforms for data migrations or steady-state split-stack architectures.

Why Megaport for Oracle multicloud implementations 

Megaport’s multicloud connectivity solution connecting through its virtual cloud router for Oracle enables OCI Database services, such as Autonomous Database and Exadata Database Service on Dedicated Infrastructure, with cloud-to-cloud connectivity to other CSPs, such as Microsoft Azure, AWS, and Google Cloud Platform (GCP). This connectivity benefits the coexistence of best-of-breed applications that can span multiple clouds and helps organizations realize better performance and scalability, while helping to ensure efficiencies in interoperability and availability.

Customers gain the following capabilities by using the Megaport virtual cloud router solution for Oracle:

• Ability to move workloads to hybrid cloud architecture ecosystems.
• Run workloads on low-latency, highly reliable (five nines availability) connectivity between apps and data, especially for data consolidation, integration, and analytics workloads.
• Bypass public internet traffic with dedicated connectivity between client and provider data center networks.
• Multicloud architecture with both full and split stacks.

Oracle multicloud: Split-stack architecture use case enabled by MCR

To test the MCR solution for multicloud connectivity, we built a split-stack multicloud architecture use case with the application stack running OAC on OCI, predominantly used for analytics and reporting by application users. We built another stack on Amazon Redshift, acting as a data warehouse that encompasses trend data and streaming data from multiple data feeds and downstream systems.

The three primary elements of this network architecture are Megaport Cloud Router (MCR), Megaport Virtual Cross Connect (VXC) to OCI FastConnect, and VXC to AWS Direct Connect.

Figure 1: Architecture Diagram for example solution connecting Amazon Redshift to Oracle Analyics Cloud using Megaport Cloud Router

Megaport’s cloud router solution made it possible to read and report on multicloud data sources through OAC’s advanced analytics and visualization capabilities. Organizations across many industries can use this scenario to achieve better performance and cost efficiencies for analytics.

This solution includes the following benefits:

• Ability to run data analytics at the source.
• Modern analytics reporting with real-time data and faster delivery of reports.
• Increased throughput and sub 5-millisecond latency according to standard observations depending on location of cloud regions and onramps. In some cases, it can be lower depending on the architecture and gerographic regions. 
• Dramatically improved data transfer rates from several hours to few minutes. 

Source: oracle.com

Continue reading