2025甲骨文全球AI大会：Whats New in Oracle Database SQL and PLSQL

What’s

Newin

OracleAIDatabase

SQL

and

PL/SQLLRN2875Chris

SaxonDeveloperAdvocatechris.saxon@/sql3Copyrightand/or

itsaffiliatesWhat'swrongwith

these?--AddsensorforAyr,

Scotlandinsertintosensors

(

latitude,

longitude

)

values

(

-4.6204,55.4525

);4Copyright©2025,Oracle

and/or

its

affiliates|--Addtest

datainsertinto

sensor_readingsselectsensor_id,temperature_in_c,reading_timestampfrom

sensorscrossjoingenerate_sensor_readings

(200

);Copyright©2025,Oracle

and/or

its

affiliates|5--Averagetemps

in。C&。F

in

15-minute

groupsselecttrunc

(reading_ts

)

+floor

((reading_ts

-

trunc

(

reading_ts

)

)

/

(60

/

12

),

'mi'

)

*

15

grp,avg

(temperature_in_c

)

avg_in_c,avg

(temperature_in_c

*

5/

9

-

32

)

avg_in_f

from

sensor_datagroupby

reading_ts;Copyright©2025,Oracle

and/or

its

affiliates|6--Readings

>mean

temperatureselect

*fromsensor_readingshavingtemperature_in_c

>avg

(temperature_in_c

);Copyright©2025,Oracle

and/or

its

affiliates|78Copyright©2025,Oracle

and/or

its

affiliates|

Let's

runthem&

findout!DEMOCopyright©2025,Oracle

and/or

its

affiliates|9What’s

Newin

OracleAIDatabase

SQL

and

PL/SQLLRN2875Chris

SaxonDeveloperAdvocatechris.saxon@/sqlTimeline

of

OracleAI

Database

Release

UpdatesApply

Oct

RU

to

becomeOracleAI

Database

26ai23.8Apr2523.7

Jan

2523.26Oct

2523.6

Oct

24GAMay

2423.9Jul

2523.5Jul

24Copyright©2025,Oracle

and/or

its

affiliates|11Release

Update

23.5•

Archivingand

Unarchivingof

Gold

Images•

BINARYVector

Dimension

Format•

Backup,

Restore,

and

Relocationfor

FPP

Server•

Custom

Certificatesfor

Oracle

FPPServerAuthentication

•

Duplicated

HNSWVector

Indexes

on

RAC•

General

Improvementsfor

Oracle

FPPJobScheduler•

General

Purpose

Cluster

Configuration•

Gold

Image

Based

Out

of

Place

Patching•

JSON

Collections•

JSON_ID

SQL

Operator•

Optimized

Oracle

NativeAccessto

NVMe

Devices

Over

Fabric

•

Oracle

DBCA

Supportfor

PMEMStorage•

Oracle

DBCA

Supportfor

Standard

Edition

HighAvailability

•

Oracle

Database

Installer

Command-Line

Support•

Oracle

FPP

Local

ModeWithoutJava

Container•

Oracle

FPP

Metadata

on

External

Databases•

Oracle

Grid

Infrastructure

Installer

Command-Line

Support•

Oracle

Grid

Infrastructure

Installer

Improvements•

Single-Server

Rolling

Database

Maintenance•

Store

ImagesandTransferWorking

Copies

asZIP

Files•

Vector

Memory

PoolAutomatic

Management•

Verifying

DigitalSignatureand

Integrity

of

InstallationArchive

FilesCopyright©2025,Oracle

and/or

its

affiliates|13Mongo

API

compatibleCREATEJSONCOLLECTION

TABLE

json_dataWITH

ETAG;INSERTINTO

json_dataVALUES

(

'{

"attr"

:

"value"

}'

);Copyright©2025,Oracle

and/or

its

affiliates|14{"_id"

:

"67bc3f4f00000118919b0c61","_metadata"

:

{"etag"

:"2EE12B9B0C621891E065000000000001"

},"attr"

:

"value"SELECT*

FROM

json_data;DATA}15Copyright©2025,Oracle

and/or

its

affiliates|Release

Update

23.6•

AIVector

Search:

NewVector

Distance

Metric•

HybridVector

Index•

Partition-Local

Neighbor

PartitionVector

Index•

Persistent

Neighbor

GraphVector

Indexes•

SparseVectors•

TransactionalSupportfor

Neighbor

GraphVector

Indexes•

Vector

FormatOutputfor

Feature

ExtractionAlgorithm•

GoldenGate

ReplicationofJSON-Relational

DualityViews•

JSON-Relational

DualityViews:

Hidden

and

Generated

Fields•

JSON-Relational

DualityViews:Add

FieldsWith

CalculatedValues•

JSON

CollectionViews•

JSON

Replication•

JSON

Search

Index

Path

Subsetting•

ReplicationSupportforJSON

Collection

Tables•

CONSTRAINT_NOVALIDATE,

a

Data

Pump

Import

TRANSFORM

Parameter•

Disable

Statistics

Gathering

DuringAutonomous

Database

Import

Operations

•

Enhancementsto

Oracle

Data

Redaction•

Parallel

Index

Creation

Parametersfor

Data

Pump

Import•

Sessionless

Transactions•

XMLTYPE_STORAGE_CLAUSE,

a

Data

Pump

Import

TRANSFORM

ParameterCopyright©2025,Oracle

and/or

its

affiliates|17Whattodo

withdatabaseconnection?WriteWriteCopyright©2025,Oracle

and/or

its

affiliates|18Commit&

release

Freesresources

InconsistenttransactionsKeepopen

Consistenttransactions

ConsumesresourcesDatabase

connection

duringapp

processing?Copyright©2025,Oracle

and/or

its

affiliates|19Commit&

release

Freesresources

InconsistenttransactionsKeepopen

Consistenttransactions

ConsumesresourcesDatabase

connection

duringapp

processing?Wewanttheproswithoutthecons!Copyright©2025,Oracle

and/or

its

affiliates|20StartsessionlesstrxWriteSuspendtrxResumetrxWriteCommit/rollback

Minimalresources

ConsistenttransactionsCopyright©2025,Oracle

and/or

its

affiliates|21StartsessionlesstrxWriteSuspendtrxResumetrxWriteSuspendtrxResumetrxWriteCommit/rollbackSessionlesstransactionswith

microservicesCopyright©2025,Oracle

and/or

its

affiliates|22getTransactionIdstartTransactionsuspendTransactionImmediately

executeUpdateAndSuspendresumeTransactionControllingsessionlesstransactionsget_transaction_idstart_transactionsuspend_transactionstart_transactionOCIAttrGetOCITransStartOCITransDetachOCITransStartdbms_transactionJDBC

Oracle

Call

InterfaceGlobaltrx

IDStartSuspendResumeCopyright©2025,Oracle

and/or

its

affiliates|23Release

Update

23.7•AIVectorSearch:

Arithmetic

and

Aggregate

Operations•Additional

Flexibility

Defining

JSON-Relational

DualityViews•Change

Compatible

to

23.6.0

to

Use

NewAI

Vector

Search

Features

in

23.6

or

Later

Releases•

Cloud

Developer

Packages•

DBMS_DEVELOPER

Package•

Dimension-WiseArithmeticSupportin

PL/SQL•Foreign

Function

Interface

for

JavaScript

to

Call

PL/SQL

Code

Units•

HybridVector

IndexforJSON•

In-DatabaseAlgorithms

SupportforVECTOR

DataType

Predictors•

Included

Columnsin

Neighbor

PartitionVector

Indexes•JSON

to

Duality

Migrator:

Multi-Collection

Import

API•

JSONto

Duality

Migrator:Validation

ofSchemaand

Data•

Materialized

Expression

Columns•

Move

Data

Chunks

Between

Shardspaces•

PL/SQL

BINARYVectorSupport•

PL/SQLJACCARD

Distance

Support•

SQL

Time

Bucketing•

ShardingSupportforAI

Vector

Search•

SmallfileTablespaceShrink•Support

for

ImageTransformer

Models

with

AI

Vector

Search

Using

the

In-Database

ONNX

Runtime

•

Vector

DataType

Supportin

ExternalTablesCopyright©2025,Oracle

and/or

its

affiliates|25Origin

Stride(15

min)00:0000:1500:30dt

00:4501:0001:1501:3001:45bucket#=floor

(

(dt

–

origin

)

/

stride

)starttime=

origin

+

(

bucket#

*

stride

)endtime=

origin

+

(

(

bucket#

+

1

)

*

stride

)Counteventsin

15-minutegroupsCopyright©2025,Oracle

and/or

its

affiliates|26Value

to

bucketdate

ortimestampinterval

or

ISO8601time(First)bucket

startdate

ortimestamptime_bucket

(datetime,<stride>,

<origin>,[

start

|

end

])Counteventsin

15-minutegroupsCopyright©2025,Oracle

and/or

its

affiliates|27time_bucket

(reading_ts,interval

'15'minute,timestamp'2025-01-0100:00:00',[

start

|

end

])Counteventsin

15-minutegroupsCopyright©2025,Oracle

and/or

its

affiliates|28altertablesensor_readings

add

(fifteen_minute_startdateas

(…

)virtual,fifteen_minute_enddateas

(…

)virtual

)Calculateon

readCopyright©2025,Oracle

and/or

its

affiliates|29altertablesensor_readings

add

(fifteen_minute_startdateas

(…

)materialized,fifteen_minute_enddateas

(…

)materialized

)CalculateonwriteCopyright©2025,Oracle

and/or

its

affiliates|30virtualCalculateonreadMetadataonlyWrite

often,read

rarely11.1materializedCalculate

on

writeStoresresultWrite

once,read

often23.7Virtualvs

materialized

expressioncolumnsCopyright©2025,Oracle

and/or

its

affiliates|31selectdbms_metadata.get_ddl

('TABLE',

'SENSOR_READINGS');CREATETABLE

…Gettable

properties–

dbms_metadata

Slow

Parse

DDLCopyright©2025,Oracle

and/or

its

affiliates|32Gettable

properties–

dbms_developer(23.7)select

dbms_developer.get_metadata('SENSOR_READINGS');

Fast

JSONexpressions{

"objectType"

:

"TABLE","objectInfo":{"name":"SENSOR_READINGS",

…Backported

to

19.28Copyright©2025,Oracle

and/or

its

affiliates|33Release

Update

23.8•

Additional

Predicate

Supportwith

HybridVector

Search•

Client

Supportfor

SPARSEVectors•

DynamicStatisticsfor

PL/SQL

Functions•

ElasticVector

Memory

Management•

Included

Columns

SupportforJSON,

BLOBand

CLOB

DataTypes•

JSONType

Modifier

Enhancements:

Data

SizeandArray

Specifications

•

JSONto

Duality

Migrator:

Hints

Configuration

Field•

JSON_TEXTCONTAINS

andJSON_EXISTS

Supportin

DBMS_HYBRID_VECTOR.SEARCH

•

Oracle

Database

Cloud

Backup

ModuleforAzure

BlobStorage•

Restricted

Execution

Contextsfor

In-Database

JavaScript•

SparseVectorSupport

in

PL/SQL•

User-DefinedVector

Distance

FunctionsCopyright©2025,Oracle

and/or

its

affiliates|35select

*fromtable_function

();Copyright©2025,Oracle

and/or

its

affiliates|36select

*fromtable_function

();

Fixed

rowestimate--------------------------------------------------|

0

|SELECT

STATEMENT||||

1

|

SORTAGGREGATE

|

|

1

||2

|

COLLECTION

...

|

TABLE_FUNCTION

|

8168

||

Id

|Operation

|

Name

|

Rows

|Copyright©2025,Oracle

and/or

its

affiliates|37dbms_stats.set_global_plsql_prefs

('dynamic_stats',

'ON');SamplePL/SQL

functioncalls38Copyright©2025,Oracle

and/or

its

affiliates|select

*from

table_function

();

Dynamic

rowestimate--------------------------------------------------|

0

|SELECT

STATEMENT||||

1

|

SORTAGGREGATE

|

|

1

||2

|

COLLECTION

...

|

TABLE_FUNCTION

|

200

||

Id

|Operation

|

Name

|

Rows

|Copyright©2025,Oracle

and/or

its

affiliates|39CHOOSE

(default)where

=>ONfrom

=>OFFOFFFasterparsing

WorseestimatesONSlowerparsingBetter

estimatesPL/SQL

Dynamic

StatsCopyright©2025,Oracle

and/or

its

affiliates|40Release

Update

23.9•

Compile-TimeSyntax

CheckingforJavaScript

Procedures

and

Functions•

EXECUTE

Privilege

onJAVASCRIPT

Not

RequiredAnymore•

GROUP

BYALL•

Generate

and

Test

UUID

Using

SQL•

IVF

Index

Online

Reorganization•

JSONto

Duality

Migrator:Schema

Inference

usingJSON

Schema•

Multi-FactorAuthenticationfor

DatabaseAuthentication•

Multilingual

Engine

Supports

Database-Resident

Connection

Pools•

Non-Positional

INSERT

INTO

SET

Clause•

Oracle

UpdateAdvisor•

Oracle

UpdateAdvisor

Supportwith

Oracle

FPP•

Partition

Maintenance

Operations

and

Direct

Loadwith

Global

IVFand

HNSW

IndexesCopyright©2025,Oracle

and/or

its

affiliates|42insertinto

…

(

c1,

c2,

c3,

…

)values

(

'v3',

'v2',

'v1',…

)

Mismatchedinsert

into

…

(

c2,

c1,

…

)

columns

&

valuesc1,

c2,…,

count

(*)…by

c1,

c2,…;select

from

groupDuplicate

columnlistCopyright©2025,Oracle

and/or

its

affiliates|43Non-positional

INSERTsinsert

into…set

c1=

'v1',

c2

=

'v2',

c3

=

'v3',

…UPDATE-like

SETsyntaxCopyright©2025,Oracle

and/or

its

affiliates|44Non-positional

INSERTsinsert

into…set

c1=

'v1',

c2

=

'v2',

c3

=insert

into…by

name

selectc1,

c2,…,

count

(*)

c4from…group

by

all;

'v3',

…Matchto

target

onname/aliasUnaggregatedexpressionsCopyright©2025,Oracle

and/or

its

affiliates|45Random

UUID

generatorselectuuid

(4)version_4_raw_uuid,raw_to_uuid

(uuid

()

)version_4_char_uuid;09F4558F24EA4FF5BFB5E78DEBC4A0484855965c-8a99-4fa7-bf9e-f617c31a0162VERSION_4_RAW_UUIDVERSION_4_CHAR_UUIDCopyright©2025,Oracle

and/or

its

affiliates|46Release

Update

23.26•AIVector

Search:JDBC

Support

CompletionforSPARSEVectors

with

a

Binary

Format•Automaticcachingof

externaltablesin

object

storage•Enhancementsto

GraphQL

Syntaxfor

DualityView

Creation•GraphQL

Schema

IntrospectionAPI•GraphQL

Table

Functionfor

SQL•Heat

Map

Retention

Time•JSON

Outputforthe

Data

Guard

Command-Line•JSON

Replication•MaterializedView

Query

Rewritewith

CursorSharinginthe

Presence

of

BindVariables•MemorySpeed

Columnar•Move

Data

Chunks

Between

Shardspaces•PrivateAI

ContainerforVector

Embedding

Generation•Restrict

Switchover

and

Failovertoa

List

of

Databases•SQL

Objects

and

Collectionsin

MLEJavaScript•Sparsevectorsupportin

MLE

JavaScript•SupportforSQL

Qualify

Clause•Telemetry

StreamingCopyright©2025,Oracle