Details
-
Bug
-
Status: New
-
Minor
-
Resolution: Unresolved
-
None
-
None
-
None
-
Oracle 11.2.0.4.0 64bit
I2B2: 1.7.08b
-
Queries
-
Oracle
-
All Web Browsers
Description
When you use a table alias in a query you must use the alias in your index hint or it will be ignored:
http://www.dba-oracle.com/t_oracle_index_hint_syntax.htm
i2b2 generated query we see this exhibitied in the explain plan and execution time:
select /*+ index(observation_fact fact_cnpt_pat_enct_idx) */
f.encounter_num, f.patient_num
from i2b2demodata.observation_fact f
where f.concept_cd IN (select concept_cd
from i2b2demodata.CONCEPT_DIMENSION
where concept_path LIKE '\PCORI\DIAGNOSIS\10\(M00-M99) Dise~6mvn\(M30-M36) Syst~csr9\(M35) Other sy~jg1l\(M35.1) Other~dwcp\%')
group by f.encounter_num , f.patient_num
having count( distinct f.patient_num || '|' || f.encounter_num || '|' || f.provider_id || '|' || f.instance_num || '|' ||f.concept_cd || '|' ||cast(f.start_date as varchar(50))) >= 2 ;
http://www.dba-oracle.com/t_oracle_index_hint_syntax.htm
i2b2 generated query we see this exhibitied in the explain plan and execution time:
select /*+ index(observation_fact fact_cnpt_pat_enct_idx) */
f.encounter_num, f.patient_num
from i2b2demodata.observation_fact f
where f.concept_cd IN (select concept_cd
from i2b2demodata.CONCEPT_DIMENSION
where concept_path LIKE '\PCORI\DIAGNOSIS\10\(M00-M99) Dise~6mvn\(M30-M36) Syst~csr9\(M35) Other sy~jg1l\(M35.1) Other~dwcp\%')
group by f.encounter_num , f.patient_num
having count( distinct f.patient_num || '|' || f.encounter_num || '|' || f.provider_id || '|' || f.instance_num || '|' ||f.concept_cd || '|' ||cast(f.start_date as varchar(50))) >= 2 ;