Re: ifx_row_id

Hello Fernando, > Madison wrote something like "use like". I didn't get what he was answering... that was because ifx_row_id contained the vercols which can nuke things when you do a refetch of a changed data record. explain is not lying: sysptprof says seq scan: dbsname xx tabname customer partnum 1049331 lockreqs 0 lockwts 0 deadlks 0 lktouts 0 isreads 2 iswrites 0 isrewrites 0 isdeletes 0 bufreads 7 bufwrites 0 seqscans 1 pagreads 3 pagwrites 0 > Sometimes using wrong type arguments in expressions can invalidate the usage of > an index. An ROWID (traditionally) is an INT... and you're comparing it to a > CHAR/VARCHAR... again... this probably doesn't make sense since ROWID is an > internal thing and not exactly a index... yeah that is the problem; it would be nice to make it available using a rowtype or something so no conversion has to take place so it can go directly to partno and rowid . would make a really nice feature request.... Thanks anyway for your responce Superboer On 5 feb, 01:52, Fernando Nunes <domusonl...@gmail.com> wrote: > superbo...@t-online.de wrote: > > Hello Fernando, > > > remember isql this uses rowids and is not able to fetch data from > > fragmented tables unless one adds rowids to the table > > which is not so nice. > > Yes... That's a problem... > > > using ifx_row_id and no seq scans could fix that. besides this i am > > hacking around to do something simular; > > sometimes a table does not have a unique index... (well yeah i > > know.... tell it to the folks who build stuff like that) > > anyways in order to query and identify a row uniquely one can use a > > rowid. > > This will work only when the table is not fragmented. > > > another example is a fragmented table without a unique index and kick > > out a duplicate record... yeah yeah.. > > ..... hmmmm unloading around corruption caused by ??? .... > > Yes. Again the fragmented tables issue. > > > > > > > Then i saw ifx_row_id which OH YEAH can do so; however it does a seq > > scan..... > > this is not so nice when one has a huge table.... > > ok your questions: > > > ifx_row_id          1049331:257:1124131158:1 > > (expression)        1049331 > > rowid               257 > > ifx_insert_checks+  1124131158 > > ifx_row_version     1 > > > QUERY: (OPTIMIZATION TIMESTAMP: 02-04-2009 09:00:44) > > ------ > > SELECT * FROM customer where  ifx_row_id  ="1049331:257:1124131158:1"> > > Estimated Cost: 3 > > Estimated # of Rows Returned: 1 > > >   1) informix.customer: SEQUENTIAL SCAN > > >         Filters: informix.customer.ROWID = '1049331:257:1124131158:1' > > Did you notice how the query is "re-written"? It's just comparing rowid to > ifx_row_id. It's completely different.  The interesting part is that it finds > the row... > > Madison wrote something like "use like". I didn't get what he was answering... > 2) or 3)... > I wasn't able to make any serious tests... > > > > > > > Query statistics: > > ----------------- > > >   Table map : > >   ---------------------------- > >   Internal name     Table name > >   ---------------------------- > >   t1                customer > > >   type     table  rows_prod  est_rows  rows_scan  time       est_cost > >   ------------------------------------------------------------------- > >   scan     t1     1          1         28         00:00.00   4 > > > Madison told that the info is to ints which i guess is the partnum of > > the fragment and the oldfashion rowid. > > this is returned as a varchar(255)... hmmm maybe a rowtype of 2 ints > > would be better so it can pick the partnum > > and the rowid to avoid a sec scan... ??? > > There is not a really good reason to do a seq scan  as far as i can > > tell. > > correct me if i am wrong. > > I don't even understand clearly how it's comparing "ROWID" with ifx_row_id. > But ROWID is really an internal stuff, so anything is possible. What I would > like to test is the response time... does it really scan all the table pages? > Or is the sequential scan some weird output of the explain? You can use > SQLTRACE to get detailed info about this... or just test it on a bigger table. > > Sometimes using wrong type arguments in expressions can invalidate the usage of > an index. An ROWID (traditionally) is an INT... and you're comparing it to a > CHAR/VARCHAR... again... this probably doesn't make sense since ROWID is an > internal thing and not exactly a index... > > Need some testing or some light from "above" :) > > Regards. > > > > > Superboer > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently...