bitmap indexes performance
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 296
- Joined: Sun Nov 16, 2008 7:41 pm
bitmap indexes performance
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
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
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Premium Member
- Posts: 296
- Joined: Sun Nov 16, 2008 7:41 pm
I understand i can disable indexes before loading and then recreate after the load is complete.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.
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?
-
- Premium Member
- Posts: 296
- Joined: Sun Nov 16, 2008 7:41 pm
I got the permissions to drop and create indexes and when i tried in before SQL, response is "invalid option".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 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.
-
- Participant
- Posts: 152
- Joined: Tue Jan 13, 2009 8:59 am
-
- Premium Member
- Posts: 296
- Joined: Sun Nov 16, 2008 7:41 pm
-
- Participant
- Posts: 152
- Joined: Tue Jan 13, 2009 8:59 am