Page History
...
Note | ||
---|---|---|
| ||
The script temporarily truncates the i2b2 table and repopulates it from a backup. This should be kept in mind if local modifications of an i2b2 installations installation include triggers on this table. The script creates and drops 2 temporary tables during execution. The script should be invoked with the user credentials of the schema containing the i2b2 ontology table. |
...
Index optimization of the OBSERVATION_FACT table
Column indexes are a long established method to improve query performance by speeding up the selection of relevant rows as well as join operations. In order to achieve this effect, the distinct values of selected table columns are stored separately, together with pointers to table rows containing each value. The database can leverage the often smaller size of these index structures to more quickly locate relevant data rows, instead of having to scan the full original table. Indexes are often stored in specialized structures (e.g. binary trees) which are optimized for quick access. Indexes can be constructed on single columns as well as multiple columns (composite indexes), which are concatenated to form the actual index value. Composite indexes provide an additional performance benefit when multiple columns are queried that are all part of the composite index, as only a single index structure needs to be examined, instead of several. However, composite indexes can only be applied if the provided search criteria are matching the order of columns in the index (e.g. if a composite index is concatenated out of columns A, B and C, it cannot be applied if column A is not part of the query). Whether the database actually uses indexes or not is decided by the query optimizer at runtime, depending on table statistics and cached performance data from previous queries, among others. The query strategy actually chosen can be examined at runtime (e.g. with the Oracle EXPLAIN PLAN statement) or by inspecting query logs.
The OBSERVATION_FACT table as it is setup in a default i2b2 installation already contains several indexes for query optimization:
Index Name | Task | Contained columns |
---|---|---|
OBSERVATION_FACT_PK | primary key | ENCOUNTER_NUM, CONCEPT_CD, PROVIDER_ID, START_DATE |
FACT_CNPT_PAT_ENCT_IDX | optimize queries by concept code | CONCEPT_CD, INSTANCE_NUM, PATIENT_NUM, ENCOUNTER_NUM |
FACT_NOLOB | optimize queries by patient id and start date | PATIENT_NUM, START_DATE, CONCEPT_CD, ENCOUNTER_NUM |
FACT_PATCON_DATE_PRVD_IDX | optimize queries by patient id and concept code | PATIENT_NUM, CONCEPT_CD, START_DATE, END_DATE |
OF_CTX_BLOB | optimize fulltext queries to BLOB column | OBSERVATION_BLOB |
asd