Everyone must have came across DUAL table present in oracle database, most of DBA's are using it daily but do not know what exactly it is, here we will discuss about the same
DUAL table is special one row one column table available by default in oracle database
CREATE TABLE "SYS"."DUAL"
DUAL table is special one row one column table available by default in oracle database
- DUAL table is owned by SYS schema in oracle i.e. DUAL is part of oracle data dictionary
- DUAL is having only one row and only a column named DUMMY of VARCHAR2 datafile with value x.
- Selecting from dual table is useful for computing constant expressions with select statement, as DUAL is only one row table, the constant is returned only once.
- The advantage to DUAL is the optimizer understand dual is special one row , one column table
- There are times when some calculations need to be performed on values which are not available in database table, In such arithmetic calculations, table are not referenced only numeric values are used, To perform such calculations the SELECT query can be used to output the calculated values. A SELECT always requires a table in the FROM clause without which it fails.
- Now we will perform some operations using DUAL table
SQL> DESC DUAL
Name
Null? Type
----------
-------- --------------
DUMMY
VARCHAR2(1)
Selecting from DUAL table
SQL> select * from DUAL;
D
-
X
Performing arithmetic operations
SQL> select 25*25 from dual;
25*25
----------
625
select date using dual
SQL>
select sysdate from dual;
SYSDATE
---------
17-JAN-19
Generating DDL statements using DUAL
SQL>
select dbms_metadata.get_ddl('TABLE', 'DUAL') from dual;
DBMS_METADATA.GET_DDL('TABLE','DUAL')
------------------------------------------------------------------
( "DUMMY" VARCHAR2(1)
) PCTFREE 10 PCTUSED 4
Features of DUAL is not limited to this operations only, we can much more than this