Results 1 to 3 of 3

Thread: select in table

  1. #1
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1

    Unanswered: select in table

    Hi gurus informix,

    I doing a select in table xxxxx, the field in the where clause is unique index, I am executing set explain and the cost is this.

    Estimated Cost: 7668

    The table have 3130165 rows.

    How can low the Estimated Cost.

    Thank you for you help.

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    1) what ids version?
    2) copy here all result of set explain and select statment...

    if you want to know how make optimization in SQL my suggestion is:
    - manual IBM Informix Performance Guide
    - slides of IIUG conferences ( www.iiug.org )


    here you have links to manual, tutorial, etc..
    http://www-1.ibm.com/support/docview...id=swg21250290
    ________________________________________
    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).
    ________________________________________

  3. #3
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    The version is 9.40 UC7

    QUERY:
    ------
    select {+explain avoid_execute} first 1 totgn_rdtotgne_c, totgn_feulttot_k from informix.prtotgne
    where totgn_rdcodope_k = 3
    and totgn_feulttot_k <= '2007-09-20'
    and totgn_rdtipgar_p = 2
    and totgn_rdclvope_k = 9999
    and totgn_rdmodal_p = 4115
    and totgn_rdsubmod_p = 9999
    and totgn_rdcodpro_p = 99
    and totgn_rdrngimp_k = 99
    order by totgn_feulttot_k desc

    DIRECTIVES FOLLOWED:
    EXPLAIN
    AVOID_EXECUTE
    DIRECTIVES NOT FOLLOWED:

    Estimated Cost: 10629
    Estimated # of Rows Returned: 1

    1) informix.prtotgne: INDEX PATH

    (1) Index Keys: totgn_rdcodope_k totgn_feulttot_k totgn_rdtipgar_p totgn_rdclvope_k totgn_rdmodal_p totgn_rdsubmod_p totgn
    _rdcodpro_p totgn_rdrngimp_k (Key-First) (Serial, fragments: ALL)
    Lower Index Filter: (informix.prtotgne.totgn_rdcodope_k = 3 AND informix.prtotgne.totgn_feulttot_k <= 20070920 )
    Key-First Filters: (informix.prtotgne.totgn_rdcodpro_p = 99 ) AND
    (informix.prtotgne.totgn_rdmodal_p = 4115 ) AND
    (informix.prtotgne.totgn_rdrngimp_k = 99 ) AND
    (informix.prtotgne.totgn_rdsubmod_p = 9999 ) AND
    (informix.prtotgne.totgn_rdclvope_k = 9999 ) AND
    (informix.prtotgne.totgn_rdtipgar_p = 2 )

    Thank you for you help.

Posting Permissions

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