You can join tables with common column names with
t1 JOIN t2 USING ( col )
But this leads to a trap!
With
t1 JOIN t2 USING ( c1 ) JOIN t3 USING ( c2 )
With c2 in just t2 and t3 this works
But add c2 to t1 in the future it breaks 😖
@lukaseder.bsky.social demos
Assertions in Oracle AI Database enable you to define cross-table constraints
@sdstuber.bsky.social shows how he used them to enforce the rule:
You can only invite nodes for listeners with registration checking enabled
For his Connection Manager config
Define JavaScript functions and modules in the database in Oracle AI Database 26ai
CREATE FUNCTION ... MLE LANGUAGE JAVASCRIPT ...
CREATE MLE MODULE ... LANGUAGE JAVASCRIPT ...
To expose JavaScript you can call in #SQL
Try these out on FreeSQL buff.ly/wRIX0Jx
#SQL has a powerful property:
Learn it once
Use it for 30+ years
Fayner Brack discusses how textbook examples from 1995 still work today
buff.ly/5EwtVJe
Oracle AI Database 23.26.1 added partition by expression support
=> you can use an expression directly instead of a column
e.g. by email top-level domains with
PARTITION BY LIST ( REGEXP_SUBSTR ( email_address, '[^.]+$' ) )
@danischnider.bsky.social demos
You can number rows in #SQL with
ROW_NUMBER => unique, consecutive numbers
DENSE_RANK => ties have same rank, consecutive ranks
RANK => ties have same rank, rank after ties = row number => gaps
Jess Ramos shows these in action and lists common use cases
buff.ly/pHiiY53
A common defence for storing data as flat tables is:
Joins are expensive
But is that really true?
Thomas Kejser ran performance tests and found
Joins are CHEAP when compared with the alternatives
This can be true even if you read the whole table
buff.ly/AFykOZF
You can initialize PL/SQL arrays with FOR loops itype constructors from 21c
array := arr_type ( FOR i IN ... )
These can loop through
Values => IN x .. y
A cursor => IN ( SELECT ... )
Use the optional INDEX clause to set custom indices
Try these at buff.ly/J1aH20F
Brand new to Oracle AI Database?
@anders-swanson.bsky.social shows you how to get started by
Running a Free database on Docker
Get must have dev tools SQL Developer extension for VS Code & SQLcl command line interface
And points to free resources to help you learn
Transparent Data Encryption is an efficient way to secure data in Oracle AI Database
But you need to ensure you keep track of your encryption password!
David Fitzjarrell shows you how to setup an autologin wallet to store this password
buff.ly/xjE8VD1
Discover the pitfalls of using SQL's NATURAL JOIN and JOIN USING syntax. Learn best practices for joining tables in production queries.
buff.ly
I’ve started keeping configuration information for Oracle’s Connection Manager in tables and then generating the cman.ora file needed for each listener. One of the quandaries I ran into…
The road to success is fraught with peril, and one of those perils may be a forgotten or lost encryption password for Oracle. This can happen when a database is migrated from one server to another,…
buff.ly
Many Oracle guides are written for DBAs or long-time developers, and as a newcomer it feels like you’re expected to already know the jargon. I started in the same place, learning the as I went, and…
⚡Free 5-Day Data Career Kickstart https://www.bigdataenergycourses.com/kickstart
If you're preparing for a data interview, you need to know the difference between ROW_NUMBER, RANK, and DENSE_RANK —…
buff.ly
In the latest version of the Oracle database, it is now possible to define the partition key of a partitioned table using an expression. This was already possible previously, but only with a small …
Joins are NOT Expensive! - Raw ReadingWhen talking about Data Lakes and how people access them - we must address some of the misconceptions that made them popular in the first place.
buff.ly
The Only Programming Language Built on Mathematics, Not Fashion
