About the DUAL table.

[Anand]

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

[Tim...]

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...