Re: Here is a weird one, Not using fragmentation scheme to do fragmentation

Using MOD() to simulate the hash function is a good idea but when you're using the SELECT statement, if you want fragment elimination you cannot use MOD() or any other functions on the fragmented columns. Informix only supports RANGE or EQUALITY expression to eliminate fragment. How about trying this to see if it works: select * from mytesting where i_guest_id = 1 Also I doubt you've built an index on the column i_guest_id. That's probably why even after you've used the +AVOID_FULL directive the engine still had to perform sequential scan. It just had no other choices! HTH Dong >From: "Carlos Bolden" <cbolden@harrahs.com> >Reply-To: "Carlos Bolden" <cbolden@harrahs.com> >To: informix-list@iiug.org >Subject: Here is a weird one, Not using fragmentation scheme to do >fragmentation elmination >Date: Thu, 03 Jun 1999 19:41:03 GMT > >I have a table that looks like this > >create table mytesting >( > fnmae char(30), > lastname char (30), > i_guest_id int >) fragment by expression > (mod(i_guest_id , 14 ) = 0 ) in dbspace13, > (mod(i_guest_id , 14 ) = 1 ) in dbspace15, > (mod(i_guest_id , 14 ) = 2 ) in dbspace17, > (mod(i_guest_id , 14 ) = 3 ) in dbspace19, > (mod(i_guest_id , 14 ) = 4 ) in dbspace21, > (mod(i_guest_id , 14 ) = 5 ) in dbspace23, > (mod(i_guest_id , 14 ) = 6 ) in dbspace25, > (mod(i_guest_id , 14 ) = 7 ) in dbspace27, > (mod(i_guest_id , 14 ) = 8 ) in dbspace30, > (mod(i_guest_id , 14 ) = 9 ) in dbspace03, > (mod(i_guest_id , 14 ) = 10 ) in dbspace05, > (mod(i_guest_id , 14 ) = 11 ) in dbspace07, > (mod(i_guest_id , 14 ) = 12 ) in dbspace09, > (mod(i_guest_id , 14 ) = 13 ) in dbspace11 > lock mode row;> >This table has about 100 rows in it. Ran an update statistics high on the >table and ran the following query with explain set >select * from mytesting where mod(i_guest_id , 14 ) = 1. Sqexplain said>that this: > >select * from mytesting where mod(i_guest_id,14) = 1>Estimated Cost: 11 >Estimated # of Rows Returned: 10 > >1) informix.mytesting: SEQUENTIAL SCAN (Serial, fragments: ALL) > > Filters: mod(informix.mytesting.i_guest_id , 14 ) = 1 > >I was expecting it to do somthing like sequentila scan ( serial, fragments: >1 ) > filters: etc... > > >I am running 7.3uc8 on MP-RAS 3.02. If you need the onconfig, just reply >and I will post. OPTCOMPIND = 2, OPTIMIZATION is set to first_rows in the, >even tried it set to all rows and I got the same results. >also tried to use directives and it said this > >select {+AVOID_FULL(mytesting) } * from mytesting where mod(i_guest_id,14) >= >1 > > >DIRECTIVES FOLLOWED: >DIRECTIVES NOT FOLLOWED:>AVOID_FULL ( mytesting ) Directives rule out all access paths for table. > >Estimated Cost: 11 >Estimated # of Rows Returned: 10 > >1) informix.mytesting: SEQUENTIAL SCAN (Serial, fragments: ALL) > > Filters: mod(informix.mytesting.i_guest_id , 14 ) = 1 > >This is ( in my eyes ) is not good. Has anyone ever seen this. ( I have >opened a case with Informix ) > >We will be migrating a lot of data from 7.24 to 7.3 on another system. I >don't want to waste system resources running an onpload job to unload a 6 >or >7 million rows table that is fragmented X ways when I can take advantage >of >fragmentation elmination. I have set OPT_GOAL to -1 and 0, OPTCOMPIND to >1,2,0 ( and -1 for kicks ) and I get the same results.... > > >Thanks, > >Carlos A. Bolden >Database Administrator >Harrahs Entertainment >cbolden@harrahs.com > > > > > > > _______________________________________________________________ Get Free Email and Do More On The Web. Visit http://www.msn.com