Partition tables: Indexes

From: Rusnak, George A. <rusnakga_at_hqlee.deca.mil>
Date: Mon, 01 Oct 2001 12:09:31 -0700
Message-ID: <F001.0039DD3E.20011001121026@fatcity.com>

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).

Received on Mon Oct 01 2001 - 14:09:31 CDT