There is SKIP_UNUSABLE_INDEXES option in SQLldr
- "Rusnak, George A." <rusnakga_at_hqlee.deca.mil>
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_at_hqlee.deca.mil
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Rusnak, George A.
> INET: rusnakga_at_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_at_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_at_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_at_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).
Received on Mon Oct 01 2001 - 15:34:01 CDT