Performance - date queries (Oracle-specific)

[support]

On Oracle-based i2b2, the performance on date-constrained queries in
1.3 can be improved by adding a new index that includes the
start_date, then renaming the original index so that the hint in the
SQL generated by the hive does not force the query away from the new
index.

Example:
create index FACT_CNPT_PAT_ENCT_IDX_DATE on observation_fact
(concept_cd, start_date)
tablespace i2b2_data storage (initial 9G next 500M)
/

ALTER INDEX FACT_CNPT_PAT_ENCT_IDX RENAME TO
FACT_CNPT_PAT_ENCT_IDX_ORIG
/

Relevant statistics:
Facts: 220 million
Time to build index: ~30 minutes
Query: "Patients with at least 2 diagnoses of diabetes since 1/1/2008"

Time to run before new index: >3 minutes (timeout)
Time to run after new index: 8 seconds (31,032 records found)

This performance problem was not found in MS SQL.