ZAHIR MOHIDEEN: 2025

Monday, October 20, 2025

ANY_VALUE in Oracle 19c

Oracle 21c introduced the "ANY_VALUE" function . And it is backported to 19c as well.

Per Oracle's documentation , this function is optimized .

<quote>

Use ANY_VALUE to optimize a query that has a GROUP BY clause.

ANY_VALUE returns a value of an expression in a group. It is optimized to return the first value.

<quote>

From what I see , is that it removes the need for a column to be in GROUP BY clause . I am still trying to find the correct use case for this function.

There are subtle differences in the execution plans for SQL without "ANY_VALUE" ( Listing 1) and for a SQL with "ANY_VALUE" function ( Listing 2) . Also , the function is non deterministic ( the return value can't be guranteed to return the same value) .

Here is the example test case . I welcome your comments

Code Examples:

SQL> select banner from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> Create table student as

2 SELECT

3 object_id AS student_id,

4 object_name AS student_name

5 FROM

6 all_objects;

Table created.

SQL> CREATE TABLE student_fees

2 AS

3 SELECT

4 student_id,

5 TO_NUMBER(to_char((sysdate - dbms_random.value(100, 10000)),

6 'YYYY')) AS enrolled_semester_year,

7 round(dbms_random.value(1000, 6000),

8 2) AS tuition_fees

9 FROM

10 student;

Table created.

SQL> alter table student add constraint student_pk primary key(student_id) ;

Table altered.

SQL> alter table student_fees add constraint student_fees_pk primary key(student_id, enrolled_semester_year) ;

Table altered.

SQL> alter table student_fees

2 add constraint student_fees_fk foreign key(student_id)

3 references student(student_id);

Table altered.

SQL> exec dbms_stats.gather_table_stats(user , 'STUDENT');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user , 'STUDENT_FEES') ;

PL/SQL procedure successfully completed.

Listing 1:

SQL without "ANY_VALUE" function.

SQL> Explain Plan for

2 SElect s.student_id , max(s.student_name) ,

3 sum(tuition_fees) from

4 student s , student_fees sf

5 Where s.student_id = sf.student_id

6 group by

7 s.student_id, s.student_name ;

Explained.

SQL> Select * from table(dbms_xplan.display);

Plan hash value: 2527482877

--------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 68156 | 3327K| | 1019 (1)| 00:00:01 |

| 1 | HASH GROUP BY | | 68156 | 3327K| 4024K| 1019 (1)| 00:00:01 |

|* 2 | HASH JOIN | | 68156 | 3327K| | 177 (2)| 00:00:01 |

| 3 | TABLE ACCESS FULL| STUDENT_FEES | 68156 | 665K| | 53 (0)| 00:00:01 |

| 4 | TABLE ACCESS FULL| STUDENT | 68156 | 2662K| | 123 (1)| 00:00:01 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("S"."STUDENT_ID"="SF"."STUDENT_ID")

16 rows selected.

Listing 2:

SQL with "ANY_VALUE" function.

SQL> Explain Plan for

2 SElect s.student_id , any_value(s.student_name) , sum(tuition_fees) from

3 student s , student_fees sf

4 Where s.student_id = sf.student_id

5 group by

6 s.student_id;

Explained.

SQL> Select * from table(dbms_xplan.display);

Plan hash value: 164295499

------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 68156 | 3327K| | 989 (1)| 00:00:01 |

| 1 | SORT GROUP BY NOSORT | | 68156 | 3327K| | 989 (1)| 00:00:01 |

| 2 | MERGE JOIN | | 68156 | 3327K| | 989 (1)| 00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID| STUDENT | 68156 | 2662K| | 661 (1)| 00:00:01 |

| 4 | INDEX FULL SCAN | STUDENT_PK | 68156 | | | 143 (0)| 00:00:01 |

|* 5 | SORT JOIN | | 68156 | 665K| 2696K| 328 (1)| 00:00:01 |

| 6 | TABLE ACCESS FULL | STUDENT_FEES | 68156 | 665K| | 53 (0)| 00:00:01 |

------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

5 - access("S"."STUDENT_ID"="SF"."STUDENT_ID")

filter("S"."STUDENT_ID"="SF"."STUDENT_ID")

19 rows selected.

Sunday, August 10, 2025

Read Only USER and SESSION in Oracle 23 ai

In this post ( https://zahirmohideen.blogspot.com/2025/07/hybrid-read-only-in-oracle-23ai.html) , I described about placing PDBs in "hybrid read only".

In this post , we will another "read only" feature from Oracle 23ai.

This is "read only user and session" .

This enhancement allow the DBAs to control the read/write privileges of user/session across the different parts of the application and

also enables to perform temporarily to control their privileges for testing , administration , application migration/upgrade purposes.

Here is an example.

A column ( READ_ONLY) has been added to the view "DBA_USERS".

In this example , we will be using the user "MOHIDEEN".

By default , when the user is created , the user has "READ WRITE" privilege on their schema.

SQL> show user

USER is "SYSTEM"

SQL> col username format a15

SQL> select username , read_only from dba_users order by created desc FETCH FIRST 5 ROWS ONLY;

USERNAME REA

--------------- ---

MOHIDEEN NO

AV NO

HRREST NO

CO NO

SH NO

Here , I sign in as "MOHIDEEN" and perform the table creation , updates as usual.

oracle@vbox Desktop]$ sqlplus mohideen@freepdb1

SQL> DROP TABLE IF EXISTS emp;

Table dropped.

SQL> create table emp as select object_name as emp_name , object_type as emp_dept from all_objects;

Table created.

SQL> select count(*) from emp;

COUNT(*)

----------

52709

SQL> update emp set emp_dept ='HR' Where emp_dept = 'TABLE';

141 rows updated.

SQL> commit;

Commit complete.

SQL> exit

Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

Version 23.7.0.25.01

Now , I sign in as "SYSTEM" and make the user "MOHIDEEN" as read only.

[oracle@vbox Desktop]$ sqlplus system/oracle@freepdb1

SQL*Plus: Release 23.0.0.0.0 - Production on Sun Aug 10 18:37:29 2025

Version 23.7.0.25.01

Last Successful login time: Sun Aug 10 2025 18:32:37 +00:00

Connected to:

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

Version 23.7.0.25.01

SQL> alter user mohideen read only;

User altered.

SQL> col username format a15

SQL> select username , read_only from dba_users order by created desc FETCH FIRST 5 ROWS ONLY;

USERNAME REA

--------------- ---

MOHIDEEN YES

AV NO

HRREST NO

CO NO

SH NO

Let us go back into the database as "MOHIDEEN" and perform the update operation on the table that I created. As the error clearly states , I can perform only read ( aka SELECT ) operations , not any write operation.

oracle@vbox Desktop]$ sqlplus mohideen@freepdb1

SQL*Plus: Release 23.0.0.0.0 - Production on Sun Aug 10 18:39:54 2025

Version 23.7.0.25.01

Enter password:

Last Successful login time: Sun Aug 10 2025 18:35:45 +00:00

Connected to:

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

Version 23.7.0.25.01

SQL> update emp set emp_dept ='TECH SUPPORT' Where emp_dept = 'VIEW';

update emp set emp_dept ='TECH SUPPORT' Where emp_dept = 'VIEW'

ERROR at line 1:

ORA-28194: Can perform read operations only

Help: https://docs.oracle.com/error-help/db/ora-28194/

SQL> select count(*) from emp;

COUNT(*)

----------

52709

We will reset user to allow write operations.

As in the example , user can perform read write operation.

[oracle@vbox Desktop]$ sqlplus system/oracle@freepdb1

SQL*Plus: Release 23.0.0.0.0 - Production on Sun Aug 10 18:41:21 2025

Version 23.7.0.25.01

Last Successful login time: Sun Aug 10 2025 18:37:29 +00:00

Connected to:

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

Version 23.7.0.25.01

SQL> alter user mohideen read write;

User altered.

[oracle@vbox Desktop]$ sqlplus mohideen@freepdb1

SQL> update emp set emp_dept ='DEV' Where emp_dept = 'INDEX';

115 rows updated.

Here is an example of setting the "read only" option at the session level.

SQL> show user

USER is "MOHIDEEN"

SQL> alter session set read_only=true;

Session altered.

SQL> update emp set emp_dept ='TECH SUPPORT' Where emp_dept = 'VIEW';

update emp set emp_dept ='TECH SUPPORT' Where emp_dept = 'VIEW'

ERROR at line 1:

ORA-28193: Can perform read operations only

Help: https://docs.oracle.com/error-help/db/ora-28193/

SQL> alter session set read_only=false;

Session altered.

SQL> update emp set emp_dept = 'OPS' where emp_dept = 'PROCEDURE';

35 rows updated.

Comments Welcome.

Sunday, July 27, 2025

Enhancement in Direct Path Insert - Oracle 23ai Feature

This is one another nice feature introduced in 23ai.

Oracle 23ai enhanced this feature with follow up DMLs after the direct path insert.

When we are loading lots of data into the Datawarehouse , we typically use direct path insert , as the data load is faster ; as oracle directly writes into the data file , bypassing the buffer cache and above the high water mark.

But , we can't do any data manipulation on the underlying table without issuing COMMIT / ROLLBACK.

Now , in Oracle 23ai ,we can use DMLs to view the table contents. This features enables us to do multiple checks before committing the data into the database.

Comments Welcome!

Here is an example.

SQL> select banner from v$version;

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

SQL> DROP TABLE IF EXISTS emp;

Table dropped.

SQL> create table emp

2 as

3 select object_name as emp_name ,

4 object_type as emp_dept

5 from all_objects

6 where 1 = 2;

Table created.

SQL> select count(*) from emp;

COUNT(*)

----------

SQL> insert /*+ APPEND */ into emp

2 select object_name , object_type

3 from all_objects;

52749 rows created.

SQL> select count(*) from emp;

COUNT(*)

----------

52749

SQL> update emp

2 set emp_dept ='HR'

3 Where emp_dept = 'TABLE';

149 rows updated.

SQL> commit;

Commit complete.

Sunday, July 20, 2025

Hybrid Read Only in Oracle 23ai

"Hybrid Read only mode" one of the nicest improvements in Oracle 23ai.

With this , we can have the pluggable databases ( PDB) either as "read/write" / "read only" .

In this "read only" mode , the common users will able to update the tables , as if PDB is "open" and "read write" mode the local (PDB) users will not be able to update / insert any tables; they can "select only" from the pdb objects.

This features enables the DBAs to patch , update the database objects in a pluggable database.

Here is an example.

There is a new column name 'IS_HYBRID_READ_ONLY' to the v$view ( v$container_topology)

Log on to CDB and review the PDB status.

Session 1:

SQL> show con_name;

CON_NAME

------------------------------

CDB$ROOT

SQL> select con_name , open_mode , is_hybrid_read_only from v$container_topology;

CON_NAME OPEN_MODE IS_HYBRID_READ_ONLY

___________ _____________ ______________________

CDB$ROOT READ WRITE NO

PDB$SEED READ ONLY NO

FREEPDB1 READ WRITE NO

Session 2 :

In this PDB , let us create a user in PDB and give him access to create a table , quota unlimited on "USERS" tablespace. Note that , the user need not created with "LOCAL" as the prefix.

SQL> show user

USER is "LOCAL_MOHIDEEN"

SQL> create table emp as select object_name as emp_name , object_type as dept from all_objects ;

Table created.

SQL> insert into emp values ( 'Zahir' , 'IT') ;

1 row created.

SQL> commit;

Commit complete.

As the PDB is open for "READ WRITE" , the local user was able to create a table and insert a record in to the table.

Now , let us go to the session 1 and make the PDB as hybrid read only.

Session 1:

In this session , we made the PDB as "hybrid read only" and created a common user ( C##MOHIDEEN) and give him access

to the table owned by the local user ( LOCAL_MOHIDEEN). Note that common user has to be created with C## as prefix.

SQL> show con_name;

CON_NAME

------------------------------

CDB$ROOT

SQL> alter pluggable database freepdb1 close immediate;

Pluggable database FREEPDB1 altered.

SQL> alter pluggable database freepdb1 open hybrid read only;

Pluggable database FREEPDB1 altered.

SQL> select con_name , open_mode , is_hybrid_read_only from v$container_topology;

CON_NAME OPEN_MODE IS_HYBRID_READ_ONLY

___________ _____________ ______________________

CDB$ROOT READ WRITE NO

PDB$SEED READ ONLY NO

FREEPDB1 READ WRITE YES

Session 2 :

Now , let us go to the other session and try to insert a record in to the table (EMP)

SQL> show user;

USER is "LOCAL_MOHIDEEN"

SQL> insert into emp values ('John Doe','HR');

insert into emp values ('John Doe','HR')

ERROR at line 1:

ORA-16000: Attempting to modify database or pluggable database that is open for

read-only access.

Help: https://docs.oracle.com/error-help/db/ora-16000/

SQL> exit

Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

Version 23.7.0.25.01

This time , let us log on to the PDB as the common user ( c##mohideen)

SQL> show user

USER is "C##MOHIDEEN"

SQL> insert into local_mohideen.emp values ( 'John Doe','HR');

1 row created.

SQL> commit;

Commit complete.

There we have it .

With the cdb user , I can do read/write operations, where with the local user the database is read only.

Hybrid Rocks! :-)

Comments Welcome!

Monday, July 7, 2025

Oracle 23ai New Features.

There are a few new features introduced in Oracle 23ai that have been in other RDBMS systems for a while.

These features enable compatibility with other systems and also make it easier for non-Oracle database developers.

I welcome these features,

1) as it opens to all DB developers

2) and it lead to simpler / Readable SQL

Here we go ...

a) No need for DUAL

There is no need to use DUAL for SELECT that uses only expression.

SQL> select banner from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

SQL> select sysdate ;

SYSDATE

---------

07-JUL-25

SQL> select level connect by level <=5;

LEVEL

----------

b) Boolean Datatype

The new datatype 'boolean' simplifies the development.

Now, it can hold boolean values ( true/false, 1 /0 )

Here is an example.

SQL> create table t ( c1 int , c2 boolean) ;

Table created.

SQL> insert into t values ( 1 , 'true');

1 row created.

SQL> insert into t values ( 2 , 'false');

1 row created.

SQL> insert into t values ( 3 , 1);

1 row created.

SQL> select * from t;

C1 C2

---------- -----------

1 TRUE

2 FALSE

3 TRUE

C) VALUES clause

Continuing the example from "Boolean Datatype",

We can replace those 3 individual insert SQLs with one SQL as below.

SQL> delete from t;

3 rows deleted.

SQL> insert into t values ( 1 , 'true') , ( 2 , 'false'),( 3 , 1);

3 rows created.

SQL> select * from t;

C1 C2

---------- -----------

1 TRUE

2 FALSE

3 TRUE

c) Simplified Update / Delete

The UPDATE / DELETE are handled easily with the new syntax.

As an example, we need to increase the salaries of all employees in Location ID "1700" to receive 2.25%, then we can use the simple join to accomplish it.

SQL> select * from t_dept;

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID

------------- ------------------------------ ---------- -----------

10 Administration 200 1700

20 Marketing 201 1800

30 Purchasing 114 1700

40 Human Resources 203 2400

50 Shipping 121 1500

60 IT 103 1400

70 Public Relations 204 2700

80 Sales 145 2500

90 Executive 100 1700

100 Finance 108 1700

110 Accounting 205 1700

120 Treasury 1700

130 Corporate Tax 1700

140 Control And Credit 1700

150 Shareholder Services 1700

160 Benefits 1700

170 Manufacturing 1700

180 Construction 1700

190 Contracting 1700

200 Operations 1700

210 IT Support 1700

220 NOC 1700

230 IT Helpdesk 1700

240 Government Sales 1700

250 Retail Sales 1700

260 Recruiting 1700

270 Payroll 1700

27 rows selected.

SQL> Update t_emp e

set e.salary = e.salary + ( e.salary * ( 2.25 / 100 ) )

from t_dept d

Where e.department_id = d.department_id and d.location_id = 1700;

18 rows updated.

e) "RETURNING" Clause

We can use the "RETURNING" clause to identify the state and the future state of the impacted records.

This can be used in SELECT , INSERT , UPDATE , DELETE and MERGE statements.

In the following example, the salary of the employee with ID 111 is given a 2.25% increase.

SQL> select employee_id , salary from t_emp where employee_id = 111;

EMPLOYEE_ID SALARY

----------- ----------

111 7700

SQL> var l_proposed_sal number;

SQL> var l_current_sal number;

SQL> Update t_emp

set salary = salary + ( salary * ( 2.25 / 100 ) )

Where employee_id = 111

Returning new salary ,old salary

into :l_proposed_sal , :l_current_sal ;

1 row updated.

SQL> print :l_proposed_sal ;

L_PROPOSED_SAL

--------------

7873.25

SQL> print :l_current_sal ;

L_CURRENT_SAL

-------------

7700

Tuesday, June 24, 2025

Group by 'Column Position' in Oracle 23ai

In this blog post ( https://zahirmohideen.blogspot.com/2024/04/oracle-23c-new-feature-grouping-by-alias.html ), we were introduced to a minor enhancement to the group by clause.

We can also use the column position in the group by clause in Oracle 23ai, if the parameter (group_by_position_enabled) is set to TRUE.

From my perspective, this shortcut makes the code short, but it may not be readable or ideal in the long term.

Again, this is my view.

Here is an example.

SQL> create table t as select object_name , object_type from all_objects;

Table T created.

SQL> sho parameter group_by_po

NAME TYPE VALUE

------------------------- ------- -----

group_by_position_enabled boolean FALSE

SQL> select object_type , count(*) as cnt from t group by 1 having cnt > 1;

Error starting at line : 1 in command -

select object_type , count(*) as cnt from t group by 1 having cnt > 1

Error at Command Line : 1 Column : 8

Error report -

SQL Error: ORA-03162: "OBJECT_TYPE": must appear in the GROUP BY clause or be used in an aggregate function as 'group_by_position_enabled' is FALSE

Help: https://docs.oracle.com/error-help/db/ora-03162/

More Details :

https://docs.oracle.com/error-help/db/ora-03162/

SQL> alter session set group_by_position_enabled = true;

Session altered.

SQL> select object_type , count(*) as cnt from t group by 1 having cnt > 1;

OBJECT_TYPE CNT

_____________________ ________

TABLE 2510

CLUSTER 10

INDEX 2486

SYNONYM 13105

TABLE PARTITION 656

INDEX PARTITION 608

SEQUENCE 37

DIRECTORY 12

PACKAGE 701

FUNCTION 307

PROCEDURE 54

TYPE 1656

CONTEXT 23

OPERATOR 60

DOMAIN 109

LOB 3

TABLE SUBPARTITION 33

LOCKDOWN PROFILE 3

PACKAGE BODY 165

CONSUMER GROUP 18

JOB CLASS 3

DESTINATION 2

SCHEDULE 4

WINDOW 9

SCHEDULER GROUP 4

EVALUATION CONTEXT 2

XML SCHEMA 19

INDEXTYPE 11

TYPE BODY 81

RULE SET 2

TRIGGER 129

JAVA CLASS 32754

JAVA RESOURCE 1228

JAVA DATA 17

LIBRARY 25

36 rows selected.

Let me know your comments, please!