optimizer not working properly

Hi falks, I have just migrated Informix IDS from version 7.31 to 11.70.FC4 on my Solais (see below)and noticed an unexpected behavior which did not happen in 7.31: Environment: SunOS sf2900 5.10 Generic_127111-03 sun4u sparc SUNW,Netra-T12 Informix: IBM Informix Dynamic Server Version 11.70.FC4 -- On-Line The optimizer does not recognize the index path in the following select: 1. Firstly not forcing INDEX directive: QUERY: (OPTIMIZATION TIMESTAMP: 07-08-2013 11:36:28) ------ SELECT UNIQUE dgfacprop.factura FROM dgfacprop WHERE ( dgfacprop.codigo = 'P' AND dgfacprop.alfa = '.' AND dgfacprop.nume = 364203072 ) OR ( dgfacprop.codigo = 'R' AND dgfacprop.alfa = "36E056ADU" AND dgfacprop.nume = 940 ) Estimated Cost: 885549 Estimated # of Rows Returned: 985781 1) informix.dgfacprop: SEQUENTIAL SCAN Filters: (((informix.dgfacprop.codigo = 'P' AND informix.dgfacprop.alfa = '.' ) AND informix.dgfacprop.nume = 364203072 ) OR ((informix.dgfacprop.codigo = 'R' AND informix.dgfacprop.alfa = '36E056ADU' ) AND informix.dgfacprop.nume = 940 ) ) Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 dgfacprop type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 0 2151831 13472846 00:25.96 885549 type rows_sort est_rows rows_cons time ------------------------------------------------- sort 0 985782 0 00:25.96 2. Secondly forcing the index path: QUERY: (OPTIMIZATION TIMESTAMP: 07-08-2013 11:40:25) ------ SELECT {+INDEX(dgfacprop i1_dgfacprop)} UNIQUE dgfacprop.factura FROM dgfacprop WHERE ( dgfacprop.codigo = 'P' AND dgfacprop.alfa = '.' AND dgfacprop.nume = 364203072 ) OR ( dgfacprop.codigo = 'R' AND dgfacprop.alfa = "36E056ADU" AND dgfacprop.nume = 940 ) DIRECTIVES FOLLOWED:INDEX ( dgfacprop i1_dgfacprop ) DIRECTIVES NOT FOLLOWED: Estimated Cost: 1248944 Estimated # of Rows Returned: 985781 1) informix.dgfacprop: INDEX PATH (1) Index Name: informix.i1_dgfacprop Index Keys: codigo alfa nume (Serial, fragments: ALL) Lower Index Filter: ((informix.dgfacprop.codigo = 'P' AND informix.dgfacprop.alfa = '.' ) AND informix.dgfacprop.nume = 364203072 ) (2) Index Name: informix.i1_dgfacprop Index Keys: codigo alfa nume (Serial, fragments: ALL) Lower Index Filter: ((informix.dgfacprop.codigo = 'R' AND informix.dgfacprop.alfa = '36E056ADU' ) AND informix.dgfacprop .nume = 940 ) As you can see when you don't indicate anything it does a SEQUENTIAL SCAN, taking too long to complete, whereas forcing the INDEX it does behave as expected, not taking more than some micro-seconds. Update statistics are up to date (as shown below): tabname dgfacprop owner informix partnum 6291871 tabid 136 rowsize 85 ncols 14 nindexes 2 nrows 13472333,00000 created 03-07-2013 version 9111788 tabtype T locklevel R npused 481378,0000000 fextsize 200000 nextsize 20000 flags 0 site dbname type_xid 0 am_id 0 pagesize 2048 ustlowts 2013-07-08 10:31:34.00000 secpolicyid 0 protgranularity statchange statlevel A Does anybody know the reason why of this behavior?¿Do I have to indicate INDEX directive in every select to make IDS work properly? Thanks a lot, Juan Roca

The solution: OPTCOMPIND 0 ##-- Works fine. USES INDEX OPTCOMPIND 2 ##-- Uses SEQUENTIAL SCAN

HI Juan, Did you try the following query as a replacement for your original query: SELECT UNIQUE dgfacprop.factura FROM dgfacprop WHEREdgfacprop.codigo = 'P' AND dgfacprop.alfa = '.' AND dgfacprop.nume = 364203072 UNION SELECT UNIQUE dgfacprop.factura FROM dgfacprop WHEREdgfacprop.codigo = 'R' AND dgfacprop.alfa = "36E056ADU" AND dgfacprop.nume = 940 Cordialement, Regards, Khaled Bentebal Directeur Général - ConsultiX Président UGIF - User Group Informix France IIUG - Board of Directors Tél: 33 (0) 1 39 12 18 00 Fax: 33 (0) 1 39 12 18 18 Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr Le 08/07/13 11:59, JUAN ROCA a écrit : > SELECT > > UNIQUE dgfacprop.factura > > FROM dgfacprop > WHERE ( > > dgfacprop.codigo = 'P' AND > > dgfacprop.alfa = '.' AND > > dgfacprop.nume = 364203072 > > ) > > OR > > ( > > dgfacprop.codigo = 'R' AND > > dgfacprop.alfa = "36E056ADU" AND > > dgfacprop.nume = 940 > > )

OLTP systems should always set OPTCOMPIND to zero! The optimizer used the sequential scan because the cost of doing that was 845,000 and the cost of using the index was 1,200,000 (roughly) so, based on OPTCOMPIND the cost won out over other considerations and a scan was chosen. Now, as to why the cost was off compared to actual run-time? I'm going to say it is the stats even though you think they are OK. How were stats run? Manually? To what level? Using AUS? Using dostats? If you didn't use dostats or run the stats manually using the same rule set - or, even better, ran HIGH on all columns, then I would suggest trying dostats and seeing if that changes the cost of doing the query with index versus sequential scan. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Jul 8, 2013 at 6:58 AM, JUAN ROCA <jluis.roca@hotmail.com> wrote: > The solution: > > OPTCOMPIND 0 ##-- Works fine. USES INDEX > > OPTCOMPIND 2 ##-- Uses SEQUENTIAL SCAN> > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c365a65f583004e0ff6340

What is the isolation level that you're using? That should be a bug.... I recall something similar on 11.50.... On Jul 8, 2013 11:59 AM, "JUAN ROCA" <jluis.roca@hotmail.com> wrote: > The solution: > > OPTCOMPIND 0 ##-- Works fine. USES INDEX > > OPTCOMPIND 2 ##-- Uses SEQUENTIAL SCAN> > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7b5d8531ebf2a504e102fbc4