Uploaded image for project: 'i2b2 Core Software'
  1. i2b2 Core Software
  2. CORE-361

Oracle - index hints ignored when using alias

    XMLWordPrintable

Details

    • Bug
    • Status: New
    • Minor
    • Resolution: Unresolved
    • None
    • TBD
    • None
    • None
    • Oracle 11.2.0.4.0 64bit
      I2B2: 1.7.08b
    • Rank:
      0|i003hr:
    • 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 ;

      Attachments

        Activity

          People

            mem61 Mike Mendis
            bostasie Brian Ostasiewski
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated: