RE: fragment by expression question

I agree the elimination would help, though I am not really sure how much. If you don't fragment your index on the table, it should only be at most 2 levels further down(more likely one) than if you had a fragmented index, so you have to go through one more index level per lookup. That doesn't sound to horrible to me. I might be missing something though. Using the frag_id algorithm, you could calculate which fragment it is in, use the frag_id and the ia_group_id and I believe you would get the fragment elimination. I would think it actually might be faster for reads to just have a detached, non-fragmented index for this, but once again am just hypothesizing and haven't tested this. For writes, on the other hand, it might be better to have a fragmented index and calculate the frag_id (That way the one index wouldnt become a point of contention for all the inserts into this table). It all depends on the characteristics of your system. All standard disclaimers of me actually knowing what I am talking about apply. Will >===== Original Message From "Matthew H. Devlin III" <mhdevlin@nycap.rr.com> ===== >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 >> ... >> <SNIP> > 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. ------------------------------------------------------------ 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/ ------------------------------------------------------------