...
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;
| Code Block |
|---|
{code} 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" ; { |
| Code Block |
|---|
} {code} 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
| Code Block |
|---|
{code} h3. 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. h6. 1. Queries from csf-common-legacy unit tests CSF Common Legacy Unit Tests {code} 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%') {code} {noformat} |
| No Format |
|---|
Cost (dev): 11 Cost (test): 3617 |
| Code Block |
|---|
{noformat} {code} 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%') {code} {noformat} |
| No Format |
|---|
Cost (dev): 13 Cost (test): 3617 |
| Code Block |
|---|
{noformat} {code} 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')) {code} {noformat} |
| No Format |
|---|
Cost (dev): 3
Cost (test): 2283
{noformat}
h6. |
2.
...
Queries
...
from
...
Oracle
...
forms:
...
--SOACOMP
| Code Block |
|---|
} select s.first_name, s.last_name from tbvsp_sponsor s where last_name like 'Smi%' and id like '%'; {code} {noformat} |
| No Format |
|---|
Cost (dev): 4 Cost (test): 4 |
– SOAIDEN
| Code Block |
|---|
{noformat} -- SOAIDEN {code} 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 '%'; {code} {noformat} |
| No Format |
|---|
Cost (dev): 7 Cost (test): 8 |
| Code Block |
|---|
{noformat} {code} 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'; {code} {noformat} |
| No Format |
|---|
Cost (dev): 4 Cost (test): 4 |
| Code Block |
|---|
{noformat} {code} 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; {code} {noformat} |
| No Format |
|---|
Cost (dev): 8
Cost (test): 139
{noformat}
h6. |
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
...
Tests
...
had
...
been
...
co-ordinated
...
with
...
developers
...
and
...
DBA
...
for
...
the
...
above
...
batch
...
jobs
...
and
...
functions.
...
The
...
test
...
results
...
shows
...
that
...
all
...
jobs
...
and
...
functions
...
completed
...
successfully
...
with
...
no
...
to
...
minimal
...
difference
...
in
...
performance
...
when
...
compared
...
to
...
the
...
metrics
...
of
...
the
...
production
...
run.
...
The
...
following
...
is
...
some
...
details
...
about
...
the
...
run:
...
- Test
...
- Scores
...
- Load
...
- -
...
- Monica
...
- used
...
- the
...
- largest
...
- input
...
- file
...
- (which
...
- is
...
- about
...
- 100
...
- times
...
- larger
...
- than
...
- regular
...
- daily
...
- run)
...
- in
...
- the
...
- current
...
- year
...
- for
...
- the
...
- test
...
- score
...
- load
...
- in
...
- TEST.
...
- It
...
- takes
...
- about
...
- 13
...
- minutes
...
- to
...
- finish,
...
- which
...
- is
...
- normal
...
- according
...
- to
...
- her.
...
- UA
...
- Load
...
- -
...
- The
...
- uaload
...
- completed
...
- in
...
- about
...
- 18
...
- minutes
...
- with
...
- the
...
- data
...
- file
...
- provided
...
- by
...
- Ross
...
- in
...
- Dev,
...
- with
...
- no
...
- errors.
...
- MIT
...
- ID
...
- -
...
- The
...
- mit
...
- id
...
- processed
...
- around
...
- 1600
...
- records
...
- in
...
- 24
...
- minutes
...
- in
...
- Dev
...
- environment,
...
- it
...
- is
...
- about
...
- the
...
- same
...
- in
...
- performance
...
- from
...
- what
...
- I
...
- remembered.
...
- Current
...
- Person
...
- Loads
...
- -
...
- DBA
...
- conducted
...
- the
...
- tests
...
- for
...
- us.
...
- According
...
- the
...
- information
...
- provided
...
- by
...
- them,
...
- all
...
- jobs
...
- successfully
...
- completed
...
- in
...
- Test
...
- except
...
- SXP_UPDATE_FROM_DW_KRBPERSON.
...
- All
...
- jobs
...
- finished
...
- within
...
- minutes
...
- and
...
- took
...
- about
...
- the
...
- same
...
- time
...
- when
...
- compared
...
- to
...
- production
...
- runs
...
- statistics
...
- SATURN.SXP_UPDATE_FROM_DW_KRBPERSON
...
- -
...
- the
...
- script
...
- was
...
- later
...
- updated
...
- with
...
- the
...
- latest
...
- changes
...
- to
...
- resolve
...
- the
...
- failure
...
- due
...
- to
...
- changes
...
- to
...
- the
...
- data
...
- warehouse
...
- tables.
...
- The
...
- first
...
- run
...
- of
...
- the
...
- script
...
- in
...
- Test
...
- SATURN.SXP_UPDATE_FROM_DW_KRBPERSON
...
- took
...
- 5+
...
- hours,
...
- but
...
- subsequent
...
- runs
...
- were
...
- in
...
- line
...
- with
...
- the
...
- time
...
- taken
...
- in
...
- daily
...
- production
...
- runs.
...
- Semyon
...
- and
...
- Raia
...
- had
...
- looked
...
- into
...
- the
...
- 5
...
- hours
...
- run
...
- and
...
- discovered
...
- that
...
- the
...
- script
...
- itself
...
- does
...
- not
...
- use
...
- SPRIDEN
...
- at
...
- all.
...
- They
...
- are
...
- looking
...
- into
...
- whether
...
- the
...
- long
...
- elapse
...
- time
...
- of
...
- the
...
- first
...
- job
...
- was
...
- due
...
- to
...
- the
...
- fact
...
- there
...
- might
...
- be
...
- occasional
...
- big
...
- volume
...
- of
...
- data
...
- in
...
- a
...
- day
...
- and
...
- that
...
- the
...
- job
...
- had
...
- not
...
- been
...
- run
...
- for
...
- a
...
- while
...
- in
...
- TEST.
...
- But
...
- as
...
- it
...
- did
...
- not
...
- reference
...
- SPRIDEN
...
- at
...
- all,
...
- DBA
...
- concluded
...
- that
...
- the
...
- additional
...
- new
...
- indexes
...
- to
...
- SPRIDEN
...
- do
...
- not
...
- have
...
- any
...
- impact
...
- on
...
- the
...
- SXP_UPDATE_FROM_DW_KRBPERSON
...
- job.