Enhanced Querying: Eliminating the “FROM DUAL” Clause in Oracle Database 23ai !!
2 min readHey Folks !!
Oracle Database 23c, the latest long-term support release, brings over 300 new features, with a focus on three key areas:
1. AI for Data
2. Dev for Data
3. Mission-Critical for Data
For the past couple of years, DBA & developers have been accustomed to writing SELECT ... FROM DUAL to obtain the result of a single row expression. But with the introduction of Oracle Database 23c, this is no longer necessary—you can simply leave out “DUAL” entirely!
Traditionally, the “FROM DUAL” clause in Oracle was used when a query required a table reference, even if no actual table was involved. DUAL was a dummy table with a single column and single row with value X. This table was often used to check date and performing arithmetic calculations.
With Oracle 23c, the company has taken a significant step forward by introducing a feature that eliminates this “FROM DUAL” clause. This change not only simplifies query structures but also brings Oracle in line with ANSI SQL-92 standards.
Check and Start the DB & Listener status :
[oracle@localhost ~]$ systemctl start oracle-free-23ai
[oracle@localhost ~]$
[oracle@localhost ~]$ /etc/init.d/oracle-free-23ai status
You must be root user to run the configure script. Login as root user and then run the configure script.
[oracle@localhost ~]$ su - root
Password:
[root@localhost ~]# /etc/init.d/oracle-free-23ai status
Status of the Oracle FREE 23ai service:
LISTENER status: RUNNING
FREE Database status: RUNNING
[root@localhost ~]#
[root@localhost ~]# ps -ef|grep pmon
oracle 6681 1 1 03:07 ? 00:00:00 db_pmon_FREE
root 7281 7203 0 03:08 pts/0 00:00:00 grep --color=auto pmon
[root@localhost ~]# su - oracle
[oracle@localhost ~]$
[oracle@localhost ~]$ . oraenv
ORACLE_SID = [oracle] ? FREE
The Oracle base has been set to /opt/oracle
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Fri Aug 16 03:08:24 2024
Version 23.5.0.24.07
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.5.0.24.07
SQL> def
DEFINE _DATE = "16-AUG-24" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "FREE" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "2305002407" (CHAR)
DEFINE _EDITOR = "vi" (CHAR)
DEFINE _O_VERSION = "Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.5.0.24.07" (CHAR)
DEFINE _O_RELEASE = "2305002407" (CHAR)
SQL>
SQL> select sysdate from dual;
SYSDATE
---------
16-AUG-24
SQL> select sysdate;
SYSDATE
---------
16-AUG-24
SQL> select 20/5;
20/5
----------
4
SQL> select rownum connect by level <=10;
ROWNUM
----------
1
2
3
4
5
6
7
8
9
10
SQL> set autotrace traceonly explain;
SQL> select sysdate;
Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
SQL> set autotrace off;
SQL>
SQL> set serveroutput on
declare
v_date date;
begin
select sysdate
into v_date;
dbms_output.put_line(v_date);
end;
/SQL> SQL> 2 3 4 5 6 7 8 9
16-AUG-24
PL/SQL procedure successfully completed.
SQL>
SQL> select (2*8) from dual;
(2*8)
----------
16
SQL> select (2*8) dual;
DUAL
----------
16
Hope it helped !! 🙂
