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…

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 has built scripts to show misestimates in Oracle plans tanelpoder.com/posts/xb-sql-s…

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 @GeraldVenzl geraldonit.com/whats-new-for-…

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 @RebellionRider demos youtube.com/watch?v=Pg4ox7…

How do the data types of join columns affect query performance? Dani Schnider 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 danischnider.wordpress.com/2018/12/11/dat…

Triggered by traumatic DML errors? Mike Smithers 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 mikesmithers.wordpress.com/2025/10/15/fix…

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

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! @connor_mc_d shows how to hide them with contexts connor-mcdonald.com/2025/10/15/sec…

Want to find the fastest-growing tables or LOBs in Oracle AI Database? @viniciusdba 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 github.com/vinidba/dba_sc…

Poorly written #SQL can lead to slow, hard-to-maintain queries Jordan Goodman covers common anti-patterns you should avoid, including Case expressions instead of tables Functions on indexed columns Overusing DISTINCT to “fix” duplicates datamethods.substack.com/p/sql-anti-pat…

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) blogs.oracle.com/cloud-infrastr…

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 ( sal ) KEEP ( DENSE_RANK FIRST ORDER BY hire ) => Get the lowest sal for the first hire @quanwen_zhao explores quanwenzhao.wordpress.com/2025/10/15/ora…

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…