SQL Daily @sqldaily

Daily Oracle SQL tips from the Oracle Developer Advocates for SQL blogs.oracle.com/sql Joined January 2015

Tweets

3K
Followers

26K
Following

4
Likes

122

SQL Daily @sqldaily

7 hours ago

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…

0 6 8 489 3

SQL Daily @sqldaily

a day ago

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

0 12 26 1K 8

Download Image

SQL Daily @sqldaily

4 days ago

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…

0 6 18 978 6

Download Image

SQL Daily @sqldaily

5 days ago

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

0 13 30 1K 9

Download Image

SQL Daily @sqldaily

6 days ago

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…

0 8 21 1K 7

SQL Daily @sqldaily

a week ago

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…

1 8 22 1K 4

SQL Daily @sqldaily

a week ago

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

0 7 27 1K 8

Download Image

SQL Daily @sqldaily

2 weeks ago

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

0 7 13 1K 3

Download Image

SQL Daily @sqldaily

2 weeks ago

#SQL injections are scary! If your app is vulnerable, hackers can bypass your security and access your private data @NetworkChuck shows how dangerous this can be youtube.com/watch?v=2OPVVi… Keep your data safe Use parameterized queries!

0 6 21 2K 10

SQL Daily @sqldaily

2 weeks ago

With the Oracle Job Scheduler, you can run OS scripts from the #database If you're stuck, @connor_mc_d 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” connor-mcdonald.com/2025/09/01/tro…

0 4 13 912 8

SQL Daily @sqldaily

2 weeks ago

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 covers Mapping CHAR in the ACCESS PARAMETERS to CLOB Increasing the READSIZE Using the big data driver mikesmithers.wordpress.com/2025/08/20/ora…

0 6 25 1K 7

SQL Daily @sqldaily

2 weeks ago

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

1 12 28 1K 13

Download Image

SQL Daily @sqldaily

3 weeks ago

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

0 6 13 1K 6

SQL Daily @sqldaily

3 weeks ago

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

1 7 28 2K 16

Download Image

SQL Daily @sqldaily

3 weeks ago

The #SQL hash join algorithm 1 Builds a hash table on the smaller table's join columns 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) @vlad_mihalcea explains vladmihalcea.com/hash-join-algo…

0 8 22 2K 14

SQL Daily @sqldaily

3 weeks ago

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', ... @GeraldVenzl dives into the details and gives the backstory in geraldonit.com/non-positional…

1 11 31 2K 12

SQL Daily @sqldaily

3 weeks ago

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 ]

1 10 34 1K 15

Download Video

SQL Daily @sqldaily

4 weeks ago

Automatically put all unaggregated columns in the SELECT in GROUP BY from Oracle Database 23.9 with GROUP BY ALL Dani Schnider shows how he uses it to find duplicate rows with SELECT t.*, COUNT (*) FROM t GROUP BY ALL HAVING COUNT (*) > 1 danischnider.wordpress.com/2025/08/05/ora…

0 6 26 1K 10

SQL Daily @sqldaily

4 weeks ago

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

0 8 25 1K 11

Download Image

SQL Daily @sqldaily

4 weeks ago

Virtual columns in Oracle Database have long been able to define calculated columns These run the expression on read Store the expression on write from release 23.7 with <col> <data_type> AS ( ... ) MATERIALIZED @GeraldVenzl takes a closer look geraldonit.com/oracle-databas…