fragment by expression question

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; Sent via Deja.com http://www.deja.com/ Before you buy.

"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

In article <84vsbh$3p7$1@nnrp1.deja.com>, MATTHEW H. DEVLIN <the_griffon@my-deja.com> 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; > > Sent via Deja.com http://www.deja.com/ > Before you buy. > Seems like a decent plan to me. The biggest thing you have to worry about in fragmentation expressions is their complexity. If you make the expression too complex, the optimizer can spend significantly more time trying to figure out where it put (or needs to put) a record than the fragmentation is worth. This one is fairly simple and straight forward, so you shouldn't have any problems. Without knowing the data, it's impossible to say if there is or isn't a better one, though. -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.

There's the space issue. Round-Robin fragmentation ensures that inserts get equally distributed into dbspaces. Although not essential, it would be nice if your frag expression maintains approximately the same number of rows in each fragment. Either way, you will need to maintain a closer watch on space utilization of these dbspaces, especially if other tables use them as well. Rudy "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; > > Sent via Deja.com http://www.deja.com/ > Before you buy.

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.

"MATTHEW H. DEVLIN" wrote: > > 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. Most likely only the one fragment is actually being scanned. The selection of fragments was defered from PREPARE time to EXECUTION time in recent versions so that queries with replaceable parameters would not scan all fragments since the decision of which to scan often cannot be made without the actual values for the parameters. Because of this the final determination of which dbspaces to scan often does not show up in the sqexplain.out file. To test this out zero stats and run the query again then look at the onstat -D or onstat -g iof output to see which chunks are actually being hit (assuming the rows are not still in cache which would cause no I/Os). Art S. Kagel > 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.

I followed your sugesstion and cleared my stats then ran this query: select * from inv_avail where mod(ia_group_id,5) = 4 You would expect to see read activity on just db5 but the engine sequentially scanned each dbspace, and I also noticed that they were done one at a time. I thought that the dbspaces would be scanned in parrallel? In article <3874D98D.FA1B513B@bloomberg.net>, kagel@bloomberg.net wrote: > "MATTHEW H. DEVLIN" wrote: > > > > 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. > > Most likely only the one fragment is actually being scanned. The selection > of fragments was defered from PREPARE time to EXECUTION time in recent > versions so that queries with replaceable parameters would not scan all > fragments since the decision of which to scan often cannot be made without > the actual values for the parameters. Because of this the final > determination of which dbspaces to scan often does not show up in the > sqexplain.out file. To test this out zero stats and run the query again > then look at the onstat -D or onstat -g iof output to see which chunks are > actually being hit (assuming the rows are not still in cache which would > cause no I/Os). > > Art S. Kagel > > > 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. > Sent via Deja.com http://www.deja.com/ Before you buy.

"MATTHEW H. DEVLIN" wrote: > > I followed your sugesstion and cleared my stats then ran this query: > select * from inv_avail where mod(ia_group_id,5) = 4> > You would expect to see read activity on just db5 but the engine > sequentially scanned each dbspace, and I also noticed that they were > done one at a time. I thought that the dbspaces would be scanned in > parrallel? > What's the value of PDQPRIORITY? -- John Carlson Informix DBA WHSmith USA #include std_disclaimer.h /* These are my opinions, not my company's opinion */

In article <387653B9.430CB7C8@bellsouth.net>, "Carlson@WHSmith" <carlson1@bellsouth.net> wrote: > "MATTHEW H. DEVLIN" wrote: > > > > I followed your sugesstion and cleared my stats then ran this query: > > select * from inv_avail where mod(ia_group_id,5) = 4> > > > You would expect to see read activity on just db5 but the engine > > sequentially scanned each dbspace, and I also noticed that they were > > done one at a time. I thought that the dbspaces would be scanned in > > parrallel? > > > > What's the value of PDQPRIORITY? It is set at 100 > > -- > John Carlson > Informix DBA > WHSmith USA > > #include std_disclaimer.h /* These are my opinions, not my company's > opinion */ > Sent via Deja.com http://www.deja.com/ Before you buy.