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
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rusnak, George A.
INET: [EMAIL PROTECTED]
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: [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).
