We are getting "Global Enqueue Services Deadlock detected. More info in file" on one of our 3 node RAC running on 10.2.0.4upon checking the corresponding trace file found below sql statement causing the problem.

beased on above key value I understood that it has correct cardinality value (0.05%)

What I understood sofar about the bitmap indexes is, DML operations against bitmap indexed column are expensive and it should have less cardinality values (i.e distinct keys)here cardinality value is low but DML operations are happening more on bitmap indexed column (CORR_ITEM_DESC).

My question is, if more DML operation happend on bitmap indexed column then will it cause Deadlock?

Can I give suggestion to Application team to remove this bitmap index from this table? but still i couldn't come for the conclusion on this issue.

The issue about bitmap indexes only being useful for low cardinality columns is a myth. They care actually pretty useful for variety of situations. Richard Foote does a neat presentation where he makes the case that bitmap indexes should be the only type of indexes used in a data wharehouse.

The issue about bitmap indexes being bad for tables with large amounts of DML is true. They are not designed to be used on high transactions tables.

This is the same table as before, so there is a bitmap index on the table. Even though the column is not referenced in the DML, the removal of a row requires the index to be updated. Remember, all inserts and deletes will require the index to be amended. Updates that affect the indexed row will require the index to be amended.

I can't guarantee that, but having a bitmap index on table with high volumes of DML is a bad idea, so you should do it anyway. There are locks associated with bitmap indexes, so it is likely it will fix the issue.

Bitmap index has been created on CORR_ITEM_DESC column but above delete statement does not have this bitmap indexed column (CORR_ITEM_DESC) but I am not sure why we are getting Deadlock issue while above DELETE statement runs!

2. If really bitmap index is the problem, I can suggest application team to remove bitmap index but If i remove this index how do I find what kind of index we can create on this column? (my database runing on 10.2.0.4)

3. Is there any query to find Foreign key which is refrencing to a table.

1) The bitmap index is not being referenced by the delete, but the rows it indexes are still being deleted, so it is still having to update the index bitmap to take account of the rows that have been deleted. Using bitmap indexes on systems running DML is typically considered really bad. They are better for static data, snot volatile data. So data warehouses yes. OLTP no.

1a) Deleting data is really not a great idea if the proportion of data is large. Better to drop a partition, or create a new table with out the data and swap the table names.

2) If this is a warehouse and the data is typically static, you could drop the index, delete the data and recreate the index. If this is an OLTP system, or a warehouse that has lots of DML, then you should remove the bitmap index and consider building a regular b*tree index, which will cope fine with DML.