Dual table

[rakesh76]

hi all,
well m preparing for oca(sql). i found some strange result in dual table. so i would b very thankful if any one can explain me !!


SQL> connect scott/tiger as sysdba
Connected.
SQL> select * from dual;
D
-
X

SQL> insert into dual
2 values('y');
1 row created.

SQL> select count(*) from dual;

COUNT(*)
----------
3

Questions :

1) As dual is sys table y it allows me to insert row into it ?? and if i can insert row into it...y it doesn't show me even thogh it and gave me msg that '1 row created.'

2) why it has datatype only varchar2 and not number ??


waiting 4 ur reply
thanks
rakesh

[leo]

What user ID did you use to login? Regular user should not be able to update this table. Why are you saying it does not show new rows? There are 2 more rows in dual than there shoudl be.

[slimdave]

DUAL is a special table, and the optimizer uses particular optimizations for it.

Never modify the DUAL table.

[tamilselvan]

connect scott/tiger as sysdba

causes to allow more rows in dual table.

Tamil

[rakesh76]

hi all,

Thanks for the reply.

well leo...while using count(*), it shows me three records but when u use select * from dual, it will show u only one record the value (x) which is by default there...

so my question is y is shows three records ??

thanks
rakesh

[slimdave]

well leo...while using count(*), it shows me three records but when u use select * from dual, it will show u only one record the value (x) which is by default there...

so my question is y is shows three records ??

Like I said before, "DUAL is a special table, and the optimizer uses particular optimizations for it.". The optimizer "knows" what the result of "SELECT * FROM DUAL" is, because there is only one row in the DUAL table. A similar optimization does not apply to to "SELECT COUNT(*) FROM DUAL".

[rakesh76]

thanks slimdave for the reply.

[marist89]

Is DUAL a special table or something?

[Azzer_Mac]

Yes it is.

Can be very usefull in certain circumstances.
What if you just wanted to calculate a bunch of numbers, for example 10*10?
I know this is a very simple example, but you would use the dual table like this;

select 10*10 from dual;