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 https://t.co/k8dYUDTwwo

Tracking changes to values over time is a common requirement For example, recording price fluctuations for goods Alexey Makhotkin steps through designing tables to store these changes, proposing template tables with Anchor Value Valid from/to dates https://t.co/Cp2o4SflIJ

Change the partitioning method for an Oracle table with: ALTER TABLE ... MODIFY PARTITION BY ... This can change The partition method (RANGE/LIST/HASH) The partition columns Add/remove subpartitioning Add the ONLINE clause to allow writes to continue while changing

Like #SQL? Like puzzles? Try #SQuizL A free, daily guess-the-SQL statement quiz You have up to six attempts to complete the statement on the top left using the fragments below There's a new statement to guess at every day at midnight UTC Play now at https://t.co/L2cjHVO9i2

You can run in-database AI inference on Oracle Active Data Guard @ludodba shows you how to do this to find images with semantic search Loading an ONNX model & pictures Creating PL/SQL to generate embeddings Running this proc and searches on the standby https://t.co/lv4ozKDyvv

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 https://t.co/lU91tclnDf

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

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

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 https://t.co/ht7zpEsCIX

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 https://t.co/Qd9PsktLfI

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 https://t.co/GfUNgDcdbw 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 https://t.co/TpK6Sq09YR

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

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 https://t.co/DqEpZSLs42

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) https://t.co/WiGBmZ0hb2

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

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 @Andrej_SQL shows how to improve row estimates with Extended stats Virtual columns https://t.co/9SPPa3NhmG