Re: ifx_row_id

superboer7@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...