Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2002
    Posts
    6

    Question Unanswered: delete statement on a fragment by expression table

    hi,

    I have a "fragment by expression table"
    and when i use a delete statement with a "where" using the same expression
    it scans 3 fragments instead of one .
    why?

    Thanks to all.
    See bellow description of table and delete statement.

    QUERY:
    ------
    delete from mes where yeardmes=2003

    Estimated Cost: 649528
    Estimated # of Rows Returned: 83135400

    1) informix.mes: SEQUENTIAL SCAN (Serial, fragments: 3)

    Filters: informix.mes.yeardmes = 2003



    CREATE TABLE "informix".mes (
    dmes DATE ,
    rang SMALLINT ,
    ienvm INTEGER ,
    iconst CHAR(2) ,
    isitm CHAR(5) ,
    iaasqa INTEGER ,
    val FLOAT ,
    daydmes SMALLINT,
    monthdmes SMALLINT,
    yeardmes SMALLINT
    ) FRAGMENT by expression
    yeardmes <2001 in data1_dbs,
    yeardmes =2001 in data2_dbs,
    yeardmes =2002 in data3_dbs,
    yeardmes =2003 in data4_dbs

  2. #2
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534

    Re: delete statement on a fragment by expression table

    This is not expected behaviour...

    What happens if you do a select?
    select * from mes where yearmes = 2003;

    If this does not point to fragment 4,

    What does it doe when you:
    select * from mes where yearmes > 2002;

    What is the value of your OPTCOMPIND parameter in the $INFORMIXDIR/etc/$ONCONFIG file?
    Do you perhaps have OPTIMIZATION LOW?
    Do you use the first rows parameter?
    rws

  3. #3
    Join Date
    Oct 2002
    Posts
    6

    Re: delete statement on a fragment by expression table

    Hi,

    Thanks for the reply.

    1)
    select * from mes where yeardmes = 2003;

    Estimated Cost: 665202
    Estimated # of Rows Returned: 10938720

    1) informix.mes: SEQUENTIAL SCAN (Serial, fragments: 3)

    Filters: informix.mes.yeardmes = 2003


    If this does not point to fragment 4,

    2)
    What does it doe when you:

    select * from mes where yeardmes > 2002

    Estimated Cost: 665202
    Estimated # of Rows Returned: 36462400

    1) informix.mes: SEQUENTIAL SCAN (Serial, fragments: 3)

    Filters: informix.mes.yeardmes > 2002

    3)
    What is the value of your OPTCOMPIND parameter in the $INFORMIXDIR/etc/$ONCONFIG file?
    OPTCOMPIND 2
    Do you perhaps have OPTIMIZATION LOW?
    no
    Do you use the first rows parameter?
    No

    Thanks,




    Originally posted by Roelwe
    This is not expected behaviour...

    What happens if you do a select?
    select * from mes where yearmes = 2003;

    If this does not point to fragment 4,

    What does it doe when you:
    select * from mes where yearmes > 2002;

    What is the value of your OPTCOMPIND parameter in the $INFORMIXDIR/etc/$ONCONFIG file?
    Do you perhaps have OPTIMIZATION LOW?
    Do you use the first rows parameter?

  4. #4
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    Ok,

    Could you do an
    UPDATE STATISTICS HIGH on this table
    and also on the fragmentation column?
    Does it make a difference?
    rws

Posting Permissions

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