If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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.

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.

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