Hello.

I'm having one problem with one query...


When I comment the line item_custo_comp is null or item_custo_comp = '', the command work, but when I uncomment this line, the query take long and long time and not work.

So.. I was thinking ... The informix uses index when I put in SQL the expression IS NULL ??

How Can I check this... ?

It's very strange, cuz the same query was running a few time ago...

Maybe the tables grow and ..now the SQL can't run without to use index.

Someone can help me ?

// Execute
SELECT item_custo.cod_item FROM item_custo ,item
,tipo_trat_item WHERE item_custo.cod_empresa = '20' AND
--(item_custo.cod_comp_custo IS NULL OR item_custo.cod_comp_custo = ' ') AND
item_custo.cod_empresa = item.cod_empresa AND item_custo.cod_item =
item.cod_item AND item.ies_tip_item = tipo_trat_item.ies_tip_item AND
item.ies_situacao = 'A' AND tipo_trat_item.cod_empresa = '20' AND
tipo_trat_item.ies_tip_versao = 'C' AND tipo_trat_item.ies_comprado = 'S'
AND item_custo.cod_item IN ( SELECT UNIQUE (cod_item_compon) FROM
consumo_teorico WHERE consumo_teorico.cod_empresa = '20' AND
consumo_teorico.num_versao_cus = 708)

what are you trying to do?

what version of IDS did you are using?
Do you already try see the SET EXPLAIN output to get the plan optimizer?
The update statistics for those tables was updated?

Probably the optimizer are executing a sequential scan when you add this clauses. I don't remember if have some exception when use the NULL values...
if you don't find any solution try change this line to this:
more about set explain:
http://publib.boulder.ibm.com/infoce...61%69%6e%22%20

more about NVL function:
http://publib.boulder.ibm.com/infoce...6e%76%6c%22%20

Just trying to execute this select...

This select was working and after my database grow, it not work more.

It not was modified...

IBM Informix Dynamic Server Version 9.40.UC3

Hum..

My Statistics is


UPDATE STATISTICS MEDIUM FOR TABLE item_custo (cod_item) DISTRIBUTIONS ONLY;


But my table Item_Custo, uses cod_item to link with other query.

So, can be this my problem ?

opss... sorry... not consider this post... I read wrong. I read
"... IS NULL OR A ANSI OUTER..." but is "ON"

If you can , try change this update statistics to all columns what you using in WHERE clauses of Selects
or all columns

Solved.. was statistic. Thanks for all.