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…

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…

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

Define #JSON Relational Duality Views with GraphQL syntax CREATE JSON DUALITY VIEW ... AS student @insert { -- student table, allowing inserts _id : id -- student.id ... } @anders__swanson gets into the details andersswanson.dev andersswanson.dev/2025/08/14/gra…

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 @monika_mitura demos how2ora-en.blogspot.com/2025/09/the-ma…

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