Type:
Bug
Status:
New
Priority:
Minor
Resolution:
Unresolved
Affects Version/s:
None
Component/s:
None
Environment:
Oracle 11.2.0.4.0 64bit
I2B2: 1.7.08b
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 ;
No work has yet been logged on this issue.
{"report":{"fcp":1205.8999999994412,"ttfb":194.20000000018626,"pageVisibility":"visible","entityId":11198,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":16,"apdex":0.5,"journeyId":"b55faf7a-7192-47f5-b892-d8bcacf3fbea","navigationType":0,"readyForUser":1268.3999999994412,"redirectCount":0,"resourceLoadedEnd":1004.5,"resourceLoadedStart":212.20000000018626,"resourceTiming":[{"duration":220.19999999925494,"initiatorType":"link","name":"https://community.i2b2.org/jira/s/7fd763ecdf5ed1f47fc4d22fa8382e97-CDN/4x9nqn/820011/16zrvj4/49fa3aa3d35a2cc689cbf274e66cc41a/_/download/contextbatch/css/_super/batch.css","startTime":212.20000000018626,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":212.20000000018626,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":432.3999999994412,"responseStart":0,"secureConnectionStart":0},{"duration":226.59999999962747,"initiatorType":"link","name":"https://community.i2b2.org/jira/s/898b8076c5e82f53cd816ca393e45a56-CDN/4x9nqn/820011/16zrvj4/f614b50eeb842ebd1ea7ab2903699907/_/download/contextbatch/css/project.issue.navigator,jira.view.issue,jira.global,atl.general,-_super/batch.css?jira.create.linked.issue=true&richediton=true","startTime":212.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":212.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":439.09999999962747,"responseStart":0,"secureConnectionStart":0},{"duration":576.4000000003725,"initiatorType":"script","name":"https://community.i2b2.org/jira/s/4c9c503fe98f210445831b0d7b0cdc33-CDN/4x9nqn/820011/16zrvj4/49fa3aa3d35a2cc689cbf274e66cc41a/_/download/contextbatch/js/_super/batch.js?locale=en-US","startTime":212.79999999981374,"connectEnd":614.2999999998137,"connectStart":583.3999999994412,"domainLookupEnd":212.79999999981374,"domainLookupStart":212.79999999981374,"fetchStart":212.79999999981374,"redirectEnd":0,"redirectStart":0,"requestStart":619.7999999998137,"responseEnd":789.2000000001863,"responseStart":640.2999999998137,"secureConnectionStart":600.5999999996275},{"duration":585.5999999996275,"initiatorType":"script","name":"https://community.i2b2.org/jira/s/694e598c1ae48b0f96655173631cd247-CDN/4x9nqn/820011/16zrvj4/f614b50eeb842ebd1ea7ab2903699907/_/download/contextbatch/js/project.issue.navigator,jira.view.issue,jira.global,atl.general,-_super/batch.js?jira.create.linked.issue=true&locale=en-US&richediton=true","startTime":212.79999999981374,"connectEnd":665.2999999998137,"connectStart":630.7999999998137,"domainLookupEnd":212.79999999981374,"domainLookupStart":212.79999999981374,"fetchStart":212.79999999981374,"redirectEnd":0,"redirectStart":0,"requestStart":703.7999999998137,"responseEnd":798.3999999994412,"responseStart":737.5,"secureConnectionStart":644.7999999998137},{"duration":528.1000000005588,"initiatorType":"script","name":"https://community.i2b2.org/jira/s/53f54e0ac3f00bb56b136b4d2fff2853-CDN/4x9nqn/820011/16zrvj4/aae1242f5fc81cc6a5bb8bc963ccda29/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en-US","startTime":212.8999999994412,"connectEnd":666.5,"connectStart":633.7999999998137,"domainLookupEnd":212.8999999994412,"domainLookupStart":212.8999999994412,"fetchStart":212.8999999994412,"redirectEnd":0,"redirectStart":0,"requestStart":703.7999999998137,"responseEnd":741,"responseStart":718,"secureConnectionStart":646.5},{"duration":542,"initiatorType":"script","name":"https://community.i2b2.org/jira/s/d41d8cd98f00b204e9800998ecf8427e-CDN/4x9nqn/820011/16zrvj4/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":213,"connectEnd":678,"connectStart":636.5999999996275,"domainLookupEnd":213,"domainLookupStart":213,"fetchStart":213,"redirectEnd":0,"redirectStart":0,"requestStart":716.0999999996275,"responseEnd":755,"responseStart":739,"secureConnectionStart":649.7999999998137},{"duration":560.4000000003725,"initiatorType":"script","name":"https://community.i2b2.org/jira/s/d41d8cd98f00b204e9800998ecf8427e-CDN/4x9nqn/820011/16zrvj4/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":213.09999999962747,"connectEnd":703.7000000001863,"connectStart":656.8999999994412,"domainLookupEnd":213.09999999962747,"domainLookupStart":213.09999999962747,"fetchStart":213.09999999962747,"redirectEnd":0,"redirectStart":0,"requestStart":748.2000000001863,"responseEnd":773.5,"responseStart":762.5999999996275,"secureConnectionStart":680},{"duration":248.29999999981374,"initiatorType":"link","name":"https://community.i2b2.org/jira/s/30748292e4ca68be6947d7969829384b-CDN/4x9nqn/820011/16zrvj4/4f66da484ef7d95a2a604d3ab014374c/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":213.20000000018626,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":213.20000000018626,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":461.5,"responseStart":0,"secureConnectionStart":0},{"duration":596.2999999998137,"initiatorType":"script","name":"https://community.i2b2.org/jira/rest/api/1.0/shortcuts/820011/766848d8ff8ea3676a80e44dfb51696a/shortcuts.js?context=issuenavigation&context=issueaction","startTime":213.29999999981374,"connectEnd":772.2999999998137,"connectStart":723,"domainLookupEnd":213.29999999981374,"domainLookupStart":213.29999999981374,"fetchStart":213.29999999981374,"redirectEnd":0,"redirectStart":0,"requestStart":785.0999999996275,"responseEnd":809.5999999996275,"responseStart":806.3999999994412,"secureConnectionStart":753.7000000001863},{"duration":97.3999999994412,"initiatorType":"link","name":"https://community.i2b2.org/jira/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/4x9nqn/820011/16zrvj4/efa42a25652b26dfd802540c024826b3/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-jira.view.issue,-project.issue.navigator/batch.css?jira.create.linked.issue=true&richediton=true","startTime":386,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":386,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":483.3999999994412,"responseStart":0,"secureConnectionStart":0},{"duration":538.5,"initiatorType":"script","name":"https://community.i2b2.org/jira/s/8087506fefd02b4096991c90836b49f6-CDN/4x9nqn/820011/16zrvj4/efa42a25652b26dfd802540c024826b3/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-jira.view.issue,-project.issue.navigator/batch.js?jira.create.linked.issue=true&locale=en-US&richediton=true","startTime":386.20000000018626,"connectEnd":901.2000000001863,"connectStart":867.5999999996275,"domainLookupEnd":386.20000000018626,"domainLookupStart":386.20000000018626,"fetchStart":386.20000000018626,"redirectEnd":0,"redirectStart":0,"requestStart":906,"responseEnd":924.7000000001863,"responseStart":922,"secureConnectionStart":881.7999999998137},{"duration":604.5,"initiatorType":"script","name":"https://community.i2b2.org/jira/s/d41d8cd98f00b204e9800998ecf8427e-CDN/4x9nqn/820011/16zrvj4/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":400,"connectEnd":985.0999999996275,"connectStart":947,"domainLookupEnd":400,"domainLookupStart":400,"fetchStart":400,"redirectEnd":0,"redirectStart":0,"requestStart":987.5,"responseEnd":1004.5,"responseStart":1003.2999999998137,"secureConnectionStart":959.8999999994412},{"duration":587.1999999992549,"initiatorType":"script","name":"https://community.i2b2.org/jira/s/d41d8cd98f00b204e9800998ecf8427e-CDN/4x9nqn/820011/16zrvj4/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":416.70000000018626,"connectEnd":985.7000000001863,"connectStart":952.3999999994412,"domainLookupEnd":416.70000000018626,"domainLookupStart":416.70000000018626,"fetchStart":416.70000000018626,"redirectEnd":0,"redirectStart":0,"requestStart":987.7999999998137,"responseEnd":1003.8999999994412,"responseStart":1002.7999999998137,"secureConnectionStart":964.3999999994412}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":48,"connectEnd":136,"secureConnectionStart":108,"requestStart":136,"responseStart":194,"responseEnd":417,"domLoading":211,"domInteractive":1334,"domContentLoadedEventStart":1334,"domContentLoadedEventEnd":1379,"domComplete":1747,"loadEventStart":1747,"loadEventEnd":1747,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1318.3999999994412},{"name":"bigPipe.sidebar-id.end","time":1319.3999999994412},{"name":"bigPipe.activity-panel-pipe-id.start","time":1319.5999999996275},{"name":"bigPipe.activity-panel-pipe-id.end","time":1320.0999999996275},{"name":"activityTabFullyLoaded","time":1387.2999999998137}],"measures":[],"correlationId":"2eaa9451ea31de","effectiveType":"4g","downlink":9.5,"rtt":0,"serverDuration":261,"dbReadsTimeInMs":37,"dbConnsTimeInMs":65,"applicationHash":"0629dd8d260e3954ece49053e565d01dabe11609","experiments":[]}}