SQLDaily
sqldaily.bsky.social
SQLDaily
@sqldaily.bsky.social
300 followers 3 following 220 posts
Daily Oracle SQL tips from the Oracle Developer Advocates for SQL
Posts Media Videos Starter Packs
Query Apache Iceberg tables from Oracle AI Database using external tables

To do this, set the

ACCESS PARAMETERS (
com.oracle.bigdata.fileformat=parquet
com.oracle.bigdata.credential.name=...
com.oracle.bigdata.access_protocol=iceberg
)

Bryan Grenn demos
Oracle DB 23ai supports external Apache Iceberg tables
Oracle database 23ai supports External Tables defined on Apache Iceberg objects. This post goes through how to configure an external table on Iceberg.
buff.ly
Oracle Database 23ai becomes Oracle AI Database 26ai when you apply the Oct 2025 Release Update

This adds a GRAPHQL table function that

Accepts #GraphQL queries
Maps fields to database tables and columns
Returns JSON

e.g.

SELECT * FROM GRAPHQL ( ' tab { col1 col2 } ' )
Use Oracle Data Redaction to mask sensitive data in query results

Kajal Singh runs through the enhancements added to this in 23.6 for

Mathematical & set functions
Sorting & grouping
Views with expressions
Virtual columns in function-based indexes
buff.ly
Apply the Oct 2025 Release Update and Oracle Database 23ai becomes Oracle AI Database 26ai

This adds the QUALIFY clause

QUALIFY filters window functions after grouping
Like the HAVING clause does for aggregate functions

SELECT ... FROM ...
QUALIFY fn OVER ( ... ) > ...
Instead of removing rows via DELETE, many applications mark rows as deleted by setting a flag

With Oracle #SQL, you can partition on this deleted flag to separate active and inactive data

But there are nuances to this - @connormcd.bsky.social covers the details
Soft Deletes via Partitioning
Community friend Gwen Shapira tweeted this the other day This is a common design pattern in Oracle databases (and many others) use a DELETED_FLAG style column to handle “soft deletes.” …
buff.ly
Got unaggregated columns you want to include in the SELECT list, but not the GROUP BY?

You can use ANY_VALUE, e.g.:

SELECT c1, ANY_VALUE ( c2 ), COUNT ...
FROM ...
GROUP BY c1

This returns the value from a random row (it's optimized to return the first) => non-deterministic
Database triggers often contain auditing or other security logic

So stopping bad actors from disabling triggers is vital

@petefinnigan.bsky.social shows how in Oracle Database:

Create an AFTER DDL ON DATABASE trigger
Check ALTER TRIGGER statements for DISABLE
Can we Detect Disable Trigger in the Oracle Database
Pete Finnigan's Oracle security weblog
buff.ly
Define which prior rows to include in window functions with

ORDER BY ... [ frame ] ... PRECEDING

The frame states which sort keys to include (UNBOUNDED = all)

ROWS - Strict row count
RANGE - Logical value offset; only valid for numbers & datetimes
GROUPS - Unique values count
Use Heap Maps in Oracle Database to see:

Which tables are actively used vs. rarely/never used
Whether data is being read by full table scans or index lookups
Opportunities for compression, archiving, or other ILM policies

Matt DeMarco shows how to use it
Using Oracle Heat Map to Understand Table Access Patterns
Oracle Database includes a powerful feature called Heat Map, which tracks how segments are accessed and helps guide Information Lifecycle Management (ILM) decisions. This post walks through a pract…
buff.ly
Oracle Database 21c enhanced FOR LOOPs in PL/SQL to enable you to use

Custom increments (BY)

Lists of values to loop through

Conditions to skip the loop body (WHEN)

Expressions to set the next value (REPEAT WHILE)

Mutable iterands to set their value inside the loop (MUTABLE)
Are you making any of these common #SQL mistakes?

Forgetting about NULL
Processing data in Java memory
Using JDBC Pagination to paginate large results
Using aggregate instead of window functions

@lukaseder.bsky.social explains what to do instead & lists 7 more
10 Common Mistakes Java Developers Make when Writing SQL
This article is part of a series. You might also like: 10 More Common Mistakes Java Developers Make when Writing SQLYet Another 10 Common Mistakes Java Developers Make When Writing SQL Java develop…
buff.ly
Processing one row at a time leads to lots of round-trips to the #database

This is SLOOOOOOOW

It's faster to batch records and send many in one call

How much faster?

@pdevisser.bsky.social tested with Python and batching inserted 150x more rows!
oracle23ai and python - eliminate RoundTrips.
TL;DR: Searching for RoundTrips to eliminate between python and Oracle. In this case, we "collect" the individual inserts to do them in a fe...
buff.ly
You can subtract one year from a date in Oracle #SQL with either

ADD_MONTHS ( dt, -12 )
- INTERVAL '1' YEAR

But take care - these handle 29th Feb differently:

ADD_MONTHS => 29th Feb -> 28th Feb in previous year
INTERVAL => 29th Feb -> 29th Feb in previous year => error!
The Gregorian calendar was adopted in 1582

In this year 4 Oct was followed by 15 Oct

The dates between (5-14 Oct) don't exist in Oracle Database

This can lead to surprising results, e.g.

SELECT DATE'1582-10-10' => 15 Oct

@sdstuber.bsky.social investigates
Looking Through Holes in Time - Sean D. Stuber
While testing some date/time functions today I came across an interesting quirk in Oracle's Gregorian calendar and different date representations. In the Gregorian calendar, the day after October 4,…
buff.ly
Index scalar values in #JSON arrays stored in Oracle Database with

CREATE MULTIVALUE INDEX ... ON t ( t.json_data.array. )

The optimizer can use these indexes when searching the array with

JSON_EXISTS ( t.json_data, ' $.array? ( @. == ... ) '
To cache the results of PL/SQL function calls, declare them

DETERMINISTIC => cache within a statement
RESULT_CACHE => cache across sessions

Ensure the functions have few unique inputs & are deterministic before doing this!

@antikyte.bsky.social explores
DETERMINISTIC and RESULT_CACHE PL/SQL functions
Recent events here in the UK have once again emphasised the value of being careful with your “cache”. To this end, I’m going to take a look at how PL/SQL functions defined using t…
buff.ly
Filter tables on their partition key to get partition pruning in Oracle #SQL

=> your query is more efficient

But with reference partitioning, this key is in the parent table!

@go-faster.co.uk shows how filtering these by foreign key enables pruning
Partition Pruning/Elimination on Reference Partitioned Tables
I discussed locally partitioning the unique index on a reference partitioned table in a previous blog .  Having implemented it, I wanted to ...
buff.ly
Get values from the initial/nth/final row with

FIRST_VALUE => inital
NTH_VALUE => Nth
LAST_VALUE => final

The default window ends at the current value =>

NTH_VALUE ( v, N ) is null for rows before N
LAST_VALUE gives the final value with the same sort key, not the very last row
Oracle Database has added many #SQL features in RUs like

Non-positional INSERT
GROUP BY ALL
TIME_BUCKET function

But how do you find these?

In the New Features Guides!

Some need you to up COMPATIBLE - @mikedietrichde.com the impact this has

buff.ly/5i5aPnu
Create blockchain tables in Oracle Database with

CREATE BLOCKCHAIN TABLE ... ( ... )
NO DROP UNTIL n DAYS IDLE
NO DELETE UNTIL n DAYS AFTER INSERT
HASHING USING SHA2_512 VERSION "v1"

These

Allow INSERT
Disallow UPDATE
Disallow DELETE/DROP until you wait N days as defined
The NVL function in Oracle #SQL returns

The 1st argument if it's non-null
Otherwise the 2nd argument

Use it like so

WHERE col = NVL ( :var, col )

And the plan can use an index if :var is non-null & a full table scan if it is

@monikalewandowska.bsky.social demos
buff.ly
Combine rows by value in #SQL with

GROUP BY col

And this returns one row for each value in col

You can then use aggregate functions to get totals for the rows in each group, e.g.

COUNT - number of rows
AVG - numeric mean
MIN/MAX - smallest/largest
LISTAGG - list of values