RE: fragment by expression question

excerpt from Performance Guide page 6-23 -- The database server considers only simple expressions or multiple simple expressions combined with certain operators for fragment elimination. A simple expression consists of the following parts: column operator value Simple Expression Part Description column Is a single column name. Dynamic Server supports fragment elimination on all column types except columns that are defined with the NCHAR, NVARCHAR, BYTE, and TEXT data types. operator Must be an equality or range operator. value Must be a literal or a host variable. -- from the statement "value Must be a literal or a host variable." I am assuming MOD() is a function so this is not considered for fragment elimination Will >===== Original Message From "MATTHEW H. DEVLIN" <the_griffon@my-deja.com> ===== >why are all fragments being scanned? I thought that fragments would be >eliminated based on the expression and after not seeing fragments >dropped when querying on just ia_group_id I figured i would use the >exact expression used in fragmenting and was suprised that all fragments >were scanned. > >could this have to do with the fact that the index for this table is not >in a seperate dbspace? > >QUERY: >------ >select * from inv_avail where mod(ia_group_id,5) = 0> >Estimated Cost: 2 >Estimated # of Rows Returned: 1 > >1) informix.inv_avail: SEQUENTIAL SCAN (Serial, fragments: ALL) > > Filters: mod(informix.inv_avail.ia_group_id , 5 ) = 0 > > > > >In article <387389E8.33228003@bloomberg.net>, > kagel@bloomberg.net wrote: >> "MATTHEW H. DEVLIN" wrote: >> > >> > can anyone offer any input on this expression? is there any reason >we >> > should not use it? We are doing it this way to essentially get >round >> > robin type distribution but allowing the optimizer to do fragment >> > elimination. >> > >> > FRAGMENT BY EXPRESSION >> > (mod(ia_group_id,5) = 0) IN db1, >> > (mod(ia_group_id,5) = 1) IN db2, >> > (mod(ia_group_id,5) = 2) IN db3, >> > (mod(ia_group_id,5) = 3) IN db4, >> > (mod(ia_group_id,5) = 4) IN db5; >> >> That's a HASH expression and it works just fine. >> >> Art S. Kagel >> > > >Sent via Deja.com http://www.deja.com/ >Before you buy. ------------------------------------------------------------ This e-mail has been sent to you courtesy of OperaMail, as a free service from Opera Software, makers of the award-winning Web Browser, Opera. Visit us at http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free e-mail account is waiting at: http://www.operamail.com/ ------------------------------------------------------------