Re: an index question

dcruncher4 (AT) aim (DOT) com wrote:

In article <fle19n011r2 (AT) drn (DOT) newsguy.com>, dcruncher4 (AT) aim (DOT) com says...
create index aaaa on table(col1 asc, col2 desc, col3 asc)

The performance gain on the view was incredible.
What can be the reason behind it. Can anyone explain
the internals behind it.

The only problem I see is that with this index I can not
create a PKY or a unique constraint, and for that I am
forced to create another index.

I take it back. PKY can be created on an index with
desc columns but not as part of create table syntax.
Index has to be created first explicitly and then
PKY should be created. IDS will use the same
index.
Does that mean that there is some sort of a bug
here because one way of creating PKY allows
desc index to be used, while the other one does
not.

When you "create index" by defining a PK, Informix will automatically create an
index with the defaults. The defaults are to use the ASC, to create the index
using a special name etc.
There is no BUG, only "defaults" in action.
As for the performance increase, if you imagine a physical representation of
the index it's easier to understand it...:

1 1 1
1 1 2
1 1 3
....
1 9 1

(this is the "default")

1 9 1
1 8 1
....
1 1 1

(this is the "desc" )

It's more probable that the "max(col2)" is "closer" from the record you're
looking at in the second case...

The real gain will probably vary a lot depending on your data distributions.

Regards.

--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

dcruncher4 (AT) aim (DOT) com wrote:

In article <fle19n011r2 (AT) drn (DOT) newsguy.com>, dcruncher4 (AT) aim (DOT) com says...

create index aaaa on table(col1 asc, col2 desc, col3 asc)

The performance gain on the view was incredible.
What can be the reason behind it. Can anyone explain
the internals behind it.

The only problem I see is that with this index I can not
create a PKY or a unique constraint, and for that I am
forced to create another index.


I take it back. PKY can be created on an index with
desc columns but not as part of create table syntax.
Index has to be created first explicitly and then
PKY should be created. IDS will use the same
index.

Correct.

Does that mean that there is some sort of a bug
here because one way of creating PKY allows
desc index to be used, while the other one does
not.

Not a bug. Likely just a design decision. The Primary Key syntax
doesn't allow for specifying the order of each column whether it's
within the CREATE TABLE statement or outside it. You can only use a
partially or wholly descending index for a primary key by creating the
index first.

Art S. Kagel

_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list

dcruncher4 (AT) aim (DOT) com wrote:

I am using IDS 7.31

Consider this table

(col1 integer,
col2 datetime,
col3 integer,
... other columns
)
pky = col1 + col2 + col3

The problem is that the design of this table is bit odd. Everytime a change
is made to a row of this table, a new row is created with a new timestamp
(col2 in this case) and rest of the columns remain same, except the one
which changed.

Obviously active row is considered as something with the greatest col2
for a combo of col1+col3. An appropriate view was declared to show
only the most active row

create view aaa as
select col1,col3,col4 ...., (select max(col2) from table t2 where t2.col1 =
t1.col1 and t2.col3 = t1.col3) from table t1

The performance of this query wasn't good on large set of data.
I then decided to try this

create index aaaa on table(col1 asc, col2 desc, col3 asc)

The performance gain on the view was incredible.
What can be the reason behind it. Can anyone explain
the internals behind it.

The only problem I see is that with this index I can not
create a PKY or a unique constraint, and for that I am
forced to create another index.

You'd probably also get even better performance improvement from
modifying the index/primary key as:

CREATE UNIQUE INDEX mytable_pk ON mytable( col1, col3, col2 desc );
ALTER TABLE mytable ADD CONSTRAINT PRIMARY KEY (col1, col3, col2 );

the order of the columns in the index are not relevant to the quality of
the primary key, so...

Art S. Kagel

TIA.

_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list