Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2007
    Posts
    61

    Unanswered: Can't Execute SQL Query, Maybe problem in index?

    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)

  2. #2
    Join Date
    Aug 2008
    Posts
    1
    what are you trying to do?

  3. #3
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    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:
    Code:
    NVL(item_custo.cod_comp_custo,' ') = ' '
    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
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  4. #4
    Join Date
    Oct 2007
    Posts
    61
    Just trying to execute this select...

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

    It not was modified...

  5. #5
    Join Date
    Oct 2007
    Posts
    61
    IBM Informix Dynamic Server Version 9.40.UC3

  6. #6
    Join Date
    Oct 2007
    Posts
    61
    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 ?

  7. #7
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311

    Red face

    opss... sorry... not consider this post... I read wrong. I read
    "... IS NULL OR A ANSI OUTER..." but is "ON"
    maybe can be a BUG:

    II. CUSTOMER-REPORTED BUGS FIXED IN 9.40.UC9
    173099 ONLINE-SQOPTIM OPTIMISER USES SEQUENTIAL SCAN ON WHEN SELECTING IS NULL ON A ANSI OUTER JOIN COLUMN.
    Last edited by ceinma; 08-08-08 at 14:53.
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  8. #8
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    If you can , try change this update statistics to all columns what you using in WHERE clauses of Selects
    Code:
    UPDATE STATISTICS MEDIUM FOR TABLE item_custo (cod_item, cod_empresa, cod_item) DISTRIBUTIONS ONLY;
    or all columns
    Code:
    UPDATE STATISTICS MEDIUM FOR TABLE item_custo DISTRIBUTIONS ONLY;
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  9. #9
    Join Date
    Oct 2007
    Posts
    61
    Solved.. was statistic. Thanks for all.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •