WHAT IS DUAL TABLE IN ORACLE?

In this post, you will learn about the Oracle DUAL table which is a special table used for evaluating expressions or calling functions.

Now let’s dig into more on the DUAL table.

You can watch below video on DUAL table which is showing code snippets by executing them in SQL Developer:

Why do we need DUAL table in Oracle?

In Oracle, the SELECT statement must have a FROM clause unlike other databases such as SQL Server, My SQL, Sybase, and PostgreSQL..etc.

In those databases (not Oracle), in order to get the date, you can simply write :

SELECT GETDATE(); - in SQL Server and Sybase

SELECT CURRENT_DATE; - In PostgreSQL and Greenplum

If you observe, there is no table reference (FROM clause) in the above SELECT queries.

You might be confused about why there is a need to give FROM clause if you are new to Oracle and had experience with the above-mentioned databases.

But one thing is clear, FROM clause is mandatory in all the queries in Oracle SQL. So we have to re-write the above query like below in Oracle:

SELECT CURRENT_DATE from my_table;

Of course, you can keep any physical table in the place of ‘my_table’ and get the result of the current date.

But the problem here is, you ended up getting the same number of rows currently you have in your physical table even for the current date. Obviously we don’t want this.

Fortunately, Oracle provides you with the DUAL table which is a special table that belongs to the schema of the user SYS and it is accessible to all users.

Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once.

This is shown below:

DESC DUAL;

SELECT * FROM dual;

SELECT COUNT(*) FROM DUAL;

SELECT CURRENT_DATE from dual;

or you can use

SELECT sysdate from dual;

By using the DUAL table, you can execute queries that contain functions that do not involve any table like the LOWER() function as follows:

SELECT LOWER('DUAL TABLE') FROM dual;

SELECT SYSDATE FROM DUAL; -Returns current date in Oracle

Besides calling built-in functions, you can use expressions in the SELECT clause of a query that accesses the DUAL table:

SELECT (10 + 5)/2 FROM dual;

The DUAL table is the most simple one because it was designed for fast access.

This optimization provides even better performance than directly accessing the physical DUAL table.

Following code display the numbers 1..10 from DUAL :

SELECT level
FROM DUAL
CONNECT BY level <=10;

Read also:

1. DIFFERENCE BETWEEN UNION AND UNION ALL
2. DIFFERENCE BETWEEN ALL_TAB_COLS AND ALL_TAB_COLUMNS IN ORACLE
3. ORACLE : GET RECORD COUNTS OF ALL TABLES IN A SCHEMA
4. LOADING DATA INTO ORACLE THROUGH SQL LOADER UTILITY
5. INTERVIEW QUESTION: FIND THE CANCELLATION RATE USING TRIPS AND USERS TABLES

Comments

comments