Optimizer ignores hint
A user on IDS 10.00.FC8 couldn't get the optimizer to honour an {+INDEX(tvh 157_398)} directive — set explain just showed a sequential scan with nothing under "DIRECTIVES NOT FOLLOWED". After ruling out the ONCONFIG DIRECTIVES setting, Malc spotted that 157_398 was an index implicitly created by a foreign-key constraint, so its real name begins with a space. Quoting the name with the leading space, e.g. {+INDEX(tvh " 157_398")}, made the directive work. Art Kagel's alternative: drop the constraint, create the index explicitly with a proper name, then re-add the constraint.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi all,
I'm trying to force optimizer to use the index in the query below but
it refuses to:
------- Informix Dynamic Server Version 10.00.FC8 -------------
set explain on avoid_execute;
SELECT
{+INDEX(tvh 157_398)} tvh.foo, tvh.sbar
FROM
tserv_venc_hora tvh
WHERE
tvh.num_id_hora_carga = 4213;
set explain off;
The index name is correct. Can anyone help?
TIA,
Alphonsus.
Post the set explain output please.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
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 Thu, Mar 25, 2010 at 10:48 AM, Alphonsus <karataiev@gmail.com> wrote:
> Hi all,
> I'm trying to force optimizer to use the index in the query below but
> it refuses to:
>
> ------- Informix Dynamic Server Version 10.00.FC8 -------------
>
> set explain on avoid_execute;>
> SELECT
> {+INDEX(tvh 157_398)} tvh.foo, tvh.sbar
> FROM
> tserv_venc_hora tvh
> WHERE
> tvh.num_id_hora_carga = 4213;
>
>
> set explain off;>
>
> The index name is correct. Can anyone help?
>
> TIA,
> Alphonsus.
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
On 25/03/2010 14:48, Alphonsus wrote:
> Hi all,
> I'm trying to force optimizer to use the index in the query below but
> it refuses to:
>
> ------- Informix Dynamic Server Version 10.00.FC8 -------------
>
> set explain on avoid_execute;>
> SELECT
> {+INDEX(tvh 157_398)} tvh.foo, tvh.sbar
> FROM
> tserv_venc_hora tvh
> WHERE
> tvh.num_id_hora_carga = 4213;
>
>
> set explain off;>
>
> The index name is correct. Can anyone help?
>
> TIA,
> Alphonsus.
>
Why are you trying to force it to use a different index?
On Mar 25, 12:19 pm, Art Kagel <art.ka...@gmail.com> wrote:
> Post the set explain output please.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (a...@iiug.org)
>
> See you at the 2010 IIUG Informix Conference
> April 25-28, 2010
> Overland Park (Kansas City), KSwww.iiug.org/conf
>
> 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 Thu, Mar 25, 2010 at 10:48 AM, Alphonsus <karata...@gmail.com> wrote:
> > Hi all,
> > I'm trying to force optimizer to use the index in the query below but
> > it refuses to:
>
> > ------- Informix Dynamic Server Version 10.00.FC8 -------------
>
> > set explain on avoid_execute;>
> > SELECT
> > {+INDEX(tvh 157_398)} tvh.foo, tvh.sbar
> > FROM
> > tserv_venc_hora tvh
> > WHERE
> > tvh.num_id_hora_carga = 4213;
>
> > set explain off;>
> > The index name is correct. Can anyone help?
>
> > TIA,
> > Alphonsus.
>
> > _______________________________________________
> > Informix-list mailing list
> > Informix-l...@iiug.org
> >http://www.iiug.org/mailman/listinfo/informix-list
sqexplain.out
QUERY:
------
SELECT {+INDEX(tvh 157_398)} tvh.foo, tvh.bar
FROM tserv_venc_hora tvh
WHERE tvh.num_id_hora_carga = 4213
DIRECTIVES FOLLOWED:
DIRECTIVES NOT FOLLOWED:Estimated Cost: 395677
Estimated # of Rows Returned: 572442
Temporary Files Required For: Order By
1) informix.tvh: SEQUENTIAL SCAN
Filters: informix.tvh.num_id_hora_carga = 4213
On Mar 25, 10:33 am, Alphonsus <karata...@gmail.com> wrote:
> On Mar 25, 12:19 pm, Art Kagel <art.ka...@gmail.com> wrote:
>
>
>
> > Post the set explain output please.
>
> > Art
>
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > IIUG Board of Directors (a...@iiug.org)
>
> > See you at the 2010 IIUG Informix Conference
> > April 25-28, 2010
> > Overland Park (Kansas City), KSwww.iiug.org/conf
>
> > 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 Thu, Mar 25, 2010 at 10:48 AM, Alphonsus <karata...@gmail.com> wrote:
> > > Hi all,
> > > I'm trying to force optimizer to use the index in the query below but
> > > it refuses to:
>
> > > ------- Informix Dynamic Server Version 10.00.FC8 -------------
>
> > > set explain on avoid_execute;>
> > > SELECT
> > > {+INDEX(tvh 157_398)} tvh.foo, tvh.sbar
> > > FROM
> > > tserv_venc_hora tvh
> > > WHERE
> > > tvh.num_id_hora_carga = 4213;
>
> > > set explain off;>
> > > The index name is correct. Can anyone help?
>
> > > TIA,
> > > Alphonsus.
>
> > > _______________________________________________
> > > Informix-list mailing list
> > > Informix-l...@iiug.org
> > >http://www.iiug.org/mailman/listinfo/informix-list
>
> sqexplain.out
>
> QUERY:
> ------
> SELECT {+INDEX(tvh 157_398)} tvh.foo, tvh.bar
> FROM tserv_venc_hora tvh
> WHERE tvh.num_id_hora_carga = 4213
>
> DIRECTIVES FOLLOWED:
> DIRECTIVES NOT FOLLOWED:> Estimated Cost: 395677
> Estimated # of Rows Returned: 572442
> Temporary Files Required For: Order By
>
> 1) informix.tvh: SEQUENTIAL SCAN
>
> Filters: informix.tvh.num_id_hora_carga = 4213
It isn't even listing that it didn't follow your directive. Are you
sure you have directives turned on in the $ONCONFIG file? DIRECTIVES
in the $ONCONFIG needs to be set to 1. I'm a bit worried that you
have such a simple query, I'm not sure why you would need to give it a
directive. What columns are included in the index you are trying to
get it to use? (or maybe just post the table schema?)
Jacques Renaut
IBM Informix Advanced Support
APD Team.
---> onconfig
DIRECTIVES 1 # Optimizer DIRECTIVES ON (1/Default)
or OFF (0)
---------------- Table ddl -------------
(columns foo, bar don't exist. I changed them in the original for the
sake of simplicity)
CREATE TABLE gescod:"informix".tserv_venc_hora
(
num_id_serv_venc_hora integer PRIMARY KEY,
qtd_ant_venc integer,
qtd_venc_00 integer,
qtd_venc_01 integer,
qtd_venc_02 integer,
qtd_venc_03 integer,
qtd_venc_04 integer,
qtd_venc_05 integer,
qtd_venc_06 integer,
qtd_venc_07 integer,
qtd_venc_08 integer,
qtd_venc_09 integer,
qtd_venc_10 integer,
qtd_venc_11 integer,
qtd_venc_12 integer,
qtd_venc_13 integer,
qtd_venc_14 integer,
qtd_venc_15 integer,
qtd_venc_16 integer,
qtd_venc_17 integer,
qtd_venc_18 integer,
qtd_venc_19 integer,
qtd_venc_20 integer,
qtd_venc_21 integer,
qtd_venc_22 integer,
qtd_venc_23 integer,
qtd_post_venc integer,
tip_servico char(4),
sig_malha char(2),
sig_regiao char(2),
num_id_polo smallint,
num_id_processo_servico smallint,
num_id_local char(4),
num_id_hora_carga integer,
num_total_servico integer
);
ALTER TABLE tserv_venc_horaADD CONSTRAINT fk_tservvenchora_tlocal
FOREIGN KEY (num_id_local)
REFERENCES tlocal(num_id_local)
;
ALTER TABLE tserv_venc_horaADD CONSTRAINT fk_servvenchora_horacarga
FOREIGN KEY (num_id_hora_carga)
REFERENCES thora_carga(num_id_hora_carga)
;
ALTER TABLE tserv_venc_horaADD CONSTRAINT fk_servvenchora_polo
FOREIGN KEY (num_id_polo)
REFERENCES tpolo(num_id_polo)
;
ALTER TABLE tserv_venc_horaADD CONSTRAINT fk_servvenchora_processoserv
FOREIGN KEY (num_id_processo_servico)
REFERENCES tprocesso_servico(num_id_processo_servico)
;
CREATE INDEX 157_401 ON tserv_venc_hora(num_id_local);
CREATE INDEX 157_400 ON tserv_venc_hora(num_id_processo_servico);
CREATE INDEX 157_399 ON tserv_venc_hora(num_id_polo);
CREATE INDEX 157_398 ON tserv_venc_hora(num_id_hora_carga);
CREATE UNIQUE INDEX 157_358 ON tserv_venc_hora(num_id_serv_venc_hora);
Yet, if I change the query to "select count(*)" it uses the index.
Thanks for the interest,
Alphonsus.
On Mar 25, 12:54 pm, jrenaut <jpren...@yahoo.com> wrote:
> On Mar 25, 10:33 am, Alphonsus <karata...@gmail.com> wrote:
>
>
>
>
>
> > On Mar 25, 12:19 pm, Art Kagel <art.ka...@gmail.com> wrote:
>
> > > Post the set explain output please.
>
> > > Art
>
> > > Art S. Kagel
> > > Advanced DataTools (www.advancedatatools.com)
> > > IIUG Board of Directors (a...@iiug.org)
>
> > > See you at the 2010 IIUG Informix Conference
> > > April 25-28, 2010
> > > Overland Park (Kansas City), KSwww.iiug.org/conf
>
> > > 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 Thu, Mar 25, 2010 at 10:48 AM, Alphonsus <karata...@gmail.com> wrote:
> > > > Hi all,
> > > > I'm trying to force optimizer to use the index in the query below but
> > > > it refuses to:
>
> > > > ------- Informix Dynamic Server Version 10.00.FC8 -------------
>
> > > > set explain on avoid_execute;>
> > > > SELECT
> > > > {+INDEX(tvh 157_398)} tvh.foo, tvh.sbar
> > > > FROM
> > > > tserv_venc_hora tvh
> > > > WHERE
> > > > tvh.num_id_hora_carga = 4213;
>
> > > > set explain off;>
> > > > The index name is correct. Can anyone help?
>
> > > > TIA,
> > > > Alphonsus.
>
> > > > _______________________________________________
> > > > Informix-list mailing list
> > > > Informix-l...@iiug.org
> > > >http://www.iiug.org/mailman/listinfo/informix-list
>
> > sqexplain.out
>
> > QUERY:
> > ------
> > SELECT {+INDEX(tvh 157_398)} tvh.foo, tvh.bar
> > FROM tserv_venc_hora tvh
> > WHERE tvh.num_id_hora_carga = 4213
>
> > DIRECTIVES FOLLOWED:
> > DIRECTIVES NOT FOLLOWED:> > Estimated Cost: 395677
> > Estimated # of Rows Returned: 572442
> > Temporary Files Required For: Order By
>
> > 1) informix.tvh: SEQUENTIAL SCAN
>
> > Filters: informix.tvh.num_id_hora_carga = 4213
>
> It isn't even listing that it didn't follow your directive. Are you
> sure you have directives turned on in the $ONCONFIG file? DIRECTIVES
> in the $ONCONFIG needs to be set to 1. I'm a bit worried that you
> have such a simple query, I'm not sure why you would need to give it a
> directive. What columns are included in the index you are trying to
> get it to use? (or maybe just post the table schema?)
>
> Jacques Renaut
> IBM Informix Advanced Support
> APD Team.
> > > On Thu, Mar 25, 2010 at 10:48 AM, Alphonsus <karata...@gmail.com> wrote:
> > > > Hi all,
> > > > I'm trying to force optimizer to use the index in the query below but
> > > > it refuses to:
>
> > > > ------- Informix Dynamic Server Version 10.00.FC8 -------------
>
> > > > set explain on avoid_execute;>
> > > > SELECT
> > > > {+INDEX(tvh 157_398)} tvh.foo, tvh.sbar
> > > > FROM
> > > > tserv_venc_hora tvh
> > > > WHERE
> > > > tvh.num_id_hora_carga = 4213;
>
> > > > set explain off;>
> > > > The index name is correct. Can anyone help?
>
> > > > TIA,
> > > > Alphonsus.
>
> > > > _______________________________________________
> > > > Informix-list mailing list
> > > > Informix-l...@iiug.org
> > > >http://www.iiug.org/mailman/listinfo/informix-list
>
> > sqexplain.out
>
> > QUERY:
> > ------
> > SELECT {+INDEX(tvh 157_398)} tvh.foo, tvh.bar
> > FROM tserv_venc_hora tvh
> > WHERE tvh.num_id_hora_carga = 4213
>
> > DIRECTIVES FOLLOWED:
> > DIRECTIVES NOT FOLLOWED:> > Estimated Cost: 395677
> > Estimated # of Rows Returned: 572442
> > Temporary Files Required For: Order By
>
> > 1) informix.tvh: SEQUENTIAL SCAN
>
> > Filters: informix.tvh.num_id_hora_carga = 4213
>
Is index 157_398 an index autocreated by a constraint? In which case
it probably has a space character as the first character in its name.
Not sure if you can try to optimise using one of those...
Could you post a dbschema of the table?
You've got Malc. See the result of the query below:
select 'x'||idxname||'x' from sysindices where idxname like
'%157_398%';
result = x 157_398x
Now look at the plan with spaces in the index name:
QUERY:
------
SELECT {+INDEX(tvh ' 157_398')} tvh.sig_malha, tvh.sig_regiao,
tvh.num_id_polo
FROM tserv_venc_hora tvh
WHERE tvh.num_id_hora_carga = 4213
order by tvh.num_id_hora_carga
DIRECTIVES FOLLOWED:
INDEX ( tvh 157_398 )
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 594091
Estimated # of Rows Returned: 572442
1) informix.tvh: INDEX PATH
(1) Index Keys: num_id_hora_carga (Serial, fragments: ALL)
Lower Index Filter: informix.tvh.num_id_hora_carga = 4213
Worked perfectly. Will change the name of index to make things more
clear.
Thank you all very much.
Alphonsus.
On Mar 25, 1:08 pm, Malc <i...@perrior.net> wrote:
> > > > On Thu, Mar 25, 2010 at 10:48 AM, Alphonsus <karata...@gmail.com> wrote:
> > > > > Hi all,
> > > > > I'm trying to force optimizer to use the index in the query below but
> > > > > it refuses to:
>
> > > > > ------- Informix Dynamic Server Version 10.00.FC8 -------------
>
> > > > > set explain on avoid_execute;>
> > > > > SELECT
> > > > > {+INDEX(tvh 157_398)} tvh.foo, tvh.sbar
> > > > > FROM
> > > > > tserv_venc_hora tvh
> > > > > WHERE
> > > > > tvh.num_id_hora_carga = 4213;
>
> > > > > set explain off;>
> > > > > The index name is correct. Can anyone help?
>
> > > > > TIA,
> > > > > Alphonsus.
>
> > > > > _______________________________________________
> > > > > Informix-list mailing list
> > > > > Informix-l...@iiug.org
> > > > >http://www.iiug.org/mailman/listinfo/informix-list
>
> > > sqexplain.out
>
> > > QUERY:
> > > ------
> > > SELECT {+INDEX(tvh 157_398)} tvh.foo, tvh.bar
> > > FROM tserv_venc_hora tvh
> > > WHERE tvh.num_id_hora_carga = 4213
>
> > > DIRECTIVES FOLLOWED:
> > > DIRECTIVES NOT FOLLOWED:> > > Estimated Cost: 395677
> > > Estimated # of Rows Returned: 572442
> > > Temporary Files Required For: Order By
>
> > > 1) informix.tvh: SEQUENTIAL SCAN
>
> > > Filters: informix.tvh.num_id_hora_carga = 4213
>
> Is index 157_398 an index autocreated by a constraint? In which case
> it probably has a space character as the first character in its name.
> Not sure if you can try to optimise using one of those...
> Could you post a dbschema of the table?
On Mar 25, 11:07 am, Alphonsus <karata...@gmail.com> wrote:
> ---> onconfig
>
> DIRECTIVES 1 # Optimizer DIRECTIVES ON (1/Default)
> or OFF (0)>
> ---------------- Table ddl -------------
> (columns foo, bar don't exist. I changed them in the original for the
> sake of simplicity)
>
> CREATE TABLE gescod:"informix".tserv_venc_hora
> (
> num_id_serv_venc_hora integer PRIMARY KEY,
> qtd_ant_venc integer,
> qtd_venc_00 integer,
> qtd_venc_01 integer,
> qtd_venc_02 integer,
> qtd_venc_03 integer,
> qtd_venc_04 integer,
> qtd_venc_05 integer,
> qtd_venc_06 integer,
> qtd_venc_07 integer,
> qtd_venc_08 integer,
> qtd_venc_09 integer,
> qtd_venc_10 integer,
> qtd_venc_11 integer,
> qtd_venc_12 integer,
> qtd_venc_13 integer,
> qtd_venc_14 integer,
> qtd_venc_15 integer,
> qtd_venc_16 integer,
> qtd_venc_17 integer,
> qtd_venc_18 integer,
> qtd_venc_19 integer,
> qtd_venc_20 integer,
> qtd_venc_21 integer,
> qtd_venc_22 integer,
> qtd_venc_23 integer,
> qtd_post_venc integer,
> tip_servico char(4),
> sig_malha char(2),
> sig_regiao char(2),
> num_id_polo smallint,
> num_id_processo_servico smallint,
> num_id_local char(4),
> num_id_hora_carga integer,> num_total_servico integer
> )
> ;
> ALTER TABLE tserv_venc_hora> ADD CONSTRAINT fk_tservvenchora_tlocal
> FOREIGN KEY (num_id_local)
> REFERENCES tlocal(num_id_local)
>
> ;
> ALTER TABLE tserv_venc_hora> ADD CONSTRAINT fk_servvenchora_horacarga
> FOREIGN KEY (num_id_hora_carga)
> REFERENCES thora_carga(num_id_hora_carga)
>
> ;
> ALTER TABLE tserv_venc_hora> ADD CONSTRAINT fk_servvenchora_polo
> FOREIGN KEY (num_id_polo)
> REFERENCES tpolo(num_id_polo)
>
> ;
> ALTER TABLE tserv_venc_hora> ADD CONSTRAINT fk_servvenchora_processoserv
> FOREIGN KEY (num_id_processo_servico)
> REFERENCES tprocesso_servico(num_id_processo_servico)
>
> ;
> CREATE INDEX 157_401 ON tserv_venc_hora(num_id_local)> ;
> CREATE INDEX 157_400 ON tserv_venc_hora(num_id_processo_servico)> ;
> CREATE INDEX 157_399 ON tserv_venc_hora(num_id_polo)> ;
> CREATE INDEX 157_398 ON tserv_venc_hora(num_id_hora_carga)> ;
> CREATE UNIQUE INDEX 157_358 ON tserv_venc_hora(num_id_serv_venc_hora)> ;
>
> Yet, if I change the query to "select count(*)" it uses the index.
>
> Thanks for the interest,
> Alphonsus.
>
> On Mar 25, 12:54 pm, jrenaut <jpren...@yahoo.com> wrote:
>
> > On Mar 25, 10:33 am, Alphonsus <karata...@gmail.com> wrote:
>
> > > On Mar 25, 12:19 pm, Art Kagel <art.ka...@gmail.com> wrote:
>
> > > > Post the set explain output please.
>
> > > > Art
>
> > > > Art S. Kagel
> > > > Advanced DataTools (www.advancedatatools.com)
> > > > IIUG Board of Directors (a...@iiug.org)
>
> > > > See you at the 2010 IIUG Informix Conference
> > > > April 25-28, 2010
> > > > Overland Park (Kansas City), KSwww.iiug.org/conf
>
> > > > 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 Thu, Mar 25, 2010 at 10:48 AM, Alphonsus <karata...@gmail.com> wrote:
> > > > > Hi all,
> > > > > I'm trying to force optimizer to use the index in the query below but
> > > > > it refuses to:
>
> > > > > ------- Informix Dynamic Server Version 10.00.FC8 -------------
>
> > > > > set explain on avoid_execute;>
> > > > > SELECT
> > > > > {+INDEX(tvh 157_398)} tvh.foo, tvh.sbar
> > > > > FROM
> > > > > tserv_venc_hora tvh
> > > > > WHERE
> > > > > tvh.num_id_hora_carga = 4213;
>
> > > > > set explain off;>
> > > > > The index name is correct. Can anyone help?
>
> > > > > TIA,
> > > > > Alphonsus.
>
> > > > > _______________________________________________
> > > > > Informix-list mailing list
> > > > > Informix-l...@iiug.org
> > > > >http://www.iiug.org/mailman/listinfo/informix-list
>
> > > sqexplain.out
>
> > > QUERY:
> > > ------
> > > SELECT {+INDEX(tvh 157_398)} tvh.foo, tvh.bar
> > > FROM tserv_venc_hora tvh
> > > WHERE tvh.num_id_hora_carga = 4213
>
> > > DIRECTIVES FOLLOWED:
> > > DIRECTIVES NOT FOLLOWED:> > > Estimated Cost: 395677
> > > Estimated # of Rows Returned: 572442
> > > Temporary Files Required For: Order By
>
> > > 1) informix.tvh: SEQUENTIAL SCAN
>
> > > Filters: informix.tvh.num_id_hora_carga = 4213
>
> > It isn't even listing that it didn't follow your directive. Are you
> > sure you have directives turned on in the $ONCONFIG file? DIRECTIVES
> > in the $ONCONFIG needs to be set to 1. I'm a bit worried that you
> > have such a simple query, I'm not sure why you would need to give it a
> > directive. What columns are included in the index you are trying to
> > get it to use? (or maybe just post the table schema?)
>
> > Jacques Renaut
> > IBM Informix Advanced Support
> > APD Team.
I was able to get this to work using the following:
QUERY:
------
select {+ INDEX(tvh " 101_15")}
qtd_ant_venc, qtd_venc_00 from tserv_venc_hora tvh
where tvh.num_id_hora_carga = 4213
DIRECTIVES FOLLOWED:
INDEX ( tvh 101_15 )
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 3
Estimated # of Rows Returned: 1
1) jrenaut.tvh: INDEX PATH
(1) Index Keys: num_id_hora_carga (Serial, fragments: ALL)
Lower Index Filter: jrenaut.tvh.num_id_hora_carga = 4213
So for auto generated index names, try putting the name in quotes and
make sure you put a space prior to the number that was generated.
Jacques Renaut
IBM Informix Advanced Support
APD Team
I think that Malc has nailed the problem. The index you are trying to force
is a implied index created by a constraint that does not have a supporting
explicitly created index. These indexes have 'hidden' names that begin with
a space and can't be specified anywhere for that reason. Here's the fix:
Drop the constraint (Constraint # 398 on tabid 157, create the index
manually with an explicit name, recreate the constraint, try again to force
the new index by its new name.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
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 Thu, Mar 25, 2010 at 11:33 AM, Alphonsus <karataiev@gmail.com> wrote:
> On Mar 25, 12:19 pm, Art Kagel <art.ka...@gmail.com> wrote:
> > Post the set explain output please.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > IIUG Board of Directors (a...@iiug.org)
> >
> > See you at the 2010 IIUG Informix Conference
> > April 25-28, 2010
> > Overland Park (Kansas City), KSwww.iiug.org/conf
> >
> > 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 Thu, Mar 25, 2010 at 10:48 AM, Alphonsus <karata...@gmail.com> wrote:
> > > Hi all,
> > > I'm trying to force optimizer to use the index in the query below but
> > > it refuses to:
> >
> > > ------- Informix Dynamic Server Version 10.00.FC8 -------------
> >
> > > set explain on avoid_execute;> >
> > > SELECT
> > > {+INDEX(tvh 157_398)} tvh.foo, tvh.sbar
> > > FROM
> > > tserv_venc_hora tvh
> > > WHERE
> > > tvh.num_id_hora_carga = 4213;
> >
> > > set explain off;> >
> > > The index name is correct. Can anyone help?
> >
> > > TIA,
> > > Alphonsus.
> >
> > > _______________________________________________
> > > Informix-list mailing list
> > > Informix-l...@iiug.org
> > >http://www.iiug.org/mailman/listinfo/informix-list
>
> sqexplain.out
>
> QUERY:
> ------
> SELECT {+INDEX(tvh 157_398)} tvh.foo, tvh.bar
> FROM tserv_venc_hora tvh
> WHERE tvh.num_id_hora_carga = 4213
>
>
> DIRECTIVES FOLLOWED:
> DIRECTIVES NOT FOLLOWED:> Estimated Cost: 395677
> Estimated # of Rows Returned: 572442
> Temporary Files Required For: Order By
>
> 1) informix.tvh: SEQUENTIAL SCAN
>
> Filters: informix.tvh.num_id_hora_carga = 4213
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
Thank you all.
On Mar 25, 1:36 pm, Art Kagel <art.ka...@gmail.com> wrote:
> I think that Malc has nailed the problem. The index you are trying to force
> is a implied index created by a constraint that does not have a supporting
> explicitly created index. These indexes have 'hidden' names that begin with
> a space and can't be specified anywhere for that reason. Here's the fix:
>
> Drop the constraint (Constraint # 398 on tabid 157, create the index
> manually with an explicit name, recreate the constraint, try again to force
> the new index by its new name.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (a...@iiug.org)
>
> See you at the 2010 IIUG Informix Conference
> April 25-28, 2010
> Overland Park (Kansas City), KSwww.iiug.org/conf
>
> 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 Thu, Mar 25, 2010 at 11:33 AM, Alphonsus <karata...@gmail.com> wrote:
> > On Mar 25, 12:19 pm, Art Kagel <art.ka...@gmail.com> wrote:
> > > Post the set explain output please.
>
> > > Art
>
> > > Art S. Kagel
> > > Advanced DataTools (www.advancedatatools.com)
> > > IIUG Board of Directors (a...@iiug.org)
>
> > > See you at the 2010 IIUG Informix Conference
> > > April 25-28, 2010
> > > Overland Park (Kansas City), KSwww.iiug.org/conf
>
> > > 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 Thu, Mar 25, 2010 at 10:48 AM, Alphonsus <karata...@gmail.com> wrote:
> > > > Hi all,
> > > > I'm trying to force optimizer to use the index in the query below but
> > > > it refuses to:
>
> > > > ------- Informix Dynamic Server Version 10.00.FC8 -------------
>
> > > > set explain on avoid_execute;>
> > > > SELECT
> > > > {+INDEX(tvh 157_398)} tvh.foo, tvh.sbar
> > > > FROM
> > > > tserv_venc_hora tvh
> > > > WHERE
> > > > tvh.num_id_hora_carga = 4213;
>
> > > > set explain off;>
> > > > The index name is correct. Can anyone help?
>
> > > > TIA,
> > > > Alphonsus.
>
> > > > _______________________________________________
> > > > Informix-list mailing list
> > > > Informix-l...@iiug.org
> > > >http://www.iiug.org/mailman/listinfo/informix-list
>
> > sqexplain.out
>
> > QUERY:
> > ------
> > SELECT {+INDEX(tvh 157_398)} tvh.foo, tvh.bar
> > FROM tserv_venc_hora tvh
> > WHERE tvh.num_id_hora_carga = 4213
>
> > DIRECTIVES FOLLOWED:
> > DIRECTIVES NOT FOLLOWED:> > Estimated Cost: 395677
> > Estimated # of Rows Returned: 572442
> > Temporary Files Required For: Order By
>
> > 1) informix.tvh: SEQUENTIAL SCAN
>
> > Filters: informix.tvh.num_id_hora_carga = 4213
> > _______________________________________________
> > Informix-list mailing list
> > Informix-l...@iiug.org
> >http://www.iiug.org/mailman/listinfo/informix-list
On 25 Mar, 17:45, Alphonsus <karata...@gmail.com> wrote:
> Thank you all.
>
> On Mar 25, 1:36 pm, Art Kagel <art.ka...@gmail.com> wrote:
>
> > I think that Malc has nailed the problem. The index you are trying to force
> > is a implied index created by a constraint that does not have a supporting
> > explicitly created index. These indexes have 'hidden' names that begin with
> > a space and can't be specified anywhere for that reason. Here's the fix:
>
> > Drop the constraint (Constraint # 398 on tabid 157, create the index
> > manually with an explicit name, recreate the constraint, try again to force
> > the new index by its new name.
>
> > Art
>
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > IIUG Board of Directors (a...@iiug.org)
>
> > See you at the 2010 IIUG Informix Conference
> > April 25-28, 2010
> > Overland Park (Kansas City), KSwww.iiug.org/conf
>
> > 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 Thu, Mar 25, 2010 at 11:33 AM, Alphonsus <karata...@gmail.com> wrote:
> > > On Mar 25, 12:19 pm, Art Kagel <art.ka...@gmail.com> wrote:
> > > > Post the set explain output please.
>
> > > > Art
>
> > > > Art S. Kagel
> > > > Advanced DataTools (www.advancedatatools.com)
> > > > IIUG Board of Directors (a...@iiug.org)
>
> > > > See you at the 2010 IIUG Informix Conference
> > > > April 25-28, 2010
> > > > Overland Park (Kansas City), KSwww.iiug.org/conf
>
> > > > 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 Thu, Mar 25, 2010 at 10:48 AM, Alphonsus <karata...@gmail.com> wrote:
> > > > > Hi all,
> > > > > I'm trying to force optimizer to use the index in the query below but
> > > > > it refuses to:
>
> > > > > ------- Informix Dynamic Server Version 10.00.FC8 -------------
>
> > > > > set explain on avoid_execute;>
> > > > > SELECT
> > > > > {+INDEX(tvh 157_398)} tvh.foo, tvh.sbar
> > > > > FROM
> > > > > tserv_venc_hora tvh
> > > > > WHERE
> > > > > tvh.num_id_hora_carga = 4213;
>
> > > > > set explain off;>
> > > > > The index name is correct. Can anyone help?
>
> > > > > TIA,
> > > > > Alphonsus.
>
> > > > > _______________________________________________
> > > > > Informix-list mailing list
> > > > > Informix-l...@iiug.org
> > > > >http://www.iiug.org/mailman/listinfo/informix-list
>
> > > sqexplain.out
>
> > > QUERY:
> > > ------
> > > SELECT {+INDEX(tvh 157_398)} tvh.foo, tvh.bar
> > > FROM tserv_venc_hora tvh
> > > WHERE tvh.num_id_hora_carga = 4213
>
> > > DIRECTIVES FOLLOWED:
> > > DIRECTIVES NOT FOLLOWED:> > > Estimated Cost: 395677
> > > Estimated # of Rows Returned: 572442
> > > Temporary Files Required For: Order By
>
> > > 1) informix.tvh: SEQUENTIAL SCAN
>
> > > Filters: informix.tvh.num_id_hora_carga = 4213
> > > _______________________________________________
> > > Informix-list mailing list
> > > Informix-l...@iiug.org
> > >http://www.iiug.org/mailman/listinfo/informix-list
Do not forget that constraints (check,unique,primary,foreign) can also
have 'hidden' names and can also be explicitly named.