You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 12 Next »

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. I ran the queries against Dev (with the new indexes in place) and Test (no new indexes in place) and recorded the optimizer cost for each query  as reported by the Explain Plan tool.

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
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
3. Batch Jobs

Some batch jobs that do bulk insert/update operations on SPRIDEN need to be tested to make sure they are not adversely affected by the new indexes. These are:

  • MITID - Ross used to support this
  • Admissions load - Ben
  • Test score load -  Monica
  • Current person load - ???? DBA's
  • No labels