We're taking a break from the #SQL tips over the festive period UPDATE sql_tips SET next_tip_on = DATE'2026-01-05' We hope you have a wonderful Christmastime and a fantastic New Year Here's a SQL snowflake generator to celebrate buff.ly/yjbOwiK See you in 2026!

Using bind variables is critical for secure #SQL But they can also give large performance benefits @martindba.bsky.social compares using binds vs literals in a simple Java app The result? Binds are 4.5x faster Use nearly 1000x less database memory

Oracle AI Database uses the Object-Relational Model to store spatial data @anders-swanson.bsky.social shows how to load spatial data and use it to Calculate the area of a geometry Find the distance between two points Find geometries close to a given shape

Want a visual guide to the structure of Oracle AI Database's Processes Memory Objects Files Check out the Technical Architecture diagrams in the docs Click through the interactive diagrams to see how the components relate Or view them all at buff.ly/Mo3goIy

Find sequences of events across rows with #SQL pattern matching e.g., W patterns where prices fall then rise twice With the MATCH_RECOGNIZE clause: MEASURES - values to return PATTERN - the sequence as a regex over variables DEFINE - the variables using SQL conditions

SQL Case Files Learn #SQL while tackling detective investigations Write queries to learn about suspects, uncover evidence, and close the cases Play at buff.ly/ANG0hA4

Schema annotations in Oracle AI Database enable you to add key-value metadata on Tables Views Indexes Domains These help document your objects @martindba.bsky.social shows how to add them to view columns with ALTER VIEW ... MODIFY ( <col> ANNOTATIONS ( ... ) )

Real-time SQL Plan Management in Oracle AI Database can Detect plan changes in #SQL queries Identify which of these plans is best Ensure future runs use the best plan Prashant Dixit shows it in action

You can run code in the BEGIN/END block of a PL/SQL package CREATE PACKAGE BODY ... AS ... BEGIN do_stuff(); END; Take care: this only runs when initializing the package @petefinnigan.bsky.social shows how security checks here can fail

Access attributes of JSON data stored in Oracle AI Database Simple dot-notation - <alias>.<column>.<path>.<to><attr> JSON_VALUE ( column, '$.<path>.<to><attr>' ) Dot-notation returns NULL if it can't process the attribute, JSON_VALUE gives full control over the behaviour #SQL

Aggregate functions in #SQL report totals across many rows Common ones are COUNT, SUM, AVG, MAX, MIN Baraa Khatib Salkini explains how these work and how to use them to answer questions like How many orders are there? What's their total sales value?

AI agents can help you build schemas and create test data @chrishoina.bsky.social shares an example prompt to store data about cars, trucks, motorcycles and their manufacturers Then shows you how to use it with SQLcl MCP Server and Cline to make the tables

The wait is (nearly) over! Oracle AI Database 26ai EE for Linux x86-64 will be released on premises in the next RU (23.26.1), due January 2026 Read full details at blogs.oracle.com/database/ora...

Finally … Oracle AI Database 26ai is going to be available on non-Oracle hardware Many of you have read my blog post from October during Oracle AI World in Las Vegas about Oracle AI Database 26ai replacing Oracle Database 23ai. But the main question remained: When is it available on-prem on…

Mouhamadou Diaw examines the differences between functions & procedures in PL/SQL Functions Return a value Can be called in #SQL - DML must be in an autonomous transaction Procedures Use OUT parameters to return values Can't be called in SQL directly

Tracking changes to values over time is a common requirement For example, recording price fluctuations for goods Alexey Makhotkin steps through designing tables to store these changes, proposing template tables with Anchor Value Valid from/to dates

Change the partitioning method for an Oracle table with: ALTER TABLE ... MODIFY PARTITION BY ... This can change The partition method (RANGE/LIST/HASH) The partition columns Add/remove subpartitioning Add the ONLINE clause to allow writes to continue while changing

Like #SQL? Like puzzles? Try #SQuizL A free, daily guess-the-SQL statement quiz You have up to six attempts to complete the statement on the top left using the fragments below There's a new statement to guess at every day at midnight UTC Play now at buff.ly/Myg8265

You can run in-database AI inference on Oracle Active Data Guard @ludovico.bsky.social shows you how to do this to find images with semantic search Loading an ONNX model & pictures Creating PL/SQL to generate embeddings Running this proc and searches on the standby

When tuning #SQL, focus on the estimates for how many rows each operation will return If these are off by an order of magnitude or more, there's a good chance a different plan is faster @tanelpoder.com has built scripts to show misestimates in Oracle plans

Oracle AI Database 23.26.0 developer features include QUALIFY clause GraphQL Table Function for SQL SQL Objects and Collections in MLE JavaScript PL/SQL RESETTABLE clause to avoid ORA-04068: existing state of packages has been discarded @gvenzl.bsky.social

Define schema-only accounts in Oracle AI Database with CREATE USER ... NO AUTHENTICATION => you can't connect as this user To run scripts as-if you're a schema-only account, use a proxy user ALTER USER schema_only GRANT CONNECT THROUGH proxy CONN proxy[schema_only]/<pwd>

Want to find the Nth highest value in a table? e.g. the 3rd highest salary/department? Number the values with DENSE_RANK () OVER ( PARTITION BY ... ORDER BY ... DESC ) Then filter where the output of this is 3 Manish Sharma demos

How do the data types of join columns affect query performance? @danischnider.bsky.social compares using Hashes as CHAR(32) and RAW(16) Sequences as NUMBER And finds in Oracle #SQL NUMBER is faster than RAW RAW is faster than CHAR

Triggered by traumatic DML errors? @antikyte.bsky.social shows how to recover in Oracle AI Database when you accidentally remove data with FLASHBACK TABLE ... TO BEFORE DROP => restore a dropped table TIMESTAMP => revert to a point in time

Triggers are a common way to audit DML changes But writing these can be fiddly To help you with this, @zorantica.bsky.social has built Data JAM for Oracle (Journaling & Auditing Module) An #orclAPEX app to automate audt tables and triggers Get it at

In PL/SQL EXCEPTION WHEN OTHERS THEN Catches all errors Filling your code with this without reraising the error hides unexpected issues So the compiler raises PLW-6009 to warn you If you reraise in custom logging procedures use PRAGMA SUPPRESSES_WARNING_6009 To skip this warning

Get free access to Oracle AI Database at freesql.com This browser-based tool enables you to run and share #SQL statements Even better, you can now connect to your schema from clients on your machine using SQL*Net So you can run SQL from your favourite IDE

Oracle Virtual Private Database enables you to transparently add WHERE clauses to restrict what users can see See what was added with DBMS_UTILITY.EXPAND_SQL_TEXT But this may expose sensitive values! @connormcd.bsky.social shows how to hide them with contexts

Want to find the fastest-growing tables or LOBs in Oracle AI Database? @viniciusdba.com.br has built a script that analyzes AWR data to show The 20 segments with the largest total size increases A breakdown of the increase per month for each Get it at

Poorly written #SQL can lead to slow, hard-to-maintain queries @jordangoodman.bsky.social covers common anti-patterns you should avoid, including Case expressions instead of tables Functions on indexed columns Overusing DISTINCT to “fix” duplicates

Macros in Oracle #SQL enable you to define expression templates as text At parse time the database merges these with the parent query, replacing parameters with the actual values passed Macros come in two types Scalar - use in SELECT, WHERE, ORDER BY, etc. Table - use in FROM

Oracle wins the multilingual text-to-SQL Archer Challenge by combining planning and #SQL agents Understanding Archer queries requires Arithmetic reasoning Commonsense inference Hypotheticals and counterfactuals Bilingual execution (English + Chinese)

The KEEP clause - aka FIRST/LAST functions - in Oracle #SQL enables you to get the min/max row for one column sorted by another MIN ( salary ) KEEP ( DENSE_RANK FIRST ORDER BY hire ) => Get the lowest salary for the first hire @quanwenzhao.bsky.social explores it & alternatives

Create a flashback archive in Oracle AI Database to record all changes to a table CREATE FLASHBACK ARCHIVE ... RETENTION ... ALTER TABLE ... FLASHBACK ARCHIVE Then view the changes with SELECT * FROM ... VERSIONS BETWEEN ... @Matt_Mulvaney demos

Storing UUIDs as RAW values is more efficient than strings But RAW can lead to implicit conversions => poor row estimates If you're stuck with string comparisons @andrejsql.bsky.social shows how to improve row estimates with Extended stats Virtual columns

You can generate UUIDs/GUIDs in Oracle AI Database with SYS_GUID since "forever", sequential based on host & process UUID since 23.7, random But should you store these as RAW(16) or CHAR(32)? @oyvindisene.bsky.social finds RAW is faster when querying a 100M row table

Oracle AI Database has several string similarity functions SOUNDEX PHONIC_ENCODE - double metaphone FUZZY_MATCH - several algorithms, including Jaro-Winkler & Levenshtein @brendan-tierney.bsky.social compares how they encode spelling variations of Brendan

Edition-based redefinition enables zero-downtime app upgrades for Oracle AI Database @nakdimon.bsky.social discusses why it's worth using when offline upgrades are OK: Any time or length upgrades Separate database & app upgrades Flexible release of new versions

Database deployments can fail But rolling back schema changes is tricky - so plan them @jasminfluri.bsky.social runs through options Versioned Migrations with Reversible Scripts Backups before Changes Immutable Migrations & Forward Fixes Restore Points & Flashbacks

SYSDATE in Oracle AI Database is evaluated once per Statement in #SQL Call in PL/SQL Wrap SYSDATE in a DETERMINISTIC function to trick the compiler into evaluating it once/statement But beware: this can lead to unwanted behaviour @salvis.com explores

Daylight saving is ending across Europe and North America But they change on different dates! To see when the continents are an hour closer, @kibeha.dk built a pattern-matching query comparing the TIMEZONE_HOUR for dates across the year

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 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

Create new list partitions when you insert new partition key values in Oracle #SQL by defining the table PARTITION BY LIST ( ... ) AUTOMATIC Or convert existing tables with ALTER TABLE ... SET PARTITIONING AUTOMATIC @antikyte.bsky.social demos

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

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