About the DUAL table.

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

About the DUAL table.

Postby Anand » Fri Jul 06, 2012 4:11 am

Hi Tim,

Feels good to post a question after quite some time.

Look at the following :

SQL> select count(*) from dual;

COUNT(*)
----------
1

SQL> insert into dual values('A');

1 row created.

SQL> insert into dual values('B');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from dual;

COUNT(*)
----------
1

Q.1: Why the count is 1 whereas it has to be 3 , as I have inserted 2 more rows?


SQL> select * from dual;

D
---
X

Q.2: Why it is displaying the value as 'X' and not 'A' or 'B' which I have inserted just above?


SQL> delete from dual;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from dual;

D
-
A

Q.3: I want to delete all rows of dual(therefore not put any where clause with delete statement)
but why it is deleting the very first row i.e 'X' and not 'A' and 'B'?

Q.4: Any thought,how can we create "dual" like table in terms of delete statement i.e.
"issue delete statment without where clause which in turn deletes only the first row and not the complete rows".

please provide your valueable inputs.

Thanks & Regards,
Anand Kumar Ojha
Anand
Member
 
Posts: 22
Joined: Thu Jun 07, 2012 2:59 pm

Re: About the DUAL table.

Postby Tim... » Fri Jul 06, 2012 6:16 pm

Hi.

You should *never* touch dual. There is lots of internal functionality that can break if you mess with it. Oracle sometimes say modifying dual will result in an unsupportable system.

The reason you don't always see multiple rows when querying dual is because since Oracle 10g, many operations against the dual table don't actually hot the dual table. This was a performance optimization in 10g.

You can use triggers to manage deletes if you want, but the optimizations associated with the dual table are such that you will never be able to mimic the action as efficiently.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 1 guest