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

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?

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,

Ok,

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