bitmap indexes performance

reachmexyz · Post by **reachmexyz** » Tue Mar 17, 2009 12:43 pm

Hello all

I have a fact table with aroung 12 bitmap indexes each on a individual field. Now the load process is very slow because of these indexes.
Is there any way i can disable all these indexes before loading and then enable later. Can i use OCI stage for this

kcbland · Post by **kcbland** » Tue Mar 17, 2009 12:52 pm

Oracle bitmap indexes are a well known issue with ETL. You need to drop/disable, load, and rebuild these indexes during your ETL cycle. You need to read up on bitmap indexes to understand why. The short answer is that a bitmap can have only a few entries that span the entire table. Inserting/updating causes significant index maintenance overhead, as you're experiencing.

reachmexyz · Post by **reachmexyz** » Tue Mar 17, 2009 1:00 pm

kcbland wrote:Oracle bitmap indexes are a well known issue with ETL. You need to drop/disable, load, and rebuild these indexes during your ETL cycle. You need to read up on bitmap indexes to understand why. The short answer is that a bitmap can have only a few entries that span the entire table. Inserting/updating causes significant index maintenance overhead, as you're experiencing.

I understand i can disable indexes before loading and then recreate after the load is complete.
Can i automate this disabling and enabling of indexes in OCI Stage.
If i have 12 indexes on a table can i disable 12 bitmap indexes in before sql on OCI and then later enable all the indexes on After SQL of OCI.
Can you please tell me how can i do it?

DSguru2B · Post by **DSguru2B** » Tue Mar 17, 2009 1:14 pm

Sure. We do that in our warehouse every night. Use the before/after sql tabs to drop and create indices respectively. For multiple drops and creates seperate the commands with double semi colons (;;).

chulett · Post by **chulett** » Tue Mar 17, 2009 2:37 pm

Your connection user most likely won't have permissions to do this, or really shouldn't. Have procs created to drop/rebuild them and then get permission to execute the procs, do so before- and after-sql.

reachmexyz · Post by **reachmexyz** » Fri Mar 20, 2009 11:50 am

DSguru2B wrote:Sure. We do that in our warehouse every night. Use the before/after sql tabs to drop and create indices respectively. For multiple drops and creates seperate the commands with double semi colons (;;).

I got the permissions to drop and create indexes and when i tried in before SQL, response is "invalid option".
I tried to execute the below command if my table name is xxx, schema name is sss
drop bitmap index index_name on sss.xxx;;
drop bitmap index index_name2 on sss.xxx;;

Can you please tell me whether the command i am using is syntatically wrong or there is any other way dropping bitmap indexes.

betterthanever · Post by **betterthanever** » Fri Mar 20, 2009 11:59 am

try in OPEN/CLOSE command.

Code: Select all

BEGIN
sql;
commit;
end;

reachmexyz · Post by **reachmexyz** » Fri Mar 20, 2009 12:04 pm

betterthanever wrote:try in OPEN/CLOSE command.
Code: Select all
BEGIN
sql;
commit;
end;

i am trying to do drop the indexes in before sql of OCI stage.
Can you please tell me what is this open/close command.
Also in the script does sql; means all the drop commands?

betterthanever · Post by **betterthanever** » Fri Mar 20, 2009 12:10 pm

i am also referring to the same before sql you are talking about in OCI stage...

just mentioned the syntax of using it.