...
The new indexes are as follows;
| Code Block |
|---|
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 |
|---|
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.
...
| Code Block |
|---|
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')
)
|
| No Format |
|---|
ostCost (dev): 3 Cost (test): 2283 |
| Code Block |
|---|
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
| Code Block |
|---|
select s.first_name, s.last_name
from tbvsp_sponsor s
where last_name like
'Smi%'
and id like '%';
|
| No Format |
|---|
Cost (dev): 4 Cost (test): 4 |
– SOAIDEN
| Code Block |
|---|
-- 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 '%';
|
...