upd_indx.sql: update indexes

Author: Robert Lawson
Environment: Oracle database, HPUX
Description: Sample script to update database indexes. This would be used in test instance and then ultimately in production.
Code Listing:

/* program: upd_indx.sql
   date   : 08/13/96
   author : Robert Lawson
   purpose: Mass update of indexes
*/
set termout on
set echo on
-- set heading off
-- set linesize 132
-- set feedback off
-- set pagesize 60
/*
-- /appl/jit/src/CUST302/ddl/table_q/alt_quote_model_dtl.sql
-- redoes this index with line_no
DROP INDEX QUOTE_MODEL_DETAIL_INDX1;
CREATE INDEX QUOTE_MODEL_DETAIL_INDX1
 ON QUOTE_MODEL_DETAIL (QUOTE_NO, OEDETL_LINE_NO, plant)
 tablespace JITINDX
 storage ( initial     5M   next       1M
           minextents     1 maxextents   121
           pctincrease    0 );
*/
-- new index
DROP INDEX QUOTE_MODEL_DETAIL_IDX3;
CREATE INDEX QUOTE_MODEL_DETAIL_IDX3
 ON QUOTE_MODEL_DETAIL (parent_no, plant)
 tablespace JITINDX
 storage ( initial      3M   next       1M
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_b/alt_bom_log.sql (new)
-- only index! bmli
DROP INDEX BOM_LOG_IDX1;
CREATE INDEX BOM_LOG_IDX1
 ON BILL_OF_MATERIAL_LOG (parent_no, parent_plant)
 tablespace JITINDX
 storage ( initial      3M   next       1M
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_b/alt_blownup_bill.sql (new)
-- only index!
DROP INDEX BLOWNUP_BILL_IDX1;
CREATE INDEX BLOWNUP_BILL_IDX1
 ON BLOWNUP_BILL (plant , order_no, component_no)
 tablespace JITINDX
 storage ( initial        3M   next       1M
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_p/alt_po_line_comm.sql (new)
-- only index!
DROP INDEX PO_LINE_COMM_IDX1;
CREATE INDEX PO_LINE_COMM_IDX1
 ON PO_LINE_COMM (plant , po_no, line_no, comment_seq)
 tablespace JITINDX
 storage ( initial       1M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_p/alt_po_blanket.sql (new)
-- added line to index
DROP INDEX PO_BLANKET_IDX;
CREATE INDEX PO_BLANKET_IDX
 ON PO_BLANKET (plant , po_no, line_no)
 tablespace JITINDX
 storage ( initial       1M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_o/alter_oesummary.sql
-- added index for customer
DROP INDEX OESUMMARY_IDX1;
CREATE INDEX OESUMMARY_IDX1
 ON OESUMMARY (cust_no, order_no, order_suffix)
 tablespace JITINDX
 storage ( initial      2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_o/alt_oe_entry_register.sql (new)
-- added index for customer
DROP INDEX OE_ENTRY_REG_IDX1;
CREATE INDEX OE_ENTRY_REG_IDX1
 ON OE_ENTRY_REGISTER (order_no, order_suffix, plant)
 tablespace JITINDX
 storage ( initial        1M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_r/alt_requisition.sql (new)
-- added index for vendor
DROP INDEX REQUISITION_IDX4;
CREATE INDEX REQUISITION_IDX4
 ON REQUISITION (plant, vendno, req_no, req_line_no)
 tablespace JITINDX
 storage ( initial       2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- added index for vendor
DROP INDEX REQUISITION_IDX5;
CREATE INDEX REQUISITION_IDX5
 ON REQUISITION (plant, requested_by, req_no, req_line_no)
 tablespace JITINDX
 storage ( initial      2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- added index for buyer
DROP INDEX REQUISITION_IDX6;
CREATE INDEX REQUISITION_IDX6
 ON REQUISITION (plant, buyer_code, req_no, req_line_no)
 tablespace JITINDX
 storage ( initial       2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_r/alt_req_header.sql (new)

-- added index for requestor
DROP INDEX REQ_HEADER_IDX2;
CREATE INDEX REQ_HEADER_IDX2
 ON REQ_HEADER (plant, requested_by, req_no)
 tablespace JITINDX
 storage ( initial      2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_p/alt_po_header.sql (new)
-- added index for vendor
DROP INDEX PO_HEADER_IDX2;
CREATE INDEX PO_HEADER_IDX2
 ON PO_HEADER (plant, vendno, req_no)
 tablespace JITINDX
 storage ( initial       2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- added index for requestor
DROP INDEX PO_HEADER_IDX3;
CREATE INDEX PO_HEADER_IDX3
 ON PO_HEADER (plant, buyer_code, req_no)
 tablespace JITINDX
 storage ( initial      2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_c/alt_custcntrcts.sql (new)
-- added customer
DROP INDEX CUSTCNTRCTS_IDX1;
CREATE INDEX CUSTCNTRCTS_IDX1
 ON CUSTCNTRCTS (cust_no, item_no, plant)
 tablespace JITINDX
 storage ( initial      500K   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_a/alt_ar_cia_invno.sql (new)
-- added index
DROP INDEX AR_CIA_INVNO_IDX1;
CREATE INDEX AR_CIA_INVNO_IDX1
 ON AR_CIA_INVNO (org_unit_id, order_no)
 tablespace JITINDX
 storage ( initial      500K   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- added index
DROP INDEX AR_CIA_INVNO_IDX2;
CREATE INDEX AR_CIA_INVNO_IDX2
 ON AR_CIA_INVNO (org_unit_id, cia_invno)
 tablespace JITINDX
 storage ( initial      500K   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );