SQL Daily

Want to learn a new skill in 2024? Make it #SQL! Oracle Dev Gym has a series of FREE classes to help you do this Start with Databases for Developers: Foundations Join at devgym.oracle.com/pls/apex/dg/cl…

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 mattmulvaney.hashnode.dev/how-am-i-suppo…

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 @Andrej_SQL shows how to improve row estimates with Extended stats Virtual columns blog.sqlora.com/en/fix-optimiz…

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 finds RAW is faster querying a 100M row table enesi.no/2025/10/uuids-…

Oracle AI Database has several string similarity functions SOUNDEX PHONIC_ENCODE - double metaphone FUZZY_MATCH - several algorithms, including Jaro-Winkler & Levenshtein @brendantierney compares how they encode spelling variations of Brendan oralytics.com/2025/10/15/com…

Edition-based redefinition enables zero-downtime app upgrades for Oracle AI Database @DBoriented 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 db-oriented.com/2024/12/30/ebr…

Database deployments can fail But rolling back schema changes is tricky - so plan it @jasminfluri runs through options Versioned Migrations with Reversible Scripts Backups before Changes Immutable Migrations & Forward Fixes Restore Points & Flashbacks medium.com/@jasminfluri/d…

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 @phsalvisberg explores salvis.com/blog/2025/10/1…

Daylight saving is ending across Europe and North America But they change on different dates! To see when the continents are an hour closer, Kim Berg Hansen built a pattern-matching query comparing the TIMEZONE_HOUR for dates across the year kibeha.dk/2025/10/dst-di…

Query Apache Iceberg tables from Oracle AI Database using external tables ACCESS PARAMETERS ( com.oracle.bigdata.fileformat=parquet com.oracle.bigdata.credential.name=... .bigdata.access_protocol=iceberg ) Bryan Grenn demos bryangrenn.blogspot.com/2025/09/db23ai…

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 blogs.oracle.com/database/post/…

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 Mike Smithers demos mikesmithers.wordpress.com/2025/10/02/get…

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 - @connor_mc_d covers the details connor-mcdonald.com/2025/10/03/sof…

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 shows how in Oracle Database: Create an AFTER DDL ON DATABASE trigger Check ALTER TRIGGER statements for DISABLE petefinnigan.com/weblog/archive…

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 oramatt.com/2025/09/30/usi…

Convert #JSON to rows & columns with JSON_Table But if there are errors extracting a field, by default, Oracle Database sets its value to null @MartinDBA shows how to force an error instead in 23ai with ALTER SESSION SET JSON_BEHAVIOR="ON_ERROR:ERROR" martincarstenbach.com/2025/08/07/why…

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)