-- create index

-- Examples:
CREATE INDEX DBSTARADMIN.aix_payment_invoice_id
ON  DBSTARADMIN.payment(invoice_id)
TABLESPACE user_data PCTFREE 10 
   STORAGE ( INITIAL 4M NEXT 1M MAXEXTENTS UNLIMITED PCTINCREASE 0)
unrecoverable;

CREATE INDEX ADMIN.aix_cnv_attendance
ON  ADMIN.cnv_attendance(APPLICANT_ID,CHILD_SUFFIX)
TABLESPACE DBSTAR_IX PCTFREE 10 
   STORAGE ( INITIAL 1M NEXT 1M MAXEXTENTS UNLIMITED PCTINCREASE 0)
unrecoverable;

CREATE INDEX ADMIN.aix2_vendor
ON  ADMIN.vendor(R3_VENDOR_NUMBER)
TABLESPACE DBSTAR_IX PCTFREE 10 
   STORAGE ( INITIAL 2M NEXT 5M MAXEXTENTS UNLIMITED PCTINCREASE 0)
unrecoverable;

CREATE INDEX ADMIN.XDBSTAR_REQITEM
ON  ADMIN.DBSTAR_REQUESTED_ITEM(VENDOR_ID,PROGRAM_ID,req_type_id,req_status,REQ_ID)
TABLESPACE DBSTAR_REQUEST_IDX PCTFREE 10 
   STORAGE ( INITIAL 10M NEXT 10M MAXEXTENTS UNLIMITED PCTINCREASE 0)
unrecoverable;

CREATE INDEX ADMIN.XDBSTAR_REQITEM
ON  ADMIN.DBSTAR_REQUESTED_ITEM(VENDOR_ID,req_status)
TABLESPACE DBSTAR_REQUEST_IDX PCTFREE 10 
   STORAGE ( INITIAL 10M NEXT 10M MAXEXTENTS UNLIMITED PCTINCREASE 0)
unrecoverable;


CREATE INDEX ADMIN.AIX_POSTAL_ADDRESS_ALL
ON  ADMIN.POSTAL_ADDRESS(street_name,from_street_no,to_street_no,odd_even_both,city_id,province_id)  
TABLESPACE DBSTAR_IX PCTFREE 20 
   STORAGE ( INITIAL 1M NEXT 1M MAXEXTENTS UNLIMITED PCTINCREASE 30)
unrecoverable;

CREATE INDEX ADMIN.AIX_m128
ON  ADMIN.cnv_m128(requisition_number)  
TABLESPACE DBSTAR_IX PCTFREE 0 
   STORAGE ( INITIAL 100M NEXT 20M MAXEXTENTS UNLIMITED )
unrecoverable;

-- create unique index
CREATE unique INDEX ADMIN.uaix_payment
ON  ADMIN.payment(transaction_id)  
TABLESPACE DBSTAR_IX PCTFREE 20 
   STORAGE ( INITIAL 20M NEXT 10M MAXEXTENTS UNLIMITED PCTINCREASE 30)
unrecoverable;

CREATE unique INDEX ADMIN.uaix_cnv_table_type
ON  ADMIN.cnv_table_type(column_name)  
TABLESPACE DBSTAR_IX PCTFREE 20 
   STORAGE ( INITIAL 1M NEXT 1M MAXEXTENTS UNLIMITED PCTINCREASE 30)
unrecoverable;

-- create a function based index
CREATE INDEX ADMIN.CARD_APPLICATION_ix4 on 
ADMIN.CARD_APPLICATION (UPPER(filenumber))
tablespace ADMIN_IDXD;
@gather_table_stats ADMIN CARD_APPLICATION