Pantea

Senior Data Engineer at Mydigipay Co,
AI, ML and Database enthusiast
I'm here to learn and share what I've learned :)

[SQL Daily @sqldaily]

Turn natural language into #SQL on Oracle AI Database with

SELECT AI <your query>

But how do you ensure the SQL is "good"?

@MarkHornick gives best practices

Add schema comments & annotations
Assist joins with foreign keys
Make views for common queries

https://t.co/uufzkURUSv

[SQL Daily @sqldaily]

SELECT ... FOR UPDATE statements lock the rows you're querying

If another process has these locked it must wait

To get an instant error instead, use:

SELECT ... FOR UPDATE NOWAIT

@vlad_mihalcea shows how do this with JPA and Hibernate

https://t.co/8nG6xUQjVX

[Pantea @Pani_trng]

بچه ها، کسی در مورد نحوه شرکت تو آزمون Lpic اطلاعات داره؟
ممنون 🙏

[Pantea @Pani_trng]

@Redmeption7 @omophagyy 🥺🥺

[SQL Daily @sqldaily]

The next version of the #SQL standard is in progress

Peter Eisentraut reports on the features that have been accepted into the upcoming version

QUALIFY
INSERT BY NAME
SELECT list EXCLUDE
JOIN TO ONE
Work in progress: Key joins

https://t.co/XwtnpJKEWS

[SQL Daily @sqldaily]

Trying to tune a transaction in Oracle AI Database?

Run a #SQL trace to capture runtime stats and plans

This shows what's slowest and why

@ddfdba lists how to do this using DBMS_MONITOR

And parse the traces to readable form using TKPROF

https://t.co/FiCgRMwJ9w

[SQL Daily @sqldaily]

Joins can add lead to duplicating rows in the results by mistake

Ensure rows from the left table in are processed once with:

left_tab JOIN TO ONE ( right_tab ON ... )

This raises a runtime error if the result duplicates a row in left_tab

Added in Oracle AI Database 23.26.2

[SQL Daily @sqldaily]

Like #SQL?

Like chess?

Play Quack-mate

A chess engine by Roberto Cornacchia built in pure #SQL!

Play it at https://t.co/a9tzwSZvZt

[SQL Daily @sqldaily]

From Oracle AI Database 23.9 you can use

SELECT ...
GROUP BY ALL

And the database automatically generates the grouping

Note if you have

SELECT c1 || FN ( c2 )

it won't work - it only applies to aggregate free expressions

@connor_mc_d explores https://t.co/iONIfqeUvL

[SQL Daily @sqldaily]

You can stop a user writing to Oracle AI Database with

ALTER USER ... READ ONLY

This overrides their granted privileges

Queries are allowed
All other DML or DDL which changes the db errors

Allow writes with

ALTER USER ... READ WRITE

@oraclebase demos https://t.co/BWwGUIvoKh

[MIT CSAIL @MIT_CSAIL]

Happy birthday to Alan Turing, the creator of modern computing.

[SQL Daily @sqldaily]

Coding agents write better #SQL when you provide details of your schema

@anders__swanson shows how to do this in Oracle AI Database with schema annotations and comments

CREATE TABLE ... ( ... ) ANNOTATIONS ( ... )
COMMENT ON TABLE ...

https://t.co/D9Ptznf4aW

[Connor on SQL and Database @connor_mc_d]

The perfect guide to the oracle optimizer
https://t.co/ZEm9PP0DcX
Kudos @vldbb

[SQL Daily @sqldaily]

Assertions in Oracle AI Database enable you to define cross-table constraints

@sdstuber 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

https://t.co/eFaubduQgD

[Pantea @Pani_trng]

@katalinn97 @omophagyy زندگیم عکساش خوبه
مام بگیریم از این عکسااا😍😍😍

[SQL Daily @sqldaily]

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

https://t.co/WyPRzOu9CT

[SQL Daily @sqldaily]

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

[SQL Daily @sqldaily]

Brand new to Oracle AI Database?

@anders__swanson shows you how to get started

Run 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

https://t.co/sM6xVStsSU

[Pantea @Pani_trng]

@TShajari @omophagyy 🥺🥺💙💙💙💙

[SQL Daily @sqldaily]

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!

@ddfdba shows you how to setup an autologin wallet to store this password

https://t.co/IDCRycjVC6