DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: A Funny discovery on Dual Table

  1. #1
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433

    One day , I just played around my oracle ,

    insert into dual values ('Y');
    commit;
    select count(*) from dual; --->result is 2

    then the magic takes place :
    when I type :
    delete from dual;
    commit;
    guess how many records deleted ?
    it's not 2 but 1 . There are still one record remain in the table !


    It seems DML on Dual table violate the SQL rule . Could anyone tell why it happens like that ?
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  2. #2
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    Hi Ligang,
    When you insert any value in Dual table, it is stored in a kind of stack. Suppose you insert 10 values A to J in Dual. commit. Now you select * from dual, it will give one row only, but if you count(*) it will give 11 rows.
    Now delete from dual. It will delete only one row. Commit. Now select * from dual, it will give A. Now again delete from dual and commit and select * from dual...It will give B...and so on...
    Hope you understand that when you delete from dual, it deletes only one existing row and selects the next value from the stack.
    try it for better understanding.
    Hope it helps..
    Regards
    Sandy
    "Greatest Rewards come only with Greatest Commitments!"

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Some additional information regarding this can be found in http://www.dbasupport.com/forums/sho...threadid=20238
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Originally posted by ligang

    One day , I just played around my oracle ,

    Hi ligang, so you see : do not play to much with oracle, you never know what you will find out!

    ;-)
    Orca

  5. #5
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    Thanks jmodic for that additional useful piece of information. IT WILL HELP ALL OF UP TO UNDERSTAND THE REASONS MORE CLEARLY.
    Thanks
    Sandy
    "Greatest Rewards come only with Greatest Commitments!"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width