New SPRIDEN Indexes

To support fast searching for students by first & last name, we are adding new function-based indexes to the SPRIDEN table. Because this table is central to MITSIS, we need to conduct tests to make sure the new indexes do not have an adverse effect on existing programs.

The Indexes

The new indexes are as follows;

CREATE INDEX "SATURN"."SPRIDEN_UPPER_FIRST_LAST_IDX" ON "SATURN"."SPRIDEN" (UPPER("SPRIDEN_FIRST_NAME"), UPPER("SPRIDEN_LAST_NAME"))
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SSIT_INDEX2" ;

  CREATE INDEX "SATURN"."SPRIDEN_UPPER_LAST_FIRST_IDX" ON "SATURN"."SPRIDEN" (UPPER("SPRIDEN_LAST_NAME"), UPPER("SPRIDEN_FIRST_NAME"))
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SSIT_INDEX2" ;

Testing

I tracked down several existing SQL queries to see how they were affected by the new indexes.

1. Queries from csf-common-legacy unit tests

CSF Common Legacy Unit Tests

select
        student0_.PIDM as PIDM41_,
        student0_.MIT_ID as MIT2_41_,
        student0_.LAST_NAME as LAST3_41_,
        student0_.FIRST_NAME as FIRST4_41_,
        student0_.MIDDLE_NAME as MIDDLE5_41_,
        student0_.SEX as SEX41_,
        student0_.CITIZENSHIP as CITIZENS7_41_,
        student0_.BIRTH_DATE as BIRTH8_41_,
        student0_.KRB_NAME as KRB9_41_
    from
        SPV_STUDENT_VIEW student0_
    where
        upper(student0_.LAST_NAME)=upper('Joh%')

Cost (dev): 11
Cost (test): 3617

 select
        student0_.PIDM as PIDM41_,
        student0_.MIT_ID as MIT2_41_,
        student0_.LAST_NAME as LAST3_41_,
        student0_.FIRST_NAME as FIRST4_41_,
        student0_.MIDDLE_NAME as MIDDLE5_41_,
        student0_.SEX as SEX41_,
        student0_.CITIZENSHIP as CITIZENS7_41_,
        student0_.BIRTH_DATE as BIRTH8_41_,
        student0_.KRB_NAME as KRB9_41_ 
    from
        SPV_STUDENT_VIEW student0_ 
    where
        upper(student0_.FIRST_NAME)=upper('Ale%')  
   

Cost (dev): 13
Cost (test): 3617

select

        student0_.PIDM as PIDM41_,
        student0_.MIT_ID as MIT2_41_,
        student0_.LAST_NAME as LAST3_41_,
        student0_.FIRST_NAME as FIRST4_41_,
        student0_.MIDDLE_NAME as MIDDLE5_41_,
        student0_.SEX as SEX41_,
        student0_.CITIZENSHIP as CITIZENS7_41_,
        student0_.BIRTH_DATE as BIRTH8_41_,
        student0_.KRB_NAME as KRB9_41_ 
    from
        SPV_STUDENT_VIEW student0_ 
    where
        (
            upper(student0_.FIRST_NAME) like upper('An%')
        ) 
        and (
            upper(student0_.LAST_NAME) like upper('*ohansson')
        )

Cost (dev): 3
Cost (test): 2283

select        student0_.PIDM as PIDM41_,        student0_.MIT_ID as MIT2_41_,        student0_.LAST_NAME as LAST3_41_,        student0_.FIRST_NAME as FIRST4_41_,        student0_.MIDDLE_NAME as MIDDLE5_41_,        student0_.SEX as SEX41_,        student0_.CITIZENSHIP as CITIZENS7_41_,        student0_.BIRTH_DATE as BIRTH8_41_,        student0_.KRB_NAME as KRB9_41_     from        SPV_STUDENT_VIEW student0_     where        (            upper(student0_.FIRST_NAME) like upper('An%')        )         and (            upper(student0_.LAST_NAME) like upper('*ohansson')        )

2. Queries from Oracle forms:

--SOACOMP
select s.first_name, s.last_name
               from tbvsp_sponsor s
              where  last_name like
                     'Smi%'
                 and id like '%';

Cost (dev): 4
Cost (test): 4

-- SOAIDEN
           select spriden_pidm, spriden_id, spriden_first_name
               from spriden
               where spriden_id_type = 'S'
                 and spriden_last_name like 'Smi%'
                 and spriden_first_name like '%'
                 and spriden_id like '%';

Cost (dev): 7
Cost (test): 8

                 
      select spriden_pidm, spriden_first_name
               from spriden
               where spriden_id_type = 'S'
                 and spriden_last_name like '%'
                 and spriden_first_name like '%'
                 and spriden_id like '610562178';

Cost (dev): 4
Cost (test): 4

                 
     SELECT SPRIDEN_PIDM,SPRIDEN_ID,SPRIDEN_LAST_NAME,SPRIDEN_FIRST_NAME, SPRIDEN_MI
        FROM SPRIDEN
       WHERE SPRIDEN_LAST_CAPS LIKE 'JOH%'
         AND SPRIDEN_CHANGE_IND IS NULL ORDER BY SPRIDEN_LAST_NAME, SPRIDEN_FIRST_NAME,SPRIDEN_MI;             
                 

Cost (dev): 8
Cost (test): 139