Some indexing additions:

1) Inclusion of columns in the index that are not part of the index key
but help speed processing. This feature is available in a number of
other rdbms's.
2) Use more than one index on a table to filter the result set.


sending to informix-list


sending to informix-list

Mark Denham wrote:
> Some indexing additions:
>
> 1) Inclusion of columns in the index that are not part of the index
> key but help speed processing. This feature is available in a number
> of other rdbms's.

Waaaaaa? Please explain?

> 2) Use more than one index on a table to filter the result set.

Ditto...

Andrew Hamm wrote:

> Mark Denham wrote:
>
>>Some indexing additions:
>>
>>1) Inclusion of columns in the index that are not part of the index
>>key but help speed processing. This feature is available in a number
>>of other rdbms's.
>
>
> Waaaaaa? Please explain?
This feature applies to unique indexes.
Let's presume you have an employee table. The PK is empno.
You want to optimize lookups of employee names by empno.
Without this feature the fastest to do this (assuming a non trivial
table size) is to fetch the rowid from the index and then do an fetch by
rowid from the table to get the name.
If you INCUDE empname in the unique index then you save the the table
access. Another way of looking at this is as a non-unique index with a
unique subset.
>
>>2) Use more than one index on a table to filter the result set.
>
> Ditto...
Index ANDing. Let's presume an index on the employee table by name and
one on department. You want to look up Mr. Smith in marketing.
The DBMS first collects all rowid's for Smith and then all for the
employees in the marketing department. It can then intersect the sets
and go after the real rows.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Serge Rielau wrote:

> Another way of looking at this is as a non-unique index with a
> unique subset.
Stupid... scratch that sentence.

Cheers
Serge

PS: I'm I'm looking forward to leran how temp tables work in Informix
today. I know why I want catalogued temps in DB2, but these reasons may
not apply to IDS....

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

"Serge Rielau" <srielau@ca.eye-be-em.com> wrote in message
news:c0k22f$5mg$1@hanover.torolab.ibm.com...
> Andrew Hamm wrote:
>
> > Mark Denham wrote:
> >
> >>Some indexing additions:
> >>
> >>1) Inclusion of columns in the index that are not part of the index
> >>key but help speed processing. This feature is available in a number
> >>of other rdbms's.
> >
> >
> > Waaaaaa? Please explain?
> This feature applies to unique indexes.
> Let's presume you have an employee table. The PK is empno.
> You want to optimize lookups of employee names by empno.
> Without this feature the fastest to do this (assuming a non trivial
> table size) is to fetch the rowid from the index and then do an fetch by
> rowid from the table to get the name.
> If you INCUDE empname in the unique index then you save the the table
> access. Another way of looking at this is as a non-unique index with a
> unique subset.

How about including *all* columuns in the PK? Then you'd never have to
access the data at all :-)

Neil Truby wrote:

> "Serge Rielau" <srielau@ca.eye-be-em.com> wrote in message
> news:c0k22f$5mg$1@hanover.torolab.ibm.com...
>
>>Andrew Hamm wrote:
>>
>>
>>>Mark Denham wrote:
>>>
>>>
>>>>Some indexing additions:
>>>>
>>>>1) Inclusion of columns in the index that are not part of the index
>>>>key but help speed processing. This feature is available in a number
>>>>of other rdbms's.
>>>
>>>
>>>Waaaaaa? Please explain?
>>
>>This feature applies to unique indexes.
>>Let's presume you have an employee table. The PK is empno.
>>You want to optimize lookups of employee names by empno.
>>Without this feature the fastest to do this (assuming a non trivial
>>table size) is to fetch the rowid from the index and then do an fetch by
>>rowid from the table to get the name.
>>If you INCUDE empname in the unique index then you save the the table
>>access. Another way of looking at this is as a non-unique index with a
>>unique subset.
>
>
> How about including *all* columuns in the PK? Then you'd never have to
> access the data at all :-)
>
>

Wouldn't that violate some normal form? ;-)
I dimply recall soem passages about a candidate key being a minimum set
of column which are unique.
Thsi is all about saving index space. E.g. you want to get the benefit
of an index + a unique constraint.
Some RDBMS will pick up existing indexes when a a primary key is added.
You would create the table, then an index with include columns, then
alter the table to add primary key.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Serge Rielau wrote:
> Neil Truby wrote:
>> "Serge Rielau" <srielau@ca.eye-be-em.com> wrote:
>>> Andrew Hamm wrote:
>>>> Mark Denham wrote:
>>>>> Some indexing additions:
>>>>>
>>>>> 1) Inclusion of columns in the index that are not part of the index
>>>>> key but help speed processing. This feature is available in a number
>>>>> of other rdbms's.
>>>>
>>>> Waaaaaa? Please explain?
>>>
>>> This feature applies to unique indexes.
>>> Let's presume you have an employee table. The PK is empno.
>>> You want to optimize lookups of employee names by empno.
>>> Without this feature the fastest to do this (assuming a non trivial
>>> table size) is to fetch the rowid from the index and then do an fetch by
>>> rowid from the table to get the name.
>>> If you INCUDE empname in the unique index then you save the the table
>>> access. [...strike incorrect sentence...]

I don't see that it has to apply solely to unique indexes. It
basically allows a key-only scan to also pick up the other critical
data without having to fetch the data page. So, if you've got a table
of zip-codes and state codes, you index on the zip-code - which
probably needs to be unique - and include the state code too, and you
never have to go to the table which contains demographic data stored
in blobs for the state code.

The Orrible database has a feature along these lines, I believe.

>> How about including *all* columuns in the PK? Then you'd never have to
>> access the data at all :-)

An extreme case, but there has been a request for index-only tables.

> Wouldn't that violate some normal form? ;-)

No; normal forms apply to the table, not to the indexes on the table.

> I dimply recall soem passages about a candidate key being a minimum set
> of column which are unique.
> Thsi is all about saving index space. E.g. you want to get the benefit
> of an index + a unique constraint.
> Some RDBMS will pick up existing indexes when a a primary key is added.
> You would create the table, then an index with include columns, then
> alter the table to add primary key.

The index has to be unique - but the problem is that normally, the
'larger key' would not enforce the uniqueness correctly. If you have
a unique index on columns A, B, and C, but A and B alone are the true
primary key, then that unique index does not automatically enforce the
primary key. You could have rows A1, B1, C1 and A1, B1, C2 which are
unique according to the index but have the same primary key. If (a
modified version of) the DBMS is aware that A, B must be unique but
that C should also be indexed, then it can use the index and enforce
the correct uniqueness.

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Jonathan Leffler wrote:

> Serge Rielau wrote:
>
>> Neil Truby wrote:
>>
>>> "Serge Rielau" <srielau@ca.eye-be-em.com> wrote:
>>>
>>>> Andrew Hamm wrote:
>>>>
>>>>> Mark Denham wrote:
>>>>>
>>>>>> Some indexing additions:
>>>>>>
>>>>>> 1) Inclusion of columns in the index that are not part of the index
>>>>>> key but help speed processing. This feature is available in a number
>>>>>> of other rdbms's.
>>>>>
>>>>>
>>>>> Waaaaaa? Please explain?
>>>>
>>>>
>>>> This feature applies to unique indexes.
>>>> Let's presume you have an employee table. The PK is empno.
>>>> You want to optimize lookups of employee names by empno.
>>>> Without this feature the fastest to do this (assuming a non trivial
>>>> table size) is to fetch the rowid from the index and then do an
>>>> fetch by
>>>> rowid from the table to get the name.
>>>> If you INCUDE empname in the unique index then you save the the table
>>>> access. [...strike incorrect sentence...]
>
>
> I don't see that it has to apply solely to unique indexes. It basically
> allows a key-only scan to also pick up the other critical data without
> having to fetch the data page. So, if you've got a table of zip-codes
> and state codes, you index on the zip-code - which probably needs to be
> unique - and include the state code too, and you never have to go to the
> table which contains demographic data stored in blobs for the state code.
>
The result is a unique index none the less. If a part of the index is
unique all is unique.

> The Orrible database has a feature along these lines, I believe.
No need to look that far:
CREATE TABLE T1 (zip CHAR(6) NOT NULL,
province VARCHAR(20),
area BIGINT);
CREATE UNIQUE INDEX i1 ON T1(zip) INCLUDE (province);
ALTER TABLE T1 ADD PRIMARY KEY (zip);
SQL0598W Existing index "SRIELAU.I1" is used as the index for the
primary key or a unique key. SQLSTATE=01550

http://publib.boulder.ibm.com/infoce...n/r0000888.htm

>>> How about including *all* columuns in the PK? Then you'd never have to
>>> access the data at all :-)
>
>
> An extreme case, but there has been a request for index-only tables.
>
>> Wouldn't that violate some normal form? ;-)
>
>
> No; normal forms apply to the table, not to the indexes on the table.
Reread the PK line ;-)

>
>> I dimply recall soem passages about a candidate key being a minimum
>> set of column which are unique.
>> Thsi is all about saving index space. E.g. you want to get the benefit
>> of an index + a unique constraint.
>> Some RDBMS will pick up existing indexes when a a primary key is added.
>> You would create the table, then an index with include columns, then
>> alter the table to add primary key.
>
>
> The index has to be unique - but the problem is that normally, the
> 'larger key' would not enforce the uniqueness correctly. If you have a
> unique index on columns A, B, and C, but A and B alone are the true
> primary key, then that unique index does not automatically enforce the
> primary key. You could have rows A1, B1, C1 and A1, B1, C2 which are
> unique according to the index but have the same primary key. If (a
> modified version of) the DBMS is aware that A, B must be unique but that
> C should also be indexed, then it can use the index and enforce the
> correct uniqueness.
We agree violently!

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

"Jonathan Leffler" <jleffler@earthlink.net> wrote in message
news:402EEA68.9010107@earthlink.net...
> Serge Rielau wrote:
> > Neil Truby wrote:
> >> "Serge Rielau" <srielau@ca.eye-be-em.com> wrote:
> >>> Andrew Hamm wrote:
> >>>> Mark Denham wrote:
> >>>>> Some indexing additions:
> >>>>>
> >>>>> 1) Inclusion of columns in the index that are not part of the index
> >>>>> key but help speed processing. This feature is available in a number
> >>>>> of other rdbms's.
> >>>>
> >>>> Waaaaaa? Please explain?
> >>>
> >>> This feature applies to unique indexes.
> >>> Let's presume you have an employee table. The PK is empno.
> >>> You want to optimize lookups of employee names by empno.
> >>> Without this feature the fastest to do this (assuming a non trivial
> >>> table size) is to fetch the rowid from the index and then do an fetch
by
> >>> rowid from the table to get the name.
> >>> If you INCUDE empname in the unique index then you save the the table
> >>> access. [...strike incorrect sentence...]
>
> I don't see that it has to apply solely to unique indexes. It
> basically allows a key-only scan to also pick up the other critical
> data without having to fetch the data page. So, if you've got a table
> of zip-codes and state codes, you index on the zip-code - which
> probably needs to be unique - and include the state code too, and you
> never have to go to the table which contains demographic data stored
> in blobs for the state code.
>
> The Orrible database has a feature along these lines, I believe.

This is also a feature of DB2..

"Madison Pruet" <mpruet@comcast.net> wrote in message
news:rmOXb.324282$na.477931@attbi_s04...
>
> "Jonathan Leffler" <jleffler@earthlink.net> wrote in message
> news:402EEA68.9010107@earthlink.net...
> > Serge Rielau wrote:
> > > Neil Truby wrote:
> > >> "Serge Rielau" <srielau@ca.eye-be-em.com> wrote:
> > >>> Andrew Hamm wrote:
> > >>>> Mark Denham wrote:
>
> This is also a feature of DB2..

Madison, could we try to avoid turning this thread into an "us-vrs-them"
thread? :-)