ifx_row_id
Poster asks about IDS 11.5's ifx_row_id: what datatype it is, why queries filtering on it do a sequential scan (unlike filtering on rowid, which uses an index), and how to handle the extra checksum/version values appended when a table has vercols. Madison Pruet replies that it's internally two integers exposed externally as a VARCHAR(255), and suggests using LIKE to match rows when vercols adds the trailing fields; Clive notes the term is undocumented in the Info Center but findable via a Google site:ibm.com search. Fernando Nunes asks for the use case (fragmented tables lacking a unique index, isql-style rowid fetches) and set explain output, which still shows a sequential scan. No fix for avoiding the sequential scan is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello All,
i was reading the german newsletter and it explained about ifx_row_id.
This is a nice feature, however it does pop up some questions:
1: what is the datatype of ifx_row_id??
2: how to create a query using ifx_row_id which does not do a seq scan
eq:
set explain on;
select ifx_row_id,rowid, * from systables
where ifx_row_id = "2097796:1025"
-->> explain says seq scan.
set explain on;
select ifx_row_id,rowid, * from systables
where rowid = 1025
-->> explain says index...
3: how to get rid of version info when a table has vercols added??
and how to query that;
say
create table tessie ( a int);
insert into tessie values (1);
alter table tessie add vercols;
select ifx_row_id from tessiereturns "2097493:257:-994169306:1"
i want to do a
select ifx_row_id from tessie
where ifx_row_id ="2097493:257"
hmmm that does not work i need to do a
select ifx_row_id from tessie
where ifx_row_id ="2097493:257:-994169306:1"
which makes it not really nice to use in case of a refetch....
specially if a row is updated and the last 2 bits changed....
comments are welcome
Superboer.
BTW i searched
http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp for
ifx_row_id
that said: Nothing found.
superboer7@t-online.de wrote:
> Hello All,
>
> i was reading the german newsletter and it explained about ifx_row_id.
> This is a nice feature, however it does pop up some questions:
>
> 1: what is the datatype of ifx_row_id??
internally this is two integer columns. The external representation is
as a varchar 255.
> 2: how to create a query using ifx_row_id which does not do a seq scan
> eq:
>
> set explain on;
> select ifx_row_id,rowid, * from systables
> where ifx_row_id = "2097796:1025"
> -->> explain says seq scan.
> set explain on;
> select ifx_row_id,rowid, * from systables
> where rowid = 1025
> -->> explain says index...>
> 3: how to get rid of version info when a table has vercols added??
> and how to query that;
Use 'like'
>
> say
> create table tessie ( a int);
> insert into tessie values (1);
> alter table tessie add vercols;>
> select ifx_row_id from tessie> returns "2097493:257:-994169306:1"
>
> i want to do a
>
> select ifx_row_id from tessie
> where ifx_row_id ="2097493:257">
> hmmm that does not work i need to do a
> select ifx_row_id from tessie
> where ifx_row_id ="2097493:257:-994169306:1">
> which makes it not really nice to use in case of a refetch....
> specially if a row is updated and the last 2 bits changed....
>
>
> comments are welcome
>
> Superboer.
>
> BTW i searched
>
> http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp for
> ifx_row_id
> that said: Nothing found.
>
>
>
>
>
>
superboer7@t-online.de wrote: > BTW i searched > > http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp for > ifx_row_id > that said: Nothing found. > > OTOH if you put site:ibm.com ifx_row_id into google you do get one hit Which shows that google knows best :-) -- Clive
Hello Clive, Madison, thanks for the response, however i still do not understand how to avoid a seq. scan. please advice Superboer.
superboer7@t-online.de wrote:
> Hello Clive, Madison,
>
> thanks for the response, however i still do not understand how to
> avoid a seq. scan.
>
>
> please advice
>
> Superboer.
Hmmmm... nice to see someone playing with this.
Can you give us an explanation on what you're trying to do?
Also, please do:
database stores_demo;
alter table customer add vercols;
select
ifx_row_id,
(select partnum from systables where tabname = 'customer'),
rowid,
ifx_insert_checksum,ifx_row_version
from
customer
where
customer_num = 101;
Then do a SELECT * FROM customer by ifx_row_id and the same by rowid.
Post the sqexplain.
But anyway, I think we should concentrate on what your trying to do.
Regards
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
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.
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 ??? ....
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'
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.
Superboer