Re: Function-based index on DML-active tables

On 04/05/2006 02:48:34 PM, Hameed, Amir wrote:
> Folks,
> I am trying to understand the impact of creating a function-based index
> on an order entry table which is heavily used by DML statements. Can
> anyone tell me the downside of creating such an index?
>

Amir, the function in function based index must be executed at least once for every row affected by a non-direct DML operation. If you have 10 millions DML operations on the columns involved per day (I invented that number), there will be 10,000,000 executions. From what you told us about your hardware, that shouldn't be a problem. Your database is a large data warehouse, which means that updates all clustered around the same time and, outside that window, the database is mostly queried. If you have DML in batches, and the rest is mostly querying, you can disable the index (ALTER INDEX <index name> UNUSABLE) for the duration of the update and then use it for querying. It all depends how much would that index help you for querying. Dropping all bit-mapped indexes and re-creating them after the DW is practically standard.

-- 
Mladen Gogala
http://www.mgogala.com

--
http://www.freelists.org/webpage/oracle-l