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…

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)

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 Lukas Eder explains what to do instead & lists 7 more blog.jooq.org/10-common-mist…

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 tested with Python and batching inserted 150x more rows! simpleoracledba.blogspot.com/2025/07/oracle…

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 investigates seanstuber.com/2018/12/18/loo…

Index scalar values in #JSON arrays stored in Oracle Database with CREATE MULTIVALUE INDEX ... ON t ( t.json_data.array.<type> ) The optimizer can use these indexes when searching the array with JSON_EXISTS ( t.json_data, ' $.array? ( @.<type> == ... ) '

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 explores mikesmithers.wordpress.com/2022/10/16/det…

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! @davidmkurtz shows how filtering these by foreign key enables pruning blog.go-faster.co.uk/2025/09/refpar…

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 the impact this has mikedietrichde.com/2025/03/24/how…