Professional Web Applications Themes

how many deleted records has a table - Oracle Server

I had a table of 15 million records. After deleting all records (using the delete command) I have to wait about 1 minute for a "select count(*)". The result was that it contained 0 records. After recreating the table the problem was solved. Therefore I want to check on each table how many deleted records they involve. I am using ORACLE 8.1.6. Ronald...

  1. #1

    Default how many deleted records has a table

    I had a table of 15 million records.

    After deleting all records (using the delete command) I have to wait
    about 1 minute for a "select count(*)". The result was that it
    contained 0 records.

    After recreating the table the problem was solved.

    Therefore I want to check on each table how many deleted records they
    involve.

    I am using ORACLE 8.1.6.

    Ronald
    Ronald Guest

  2. #2

    Default Re: how many deleted records has a table

    If you have a look at either the TRUNCATE table command or the phrase 'HIGH
    WATER MARK' in the doentation you will find the explanation for your
    performance issues here. I'd hope that atosorigin had sufficient guys in
    house who you could ask about this sort of thing.

    --
    Niall Litchfield
    Oracle DBA
    Audit Commission Uk
    "Ronald" <com> wrote in message
    news:google.com... 


    Niall Guest

  3. #3

    Default Re: how many deleted records has a table

    if you are deleting in sql*plus, make sure you have FEEDBACK set and you'll
    get a message of the number or rows deleted

    if you're doing this in PL/SQL, use the SQL%ROWCOUNT or mycursor%ROWCOUNT to
    check the number or rows deleted

    similar functionality exists in other APIs

    (Niall is correct that HIGH WATER MARK is the issue -- the count is likely
    scanning all blocks that have ever had rows in them)

    -- mcs

    "Niall Litchfield" <gov.uk> wrote in message
    news:3fa0e126$0$9479$news.pipex.net... 
    'HIGH 
    >
    >[/ref]


    mcstock Guest

  4. #4

    Default Re: how many deleted records has a table


    com (Ronald) writes:
     

    Hallo Ronald,

    you may look in the Database Concepts Manual (a96524 for Oracle 9i or
    a76965 for Oracle 8.1.6). It discusses all the logical an physical
    storage structures.

    Harald

    PS: Our last meeting was really funny. Please, greet the other
    colleagues.
    Harald Guest

  5. #5

    Default Re: how many deleted records has a table

    On Thu, 30 Oct 2003 01:38:43 -0800, Ronald wrote:
     

    Hi Ronald,

    have a look here for an explanation :

    http://www.jlcomp.demon.co.uk/faq/trunc_del.html


    Regards,
    Norman.

    --
    Delete the obvious bit from my email address to reply by email.


    Norman Guest

Similar Threads

  1. Accessing Deleted Records in FoxPro with CF
    By Natesac in forum Coldfusion Database Access
    Replies: 1
    Last Post: April 4th, 09:17 PM
  2. How to get All Records in Table A that don't havematching ID in Table B
    By Photoshare in forum Coldfusion Database Access
    Replies: 4
    Last Post: February 23rd, 10:46 PM
  3. Replies: 2
    Last Post: September 9th, 12:07 PM
  4. retrieve deleted records for a specici userid
    By Patrick in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 17th, 07:15 PM
  5. Delete from one table with matching records in another table
    By Dale Fye in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 1st, 01:28 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139