From joni_65@yahoo.com Mon, 01 Oct 2001 13:34:01 -0700 From: Johnson Poovathummoottil Date: Mon, 01 Oct 2001 13:34:01 -0700 Subject: Re: Partition tables: Indexes In-Reply-To: Message-ID: MIME-Version: 1.0 Content-Type: text/plain There is SKIP_UNUSABLE_INDEXES option in SQLldr --- "Rusnak, George A." wrote: > Group, > I have a partition that has < 1 million records that > is joined to 4 other > tables. In order to get an acceptable response time > I added two additional > indexes. The response time is now acceptable but the > load time is > unacceptable. I truncate the partition prior to the > load. Is there any way > to drop/set unusable the local index on the > partition being loaded, load the > data and then rebuild the local index? > If so PLEASE give me an example !! I am just about > burned out on reading the > docs !!! > My table structure: > CREATE TABLE scan_contract > (CONTRACT_BEGIN_DATE DATE, > NSN VARCHAR2(13) , > CONTRACT VARCHAR2(14) NOT NULL, > CONTRACT_END_DATE DATE, > FUTURE_EFF_DATE DATE, > FUTURE_SELL_PRICE NUMBER(11,4), > SELL_PRICE NUMBER(8,2), > UPDATE_DATE DATE, > DODAAC VARCHAR2(6), > VENDOR_NUMBER VARCHAR2(4), > ITEM_UPC VARCHAR2(14), > REGION_FFS VARCHAR2(3) > ) > partition by range (REGION_FFS) > SUBPARTITION BY HASH(DODAAC) > SUBPARTITIONS 10 > (partition CONTRACT_1 Values less than > ('RGC') > tablespace contract_ffs_1, > partition CONTRACT_2 Values less than > ('RGD') > tablespace contract_ffs_2, > partition CONTRACT_3 Values less than > ('RGE') > tablespace contract_ffs_3, > partition CONTRACT_4 Values less than > ('RGF') > tablespace contract_ffs_4, > partition CONTRACT_5 Values less than > ('RGG') > tablespace contract_ffs_5, > partition CONTRACT_6 Values less than > ('RGH') > tablespace contract_ffs_6 > ) > PCTFREE 10 > PCTUSED 40 > INITRANS 1 > MAXTRANS 255 > TABLESPACE scan_down_ts > NOLOGGING > STORAGE(INITIAL 5M > NEXT 2M > MINEXTENTS 1 > MAXEXTENTS 99 > PCTINCREASE 0); > Create index scan_contract_idx1 > ON scan_contract(REGION_FFS, > NSN, > dodaac) STORAGE (initial 10K) > LOCAL > (partition CONTRACT_1 TABLESPACE contract_ffs_1, > partition CONTRACT_2 TABLESPACE contract_ffs_2, > partition CONTRACT_3 TABLESPACE contract_ffs_3, > partition CONTRACT_4 TABLESPACE contract_ffs_4, > partition CONTRACT_5 TABLESPACE contract_ffs_5, > partition CONTRACT_6 TABLESPACE contract_ffs_6); > Create index scan_contract_item_upc_idx > ON scan_contract(ITEM_UPC) STORAGE (initial 25K) > LOCAL > (partition CONTRACT_1 TABLESPACE contract_ffs_1, > partition CONTRACT_2 TABLESPACE contract_ffs_2, > partition CONTRACT_3 TABLESPACE contract_ffs_3, > partition CONTRACT_4 TABLESPACE contract_ffs_4, > partition CONTRACT_5 TABLESPACE contract_ffs_5, > partition CONTRACT_6 TABLESPACE contract_ffs_6); > Create index scan_contract_contract_idx > ON scan_contract(CONTRACT) STORAGE (initial 25K) > LOCAL > (partition CONTRACT_1 TABLESPACE contract_ffs_1, > partition CONTRACT_2 TABLESPACE contract_ffs_2, > partition CONTRACT_3 TABLESPACE contract_ffs_3, > partition CONTRACT_4 TABLESPACE contract_ffs_4, > partition CONTRACT_5 TABLESPACE contract_ffs_5, > partition CONTRACT_6 TABLESPACE contract_ffs_6); > > TIA > > Al Rusnak > 804-734-8453 > rusnakga@hqlee.deca.mil > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Rusnak, George A. > INET: rusnakga@hqlee.deca.mil > > Fat City Network Services -- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California -- Public Internet > access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an > E-Mail message > to: ListGuru@fatcity.com (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). __________________________________________________ Do You Yahoo!? Listen to your Yahoo! Mail messages from any phone. http://phone.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson Poovathummoottil INET: joni_65@yahoo.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (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).