RE: local Bimap indexes -- thanks
Thanks Kirti for the suggestion..it works now. Sathish. On Mon, 14 Oct 2002 19:53:23 -0800, Deshpande, Kirti [EMAIL PROTECTED] said: Create logon triggers for these Informatica user id to alter session set... - Kirti -Original Message- Sent: Monday, October 14, 2002 7:09 PM To: Multiple recipients of list ORACLE-L Thats what we exactly did except that iniformatica starts a separate session when it is loading the target which results in the error unusable state . sathish On Mon, 14 Oct 2002 15:44:02 -0800, Khedr, Waleed [EMAIL PROTECTED] said: In Informatica there is a stored procedure transform (runs any Oracle SP) that you can make it pre source load. So the SP would be called before loading the table. Regards, Waleed -Original Message- Sent: Monday, October 14, 2002 7:04 PM To: Multiple recipients of list ORACLE-L Hello All, This problem pertains to local bitmap index in dw env. We are using informatica to load our fact tables. i have a fact table partitioned on period key (range partition). We have built local bitmap indexes on the foreign keys. During the loading of the fact table for month 1, we are making the local bitmap index partition pertaining to month 1 as UNUSABLE (through stored procedure being called at just before the target is being loaded). In that same procedure we also set ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE. Once informatica starts to load the target, it opens up a new session which in turn invalidates the alter session and hence oracle terminates with an error saying 0ra-1052 index in unusable state Please let me know if there is any workaround to this. Is droping and recreating the bitmap index the ONLY option ???. this fact table is going to grow to about 150 mill rows in 12 month time frame , avg row length being 100 bytes) oracle ver 9.2., TIA, sathish. -- http://fastmail.fm - The way an email service should be -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- http://fastmail.fm - Access all of your messages and folders wherever you are -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- http://fastmail.fm/ - A no graphics, no pop-ups email service -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services
RE: local Bimap indexes
In Informatica there is a stored procedure transform (runs any Oracle SP) that you can make it pre source load. So the SP would be called before loading the table. Regards, Waleed -Original Message- Sent: Monday, October 14, 2002 7:04 PM To: Multiple recipients of list ORACLE-L Hello All, This problem pertains to local bitmap index in dw env. We are using informatica to load our fact tables. i have a fact table partitioned on period key (range partition). We have built local bitmap indexes on the foreign keys. During the loading of the fact table for month 1, we are making the local bitmap index partition pertaining to month 1 as UNUSABLE (through stored procedure being called at just before the target is being loaded). In that same procedure we also set ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE. Once informatica starts to load the target, it opens up a new session which in turn invalidates the alter session and hence oracle terminates with an error saying 0ra-1052 index in unusable state Please let me know if there is any workaround to this. Is droping and recreating the bitmap index the ONLY option ???. this fact table is going to grow to about 150 mill rows in 12 month time frame , avg row length being 100 bytes) oracle ver 9.2., TIA, sathish. -- http://fastmail.fm - The way an email service should be -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: local Bimap indexes
Thats what we exactly did except that iniformatica starts a separate session when it is loading the target which results in the error unusable state . sathish On Mon, 14 Oct 2002 15:44:02 -0800, Khedr, Waleed [EMAIL PROTECTED] said: In Informatica there is a stored procedure transform (runs any Oracle SP) that you can make it pre source load. So the SP would be called before loading the table. Regards, Waleed -Original Message- Sent: Monday, October 14, 2002 7:04 PM To: Multiple recipients of list ORACLE-L Hello All, This problem pertains to local bitmap index in dw env. We are using informatica to load our fact tables. i have a fact table partitioned on period key (range partition). We have built local bitmap indexes on the foreign keys. During the loading of the fact table for month 1, we are making the local bitmap index partition pertaining to month 1 as UNUSABLE (through stored procedure being called at just before the target is being loaded). In that same procedure we also set ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE. Once informatica starts to load the target, it opens up a new session which in turn invalidates the alter session and hence oracle terminates with an error saying 0ra-1052 index in unusable state Please let me know if there is any workaround to this. Is droping and recreating the bitmap index the ONLY option ???. this fact table is going to grow to about 150 mill rows in 12 month time frame , avg row length being 100 bytes) oracle ver 9.2., TIA, sathish. -- http://fastmail.fm - The way an email service should be -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- http://fastmail.fm - Access all of your messages and folders wherever you are -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: local Bimap indexes
Create logon triggers for these Informatica user id to alter session set... - Kirti -Original Message- Sent: Monday, October 14, 2002 7:09 PM To: Multiple recipients of list ORACLE-L Thats what we exactly did except that iniformatica starts a separate session when it is loading the target which results in the error unusable state . sathish On Mon, 14 Oct 2002 15:44:02 -0800, Khedr, Waleed [EMAIL PROTECTED] said: In Informatica there is a stored procedure transform (runs any Oracle SP) that you can make it pre source load. So the SP would be called before loading the table. Regards, Waleed -Original Message- Sent: Monday, October 14, 2002 7:04 PM To: Multiple recipients of list ORACLE-L Hello All, This problem pertains to local bitmap index in dw env. We are using informatica to load our fact tables. i have a fact table partitioned on period key (range partition). We have built local bitmap indexes on the foreign keys. During the loading of the fact table for month 1, we are making the local bitmap index partition pertaining to month 1 as UNUSABLE (through stored procedure being called at just before the target is being loaded). In that same procedure we also set ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE. Once informatica starts to load the target, it opens up a new session which in turn invalidates the alter session and hence oracle terminates with an error saying 0ra-1052 index in unusable state Please let me know if there is any workaround to this. Is droping and recreating the bitmap index the ONLY option ???. this fact table is going to grow to about 150 mill rows in 12 month time frame , avg row length being 100 bytes) oracle ver 9.2., TIA, sathish. -- http://fastmail.fm - The way an email service should be -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- http://fastmail.fm - Access all of your messages and folders wherever you are -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: local Bimap indexes
Why do you have to make the indexes unusable? Can't you do a direct load into the table (nologging mode)? Waleed -Original Message- To: Multiple recipients of list ORACLE-L Sent: 10/14/02 7:08 PM Thats what we exactly did except that iniformatica starts a separate session when it is loading the target which results in the error unusable state . sathish On Mon, 14 Oct 2002 15:44:02 -0800, Khedr, Waleed [EMAIL PROTECTED] said: In Informatica there is a stored procedure transform (runs any Oracle SP) that you can make it pre source load. So the SP would be called before loading the table. Regards, Waleed -Original Message- Sent: Monday, October 14, 2002 7:04 PM To: Multiple recipients of list ORACLE-L Hello All, This problem pertains to local bitmap index in dw env. We are using informatica to load our fact tables. i have a fact table partitioned on period key (range partition). We have built local bitmap indexes on the foreign keys. During the loading of the fact table for month 1, we are making the local bitmap index partition pertaining to month 1 as UNUSABLE (through stored procedure being called at just before the target is being loaded). In that same procedure we also set ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE. Once informatica starts to load the target, it opens up a new session which in turn invalidates the alter session and hence oracle terminates with an error saying 0ra-1052 index in unusable state Please let me know if there is any workaround to this. Is droping and recreating the bitmap index the ONLY option ???. this fact table is going to grow to about 150 mill rows in 12 month time frame , avg row length being 100 bytes) oracle ver 9.2., TIA, sathish. -- http://fastmail.fm - The way an email service should be -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- http://fastmail.fm - Access all of your messages and folders wherever you are -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
