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

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

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.bsky.social shows how to force an error instead in 23ai with ALTER SESSION SET JSON_BEHAVIOR="ON_ERROR:ERROR"

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 @lukaseder.bsky.social explains what to do instead & lists 7 more

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.bsky.social tested with Python and batching inserted 150x more rows!

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.bsky.social investigates

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.bsky.social explores

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! @go-faster.co.uk shows how filtering these by foreign key enables pruning

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.com the impact this has buff.ly/5i5aPnu

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 in Oracle Database with GraphQL syntax CREATE JSON DUALITY VIEW ... AS student @insert { -- student table, allowing inserts _id : id -- student.id ... } @anders-swanson.bsky.social gets into the details andersswanson.dev buff.ly/fhqMxZt

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 @monikalewandowska.bsky.social demos

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

With Schema Annotations in Oracle Database, you can document your objects by adding key-value metadata to Tables Columns Indexes (Materialized) Views With the clause ANNOTATIONS ( <key1> <value1>, <key2> <value2>, ... ) Added in release 23ai, this has been backported to 19.28

#SQL injections are scary! If your app is vulnerable, hackers can bypass your security and access your private data Network Chuck shows how dangerous this can be buff.ly/cbC7PD3 Keep your data safe Use parameterized queries!

With the Oracle Job Scheduler, you can run OS scripts from the #database If you're stuck, @connormcd.bsky.social has built a troubleshooting guide to help 1 Arguments not command line 2 Config file 3 Permissions 4 Who runs your script 5 You start with “nothing”

External tables are a great way to read text files into Oracle Database with #SQL But what if you have fields longer than 4,000 characters? @antikyte.bsky.social covers Mapping CHAR > 4000 in the ACCESS PARAMETERS to CLOB Increasing the READSIZE Using the big data driver

Take care when filtering the INNER table in OUTER JOINs e.g. the right table in a LEFT OUTER JOIN Do this in the WHERE clause => it's an INNER JOIN To preserve the OUTER JOIN, filter the inner table in the ON clause The pic shows outer joins ON number col with inner filters

With Oracle SQLcl 25.2 you can start MCP servers These enable you to talk to your Oracle Database via AI agents @thatjeffsmith.com has compiled a FAQ to help you understand it, including Does this work with 11g or or 12c? Probably How to make it safe buff.ly/J34VHtb

Convert rows to a comma-separated list of de-duplicated values in Oracle #SQL with LISTAGG ( DISTINCT val, ', ' ) WITHIN GROUP ( ORDER BY ... ) LISTAGG was introduced in 11g Release 2 The DISTINCT clause was added in 19c

The #SQL hash join algorithm 1 Builds a hash table on the smaller table's join column(s) 2 Reads the larger table, checking if its join columns hash to the table built in 1 This grows linearly with the size of the tables - O ( n + m ) @vladmihalcea.bsky.social explains

Oracle Database 23.9 added non-positional inserts This assigns values to columns in the SET clause (like UPDATE) INSERT INTO employees SET employee_id = 210, first_name = 'Gerald', ... @gvenzl.bsky.social dives into the details and gives the backstory in

Get the top-N rows in Oracle #SQL with FETCH FIRST n ROWS ONLY You can include all rows with the same sort value as the Nth with FETCH FIRST n ROWS WITH TIES Or get a fraction of the rows with FETCH FIRST n PERCENT ROWS [ ONLY | WITH TIES ]

Automatically put all unaggregated columns in the SELECT in GROUP BY from Oracle Database 23.9 with GROUP BY ALL @danischnider.bsky.social shows how he uses it to find duplicate rows with SELECT t.*, COUNT (*) FROM t GROUP BY ALL HAVING COUNT (*) > 1

Get values from the previous or next row in #SQL with <fn> ( val ) OVER ( ORDER BY ... ) Where <fn> is either LAG - previous LEAD - next By default these look one row forward/back Use the second parameter to get the value from N rows before/after the current