regarding index
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: regarding index

  1. #1
    Join Date
    Sep 2000
    Posts
    305
    HI friends I want to know one thing about index

    If I want to delete some data fome a table in which I have a Index and the data are huge it is in lacks
    Which option is good?
    a) delete first record then drop the index
    b) drop the index and then delete the records.

    Which option takes less time?
    I will explain by an example
    I have a table called tbl_x having column col1,col2
    I have created index on col1 which is having date datatype and the no. of records is around 6000000 now I want to delete records which should be less then the specified date.

    So please tell me which option will be good a or b?

    Thanks in advance
    Shailendra

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Hi,
    go for a) since Oracle can use index to find records to be deleted.
    If you drop that index and there is no other index to use then you probably hit the full table scan.

    If you need to delete large amount of rows in one transaction you should pay attention to proper size of rollback.
    Sometimes it's better to divide one huge transaction to ten or more smaller transactions, e.g. delete rows for one month insted of for the whole year.

    HTH,
    Ales

  3. #3
    Join Date
    Sep 2000
    Posts
    305
    Thanks ales

    can you explain me in detail the role of rollback segment while delete records.
    As you told that if I go for large transaction I need a large rollback segment. I know only this thing that rollback segment keeps record of data which is changed before any DML happens.
    Can you please tell explain me in detail.

    Thanks
    Shailendra

  4. #4
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    My apologies ... to explain that in detail is beyond my knowledge.

    During a transaction data are changed with UPDATE or DELETE statements. Then the transaction is commited or rollbacked. For case of rollback the original data must be saved somewhere. That is purpose of rollback segments.

    If you want to delete rows from the table then all the db blocks you're going to change must be copied to rollback segment. Thus, you need enough space in the rollback to hold all the data you want to delete.

    If you don't have enough space statement returns error and you have to rollback it which is pain for large amount of data.

    So my advise is: first try on smaller amount and if all goes well, try larger.

    In case you think you have not enough space in rbs it's possible to create a large rbs and tell the transaction to use it.

    Some info about rollback segment you can find in following views:
    sys.dba_rollback_segs
    dba_segments
    user_segments

    Ales

  5. #5
    Join Date
    Sep 2000
    Posts
    305
    thanks ales

    can you give me your yahoo id if you do not mind can you add it to my list

    thanks again

  6. #6
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    No, I don't mind at all. But I do not have yahoo so I send you my office address.
    Have a nice time!
    A.

  7. #7
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Ouch ... sending e-mails is disabled by admin.
    So, if you want you can e-mail me at alesv@post.cz.
    Regards,
    Ales

  8. #8
    Join Date
    Sep 2000
    Posts
    305
    anybody want to suggest more on this thread

    thanks

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    If you are deleting more than 20% of rows in the table, then rebuild the index after deletion is done.

  10. #10
    Join Date
    Sep 2000
    Posts
    305
    when we delete records why we need to rebuild index?

    thanks

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