Does your application relies heavily on DUAL?

If the answer to the question is yes, and your application is on a networked or more particularly in internet, think twice. Because the normal DUAL table if referenced may cause lot of logical I/O to happen. Read below a tip from Mark.J.Bobak as appeared in ORACLE MAGAZINE (September/October 2004).

Mark suggests to create our own DUAL table as an Index Organized Table, rather than a normal heap table. He thinks that it will reduce logical I/Os significantly. The definition of the DUAL table can be like:

CREATE TABLE MYDUAL(
DUMMY VARCHAR2(1)
PRIMARY KEY CONSTRAINT ONE_ROW
CHECK(DUMMY='X')) ORGANIZATION INDEX;

He sees benefit of avoiding overhead of a trigger and still protect the table from having more than one row. Note that the primary key protects against more than one row containing 'X' and the check constraint protects against any rows containing something other than 'X' which results in exactly one row containing 'X'.