regarding index

[shailendra]

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

[ales]

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

[shailendra]

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

[ales]

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

[shailendra]

thanks ales

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

thanks again

[ales]

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.

[ales]

Ouch ... sending e-mails is disabled by admin.
So, if you want you can e-mail me at alesv@post.cz.
Regards,
Ales

[shailendra]

anybody want to suggest more on this thread

thanks

[tamilselvan]

If you are deleting more than 20% of rows in the table, then rebuild the index after deletion is done.

[shailendra]

when we delete records why we need to rebuild index?

thanks