New JOIN TO ONE clause in 26.2 SELECT

 I've just published a short video on the new JOIN TO ONE clause in SELECT statements in Oracle 26ai 26.2

This clause allows you to let the database automatically determine JOIN columns based on Primary Key and Foreign Key relationships configured in the database.  JOIN TO ONE defaults to doing a LEFT OUTER JOIN so I also demonstrate how to use it for INNER JOINs

Tracing a Power BI DirectQuery Refresh in Oracle

 In today's video I have demonstrated how Power BI can use DirectQuery to query an Oracle database and refresh reports without actually storing the data in the Power BI file (as would be done if "Import" was used instead of DirectQuery).

I have used SQL Tracing in the Database Instance to identify the SQL statement that Power BI executes

For the first visual in Power BI which shows total salary by Department, the Power BI module and SQL statement are identified as :

MODULE NAME:(msmdsrv.exe) 
CLIENT DRIVER:(ODPM.NET : 23.6.0.0.0)

sqlid='c8a9qd2dzks8h'

SELECT * FROM (
SELECT
*
FROM 
(

SELECT
"t1"."DEPARTMENT_NAME" "c6", SUM ( "t4"."SALARY" )
 "a0"
FROM 
((
select "$Table"."EMPLOYEE_ID" as "EMPLOYEE_ID",
    "$Table"."FIRST_NAME" as "FIRST_NAME",
    "$Table"."LAST_NAME" as "LAST_NAME",
    "$Table"."EMAIL" as "EMAIL",
    "$Table"."PHONE_NUMBER" as "PHONE_NUMBER",
    "$Table"."HIRE_DATE" as "HIRE_DATE",
    "$Table"."JOB_ID" as "JOB_ID",
    "$Table"."SALARY" as "SALARY",
    "$Table"."COMMISSION_PCT" as "COMMISSION_PCT",
    "$Table"."MANAGER_ID" as "MANAGER_ID",
    "$Table"."DEPARTMENT_ID" as "DEPARTMENT_ID"
from "HR"."EMPLOYEES" "$Table"
) "t4"

 LEFT OUTER JOIN 

(
select "$Table"."DEPARTMENT_ID" as "DEPARTMENT_ID",
    "$Table"."DEPARTMENT_NAME" as "DEPARTMENT_NAME",
    "$Table"."MANAGER_ID" as "MANAGER_ID",
    "$Table"."LOCATION_ID" as "LOCATION_ID"
from "HR"."DEPARTMENTS" "$Table"
) "t1" on 
(
"t4"."DEPARTMENT_ID" = "t1"."DEPARTMENT_ID"
)
)

GROUP BY "t1"."DEPARTMENT_NAME"
)
 "MainTable"
WHERE 
(

NOT(
(
"a0" IS NULL 
)
)

)

ORDER BY "a0"
DESC
,"c6"
ASC
 ) WHERE ROWNUM (lessthan) 1001

For the second visual in Power BI which shows count of employees in each Department, the Power BI module and SQL statement are identified as :

MODULE NAME:(msmdsrv.exe)
CLIENT DRIVER:(ODPM.NET : 23.6.0.0.0)

sqlid='dh7nbfqsy942q'

SELECT
"t1"."DEPARTMENT_NAME" "c6",
COUNT("t4"."EMPLOYEE_ID")
 "a0"
FROM 
((
select "$Table"."EMPLOYEE_ID" as "EMPLOYEE_ID",
    "$Table"."FIRST_NAME" as "FIRST_NAME",
    "$Table"."LAST_NAME" as "LAST_NAME",
    "$Table"."EMAIL" as "EMAIL",
    "$Table"."PHONE_NUMBER" as "PHONE_NUMBER",
    "$Table"."HIRE_DATE" as "HIRE_DATE",
    "$Table"."JOB_ID" as "JOB_ID",
    "$Table"."SALARY" as "SALARY",
    "$Table"."COMMISSION_PCT" as "COMMISSION_PCT",
    "$Table"."MANAGER_ID" as "MANAGER_ID",
    "$Table"."DEPARTMENT_ID" as "DEPARTMENT_ID"
from "HR"."EMPLOYEES" "$Table"
) "t4"

 LEFT OUTER JOIN 

(
select "$Table"."DEPARTMENT_ID" as "DEPARTMENT_ID",
    "$Table"."DEPARTMENT_NAME" as "DEPARTMENT_NAME",
    "$Table"."MANAGER_ID" as "MANAGER_ID",
    "$Table"."LOCATION_ID" as "LOCATION_ID"
from "HR"."DEPARTMENTS" "$Table"
) "t1" on 
(
"t4"."DEPARTMENT_ID" = "t1"."DEPARTMENT_ID"
)
)

GROUP BY "t1"."DEPARTMENT_NAME" 

Thus, every refresh runs a number of queries -- some to synchronise the schema from Oracle to Power BI and others to refresh the numbers to present in the Visuals. This proves that the actual load of computing the GROUP BY and aggregations is in the *database instance* (because that is where the data actually resides) and not in the Power BI file (because no data is copied into the Power BI file)

WAIT Clause for DMLs in 26.2

 Oracle 26ai 26.2 now introduces the WAIT (and NOWAIT) Clause for INSERT / UPDATE / DELETE / MERGE DMLs.  We have had a WAIT Clause for SELECT FOR UPDATE statements but not for these "simple" DML statements.

This is my Video Demo :  Wait Clause for DMLs in 26.2