Writing about oracle dual table for fresh grads ( and experts as well). Dual is an oracle inbuilt table (Object). It seems very simple, though it can be challenging if played around. I have tried to put it up in questions format so a simple DUAL object can look little interesting. 😉 Anyways this post can be a nice precursor to experiment more with oracle dual table.
Target Audience: – DB professionals who have 2 to 5 years of working experience on oracle database.
What is Oracle DUAL table? : We all know DUAL is an Oracle Database dictionary table available to all the users. It’s a one column and one row table that enable users to perform the needed calculations.
For example :-
SELECT 55242-402+212 FROM DUAL;
SELECT SYSTIMESTAMP FROM DUAL;
So, why this dual table is needed? There is an interesting story from around DUAL table if you are interested. From source – (Wikipedia) (Words from the person who created in DUAL table in Oracle Dictionary) …
I created the DUAL table as an underlying object in the Oracle Data Dictionary. It was never meant to be seen itself, but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. Then, by using GROUP BY, the resulting join could be summarized to show the amount of storage for the DATA extent and for the INDEX extent(s). The name, DUAL, apt for the process of creating a pair of rows from just one.
When do you use DUAL? Generally when I am required to perform calculations like check system date, evaluate a quick arithmetic and no calculator is available around 🙂 and to merge some user defined values to the result set.
SELECT DISTINCT PRODUCT_TYPE FROM PRODUCTS UNION
SELECT “LARGE INSTALLATIONS” FROM DUAL UNION
SELECT “CONSULTING” FROM DUAL;
In Informatica, I have the habit of using DUAL table (local copy) as Target instance when I tend to drop all the records before target instance. This generally happens in the reconciliation process. The session is set to be aborted if the Reconciliation fails and the rows will get filtered out before target instance if the reconciliation / Audit check is successfully passed.
When there is a need to create a set of data for example set of dates to load time dimension. You can refer how dual table is used to create source data in below post – http://etllabs.com/informatica/how-to-load-date-dimension/4860/
Can you create a DUAL like table? Why not! You can replicate the DUAL table structure in your own schema and use it exactly as you do with DUAL table.
CREATE TABLE mydual AS SELECT * FROM DUAL
How many columns, rows can you return from a dual table? humm as many as you want.
Can you change, insert, update rows into DUAL table? Yes you can however please do not try to do so in application databases for your and project safety.
Your thoughts and suggestions are highly appreciated.