skip fragment question,SQL EXPLAIN differen

Hi&#65292; in the ESQL application, the application use host variable, the SQL take about 3s, the expalin show it scan all fragment.In dbaccess I replace host variable with real values, the SQL take about 3ms, the explain show it skip fragment. Why? Do it due to the host variable? How to change the ESQL/C application, so it can skip fragment also? Index Keys: yngyjg jioyrq jio1gy (Serial, fragments: ALL) Index Keys: yngyjg jioyrq jio1gy (Serial, fragments: 2272) table acpls has lots of fragment. table pjgcs has only on fragment. 1, in the ESQL APPLICATION QUERY: (OPTIMIZATION TIMESTAMP: 07-17-2018 16:07:12) ------ SELECT cpznxh , guiyls , jiaoym , yewudh , huobdh , kmuhao , xnzhbz , zhangh , jiedbz , jio1je , khzhlx , kehuzh , zhuzwm , zhyodm , pngzhh , yngyjg fromacpls where yngyjg in ( select s . yngyj g from pjgcs s where s . yngyjg like ? ) -- * &#26102;&#38388;: 20180709 &#20462;&#25913;&#32773;: Li,Shj add --yngyjg like :cYNGYJG -- * &#26102;&#38388;: 20180709 &#20462;&#25913;&#32773;: Li,Shj and jioyrq = ? and jio1gy = ? --and yngyjg = zhyyjg and jiluzt = '0' into temp tmp_acpdr with no log Estimated Cost: 81 Estimated # of Rows Returned: 1 1) cbs.acpls: INDEX PATH Filters: cbs.acpls.jiluzt = '0' (1) Index Name: cbs.acpls_idx6 Index Keys: yngyjg jioyrq jio1gy (Serial, fragments: ALL) Lower Index Filter: ((cbs.acpls.jio1gy = '99995001' AND cbs.acpls.jioyrq = '20220321' ) AND cbs.acpls.yngyjg = ANY <subquery> ) Subquery: --------- Estimated Cost: 80 Estimated # of Rows Returned: 2403 1) cbs.s: INDEX PATH (1) Index Name: cbs.pjgcs_idx1 Index Keys: yngyjg (Key-Only) (Serial, fragments: ALL) Index Key Filters: (cbs.s.yngyjg LIKE '%' ) Query statistics: ----------------- Host variables: --------------- No. type flags value ---------------------------- 1 char 0x000 % 2 char 0x000 20220321 3 char 0x000 99995001 Table map : ---------------------------- Internal name Table name ---------------------------- t1 acpls t2 tmp_acpdr type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 74 1 74 00:03.22 81 type table rows_ins time ----------------------------------- insert t2 74 00:03.22 Subquery statistics: -------------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 s type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 2416 2403 2416 00:00.00 80 type rows_sort est_rows rows_cons time ------------------------------------------------- sort 2416 0 2416 00:00.00 2, explain in dbaccess QUERY: (OPTIMIZATION TIMESTAMP: 07-17-2018 16:05:58) ------ SELECT cpznxh,guiyls,jiaoym,yewudh,huobdh,kmuhao,xnzhbz,zhangh, jiedbz,jio1je,khzhlx,kehuzh,zhuzwm,zhyodm,pngzhh,yngyjg from acpls where yngyjg in ( select s.yngyjg from pjgcs s where s.yngyjg like '%' ) and jioyrq = '20220321' and jio1gy = '99995001' --and yngyjg = zhyyjg and jiluzt = '0' into temp tmp_acpdr with no log Estimated Cost: 81 Estimated # of Rows Returned: 1 1) cbs.acpls: INDEX PATH Filters: cbs.acpls.jiluzt = '0' (1) Index Name: cbs.acpls_idx6 Index Keys: yngyjg jioyrq jio1gy (Serial, fragments: 2272) Fragments Scanned: (2272) sys_p2272 in datacbs25 Lower Index Filter: ((cbs.acpls.jio1gy = '99995001' AND cbs.acpls.jioyrq = '20220321' ) AND cbs.acpls.yngyjg = ANY <subquery> ) Subquery: --------- Estimated Cost: 80 Estimated # of Rows Returned: 2403 1) vlog.s: INDEX PATH (1) Index Name: cbs.pjgcs_idx1 Index Keys: yngyjg (Key-Only) (Serial, fragments: ALL) Index Key Filters: (vlog.s.yngyjg LIKE '%' ) Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 acpls t2 tmp_acpdr type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 74 1 74 00:00.03 81 type table rows_ins time ----------------------------------- insert t2 74 00:00.03 Subquery statistics: -------------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 s type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 2416 2403 2416 00:00.00 80 type rows_sort est_rows rows_cons time ------------------------------------------------- sort 2416 0 2416 00:00.00 thanks for your time.

Can you try using the "WITH REOPTIMIZATION" clause in the ESQL program and verify if the plan changes? "https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/i ds_sqs_0923.htm" Also, can you post the fragmentation clause for the table acpls (or index acpls_idx6 ) ? -- Luis Marques

It's interval fragment. DBSCHEMA Schema Utility INFORMIX-SQL Version 12.10.FC8W1X6 { TABLE "cbs".acpls row size = 936 number of columns = 96 index size = 112 } create table "cbs".acpls ( chpzxh integer default 0 not null , cpznxh integer default 0 not null , cpxgbz char(1), guiyls char(12) not null , qantrq char(8), qtails char(12), ynjyrq char(8), yngyls char(12), jioyrq char(8) not null , zhujrq char(8), jioysj integer default 0 not null , yngyjg char(4) not null , zhngjg char(4) not null , zhngdh char(5), jiaoym char(4) not null , jio1gy char(8) not null , shoqgy char(8), zhyyjg char(4) not null , zhkjjg char(4) not null , yewudh char(4) not null , huobdh char(2) not null , zhaoxh char(6), kmuhao char(6) not null , kemucc char(1) not null , zhangh char(20) not null , zhuzzh char(20), jiedbz char(1) not null , jio1je decimal(14,2) default 0.00, zhhuye decimal(15,2) default 0.00, yueefx char(1), xnzhbz char(1) not null , kxhubz char(1), bchzbz char(1) not null , chbubz char(1), gngzqz integer default 0, cunqii char(3), pngzhh char(13), kehuzh char(20), khzhlx char(1), shunxh char(4), zhyodm char(22), zhyod2 char(22), dfakmh char(6), xjxmdm char(10), waiwbh char(3), yewubh char(16), qixirq char(8), paijia decimal(12,6) default 0.000000, rjiebz char(1), waihbz char(1), dxczbz char(1), xiozxh char(17), kehhao char(10), djibbz char(2), rzzhbz char(1) not null , yueexz char(1), zhuzwm char(62), jishuu decimal(20,2) default 0.00, jiejuh char(16), daynbz char(1), pzjcbz char(1), cpbh01 char(4), cpbh02 char(4), cpbh03 char(4), beiy01 char(1), beiy04 char(4), beiy40 char(40), beyint integer default 0, beydec decimal(15,2) default 0.00, shjnch integer default 0, jiluzt char(1) default '0', qjulsh char(26), cjsbdm char(16), cjznxh integer default 0, cpwd1v char(20), cpwd2v char(20), cpwd3v char(20), cpwd4v char(20), cpwd5v char(20), cpwd6v char(20), cpwd7v char(20), cpwd8v char(20), cpwd9v char(20), cpwdav char(20), cpwdbv char(20), cpwdcv char(20), cpwddv char(20), cpwdev char(20), cpwdfv char(20), cjbhov char(4), hscpzl char(6), hskhlb char(6), hsqxsx char(6), dkjelx char(4), hscpzt char(1), hmjsjc char(20) ) fragment by range(TO_DATE (jioyrq ,'%Y%m%d' )) interval(interval( 1) day(9) to day) store in(datacbs25) partition p0 VALUES < datetime(2016-01-01 00:00:00.00000) year to fraction(5) in datacbs07 extent size 32000 next size 32000 lock mode row; revoke all on "cbs".acpls from "public" as "cbs"; create index "cbs".acpls_idx3 on "cbs".acpls (zhangh,jioyrq) using btree ; create unique index "cbs".acpls_idx4 on "cbs".acpls (jioyrq,guiyls, cpznxh) using btree ; create index "cbs".acpls_idx6 on "cbs".acpls (yngyjg,jioyrq,jio1gy) using btree ; create index "cbs".acpls_idx7 on "cbs".acpls (qantrq,qtails) using btree ;