is i2b2 indexing hint working for oracle ?

Hi there,

Sorry for posting issue here, as I was not able to post issue at https://github.com/i2b2/i2b2-core-server.

Does i2b2 create correct syntax for oracle index hinting? To me, it seems like index should be refereed by alias if alias was used to refer a table.

Example: sql from I2b2 log

  select  /*+ index(observation_fact fact_cnpt_pat_enct_idx) */ f.patient_num
 from Nightherondata.observation_fact f
 where
 f.concept_cd IN (select concept_cd from  Nightherondata.concept_dimension   where concept_path 

...

I ran the above sql from i2b2 webclient and sqldevoper does not use index.

Afer reading http://www.dba-oracle.com/t_oracle_index_hint_syntax.htm,

Also note that of you alias the table, you must use the alias in the index hint:
select /*+ index(c cust_primary_key_idx) */ * from customer c;

I change observation_fact to f for index hinting (change table name to alias name)

  select  /*+ index(f fact_cnpt_pat_enct_idx) */ f.patient_num
 from Nightherondata.observation_fact f
 where
 f.concept_cd IN (select concept_cd from  Nightherondata.concept_dimension   where concept_path 

...

now above sql uses index when I run trough sqldevloper.

I am very new to i2b2 source code. I found two instance which may need to be change.

/i2b2-core-server$ grep -ir "observation_fact fact_cnpt_pat_enct_idx" * --exclude-dir _test --exclude-dir testfiles
edu.harvard.i2b2.crc/src/server/edu/harvard/i2b2/crc/dao/setfinder/querybuilder/temporal/TemporalPanelConceptItem.java:                 return " /*+ index(observation_fact fact_cnpt_pat_enct_idx) */ ";
edu.harvard.i2b2.crc/src/server/edu/harvard/i2b2/crc/dao/setfinder/querybuilder/BuildTempTableSql.java:             sqlHintClause = " /*+ index(observation_fact fact_cnpt_pat_enct_idx) */ ";

i2b2 / i2b2-workinggroup-transmart-etl

is i2b2 indexing hint working for oracle ? #2