Bit Indexing partitioned columns

[goodmans]

Hi I have a big table holding 1b rows. It is partitioned by a column contains 20 distinct values.

Its taking 21 hrs to update one partition with 2m rows. I have a bitmap index on this table as well. I dont know why its taking that long?. I am guessing that bitmap index is not needed on partitioned column, But not sure. Please advise me.

Do I need bitmapindex on partitioned column or is expensive having bitmap index on this column?

Regards,
G

 

[jaxtell]

Goodman,
Is this in a OLTP, or data warehouse? I know the guy here drops bitmap indexes, runs the update, then recreates the indexes. He says its faster. Bitmap indexes probably aren't appropriate for high transaction environments. But don't take my word for it, I'm not a DBA. I hope this helps.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[goodmans]

Its is for DWH. Thanks for help.

I am using Informatica to update the target But Informatica sends 1 statement for each. And I dont know how efficient of bulk load.

Update statment:
UPDATE TABLE TABLE_SALES SET REC_FG='N', END_DATE =SYSDATE
WHERE LOC=10010 AND ID=1999;

Table is partitioned by LOC and there is a unique key index available on ID.

I know by using parallel hint on update statment will fasten the job. But I have 20 parallel updates goin on each partitiion, so I dont want to occupy all the processors. How can I improve the performance of the above update statement with out using parallel hint.

Regarads
G

 

[jaxtell]

I think if the bitmap index only contains the same column the table is partition on, it is probably unnecessary. I also think that if informatica is updating 1 row at a time, parallel isn't a factor. I think your best bet is probably to remove the bitmap index and change the multiple updates to a single update for many rows, if possible.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[Thargy]

goodmans,

I have had problems with bitmap indexes before. They're great for read, but bad for writing, as you have to update the entire index and you can't use it to read until the update has finished.

You might try local indexes on the partitions (unless of course you already have them). I believe that they can offer performance boosts over global indexes on the table.

Regards

T