RE: fragment by expression question

I do not think the optimizer is smart enough to realize what you are doing in this query.(It doesnt realize you are trying to break things up by fragment) Without knowing where you are hoping fragment elimination will help you, I can only guess at what you are trying to do. One option you might resort to in order to allow you to break the table up for processing it in parallel is to add the logic to your program. Have a field whose value is computed using the mod(ia_group_id), I'll call it frag_id. Then you can do your fragmentation as frag_id=0 frag_id=1 ... Then if you query where frag_id = 2 it will do the fragment elimination. Of course, this means that it wont do the elimination on ia_group_id queries. I can not think of to many cases where the elimination would cause great benefit, except in allowing you to process five sets of data in parallel. I would love to see examples to the contrary... It also means that redistributing the table could cause programatic changes depending on how you go about implementing it. Hope this helps, Will >===== Original Message From "MATTHEW H. DEVLIN" <the_griffon@my-deja.com> ===== >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.

William Rice wrote: > I do not think the optimizer is smart enough to realize what > you are doing in this query.(It doesnt realize you are trying to break > things up by fragment) > > Without knowing where you are hoping fragment elimination will help > you, I can only guess at what you are trying to do. > One option you might resort to in order to allow you to break the table up > for processing it in parallel is to add the logic to your program. > Have a field whose value is computed using the mod(ia_group_id), > I'll call it frag_id. Then you can do your fragmentation as > frag_id=0 > frag_id=1 > ... > > Then if you query where frag_id = 2 it will do the fragment elimination. > Of course, this means that it wont do the elimination on > ia_group_id queries. I can not think of to many cases where the elimination > would cause great benefit, except in allowing you to process five sets of > data in parallel. I would love to see examples to the contrary... > It also means that redistributing the table could cause programatic changes > depending on how you go about implementing it. > > Hope this helps, > Will > > >===== Original Message From "MATTHEW H. DEVLIN" <the_griffon@my-deja.com> > ===== > >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. > The query I showed as an example is not one that would ever be required by > any of our programs. I was using that query because it was a replica of the > method used by the fragmentation expression. There are many cases where the > elimination of a fragment will help. The table involved has over 25,000,000 > rows and is heavly accessed by over 200 users on an average day and 6-700 on > a busy day in a OLTP environment. If I can limit queries hitting this table > to one or two fragments and spread that over the number of users it will > greatly reduce the total IO required to process these transactions.