Create unique index in asc or desc ?
My env is Linux CentOS , IDS 11.5x .
I've a table confirmx like :
seqno integer no
stkday date yes
x_code char(4) no
error_code char(5) no
trans_s char(10) no
sendingtime char(9) yes
x_type char(2) no
and its index is :
confirmx_0 informix unique/No btree seqno
Actually , This table would be inserted about 200,000 rows of data ,
first row's seqno = 1 , second row's seqno=2, inceasing seqno like serial,
and several sessions said 4 to 5 sessions would do select in
endless loop every usleep(1000) :
select * from confirmx where seqno > ? order by seqno
As you can see , I used newest seqno to query for more inserted data
if they are avaliable ...
My question is : In my case , I wonder if I change confirmx_0 index
to be descending , it would be better in performance ?
While datas insert into confirmx , it would be about 20 or less in one time,
and the select will get seqno in asc order , if I create seqno in
descending order , I thought IDS would be less cost to judge if new data
inserted , Or I am totally wrong in this idea ? index ascending or descending
only effect in composite fields index ?
It doesn't matter. Informix can use either ascending or descending key
indexes in either direction, even for returning sorted rows in reverse
index order and the efficiency is as close to equal either way that it
doesn't matter at all.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Wed, Mar 7, 2012 at 9:07 PM, MARS CHEN <hedgezzz@yahoo.com.tw> wrote:
> My env is Linux CentOS , IDS 11.5x .
>
> I've a table confirmx like :
>
> seqno integer no
> stkday date yes
> x_code char(4) no
> error_code char(5) no
> trans_s char(10) no
> sendingtime char(9) yes
> x_type char(2) no
>
> and its index is :
> confirmx_0 informix unique/No btree seqno
>
> Actually , This table would be inserted about 200,000 rows of data ,
> first row's seqno = 1 , second row's seqno=2, inceasing seqno like serial,
> and several sessions said 4 to 5 sessions would do select in
> endless loop every usleep(1000) :
>
> select * from confirmx where seqno > ? order by seqno>
> As you can see , I used newest seqno to query for more inserted data
> if they are avaliable ...
>
> My question is : In my case , I wonder if I change confirmx_0 index
> to be descending , it would be better in performance ?
>
> While datas insert into confirmx , it would be about 20 or less in one
> time,
> and the select will get seqno in asc order , if I create seqno in
> descending order , I thought IDS would be less cost to judge if new data
> inserted , Or I am totally wrong in this idea ? index ascending or
> descending
> only effect in composite fields index ?
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae9340d39e4c77d04bab21490
MARS CHEN Wrote:
===============================================================================
My env is Linux CentOS , IDS 11.5x .
I've a table confirmx like :
seqno integer no
stkday date yes
x_code char(4) no
error_code char(5) no
trans_s char(10) no
sendingtime char(9) yes
x_type char(2) no
and its index is :
confirmx_0 informix unique/No btree seqno
Actually , This table would be inserted about 200,000 rows of data ,
first row's seqno = 1 , second row's seqno=2, inceasing seqno like serial,
and several sessions said 4 to 5 sessions would do select in
endless loop every usleep(1000) :
select * from confirmx where seqno > ? order by seqno
As you can see , I used newest seqno to query for more inserted data
if they are avaliable ...
My question is : In my case , I wonder if I change confirmx_0 index
to be descending , it would be better in performance ?
While datas insert into confirmx , it would be about 20 or less in one time,
and the select will get seqno in asc order , if I create seqno in
descending order , I thought IDS would be less cost to judge if new data
inserted , Or I am totally wrong in this idea ? index ascending or descending
only effect in composite fields index ?
===============================================================================
Response:
The ascending index will be faster in this case.
Both will be utilized with similar explains on select.
The noteworthy difference occurs due to index page(node) split logic on the
inserts. Normal index page split logic divides the index keys 50-50 between
the existing page and new page. If new records are always entered in
sequence(only adding to 1 of the 2 pages), this can lead to a mass of half
used index pages. However, if the key being added represents a new high value
for the index(fragment), Informix puts the new key on a leaf page by itself.
Less work when adding index leaf pages and fewer overall index pages will give
the ascending index better performance.
Try this example and check out the oncheck -pT results.
create table confirmx(seqno int not null);
create unique index i_desc on confirmx(seqno desc);
insert into confirmx
select level from sysmaster:sysdual connect by level <= 200000;
Index Usage Report for index i_desc on fred:informix.confirmx
Average Average
Level Total No. Keys Free Bytes
----- -------- -------- ----------
1 1 31 1652
2 31 83 1014
3 2597 77 1018
----- -------- -------- ----------
Total 2629 77 1019
drop table confirmx;
create table confirmx(seqno int not null);
create unique index i_asc on confirmx(seqno);
insert into confirmx
select level from sysmaster:sysdual connect by level <= 200000;
Index Usage Report for index i_asc on fred:informix.confirmx
Average Average
Level Total No. Keys Free Bytes
----- -------- -------- ----------
1 1 15 1844
2 15 86 981
3 1299 153 18
----- -------- -------- ----------
Total 1315 153 30
HTH,
Dave Griffen