doubts on create index
The poster asked whether Informix can build an index that excludes rows with NULL keys (as Oracle allows), to save space and reads when most values are NULL. Answers: Informix has no such option — every row gets an index entry; one reply suggested functional indexes, another (wrongly citing Codd's rules) said it's prohibited. Jonathan Leffler defended the idea as legitimate and noted an outstanding feature request for a 'unique if not null' index. Art Kagel's workaround: place the nullable column first in the key so searches prune those branches quickly. No actual Informix feature resolves it.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi Everybody,
I'd like to know if is possible to create a index in Informix that contains
just value which is not null,
Example with a table tablex
create table tablex (code integer
description char(20)
)
create index ix_tablex [parameter_to_not_insert_null_in_index] on tablex
(code);
sample of data
code description
null a
null b
1 c
2 d
select count(*) from tablex ----> returns 4 rows
if I look up the leave of index there are just 2 rows (the 2 rows which is
null are ignored)
1 c
2 d
Celso Coimbra
ClearTech Ltda
"Unlock your revenue"
Tel: (019) 2104-4509
E-mail: ccoimbra@cleartech.com.br
Not sure why you would want to do this. But maybe look at "functional indexes".
From the IDS 9.4 manual;
Using a Function as an Index Key
You can create functional indexes within an SPL routine.
You can also create an index on a nonvariant user-defined function that does
not return a large object.
A functional index can be a B-tree index, an R-tree index, or a user-defined
secondary-access method.
Functional indexes are indexed on the value that the specified function
returns, rather than on the value of a column. For example, the following
statement creates a functional index on table zones using the value that the
function Area() returns as the key:
CREATE INDEX zone_func_ind ON zones (Area(length,width));
On 8/25/06, Celso Cabral Coimbra <ccoimbra@cleartech.com.br> wrote:
>
> Hi Everybody,
>
> I'd like to know if is possible to create a index in Informix that contains
> just value which is not null,
>
> Example with a table tablex
> create table tablex (> code integer
> description char(20)
> )
> create index ix_tablex [parameter_to_not_insert_null_in_index] on tablex
> (code);>
> sample of data
> code description
> null a
> null b
> 1 c
> 2 d
>
> select count(*) from tablex ----> returns 4 rows>
> if I look up the leave of index there are just 2 rows (the 2 rows which is
> null are ignored)
> 1 c
> 2 d
>
> Celso Coimbra
> ClearTech Ltda
> "Unlock your revenue"
> Tel: (019) 2104-4509
> E-mail: ccoimbra@cleartech.com.br
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
No. A Relational Database expects that EVERY row is represented in each index.
There is no such thing as an index that does not contain a key for every row in
any properly designed RDBMS.
Art S. Kagel
----- Original Message -----
From: Celso Cabral Coimbra <ids@iiug.org>
At: 8/25 13:21:18
Hi Everybody,
I'd like to know if is possible to create a index in Informix that contains
just value which is not null,
Example with a table tablex
create table tablex (code integer
description char(20)
)
create index ix_tablex [parameter_to_not_insert_null_in_index] on tablex
(code);
sample of data
code description
null a
null b
1 c
2 d
select count(*) from tablex ----> returns 4 rows
if I look up the leave of index there are just 2 rows (the 2 rows which is
null are ignored)
1 c
2 d
Celso Coimbra
ClearTech Ltda
"Unlock your revenue"
Tel: (019) 2104-4509
E-mail: ccoimbra@cleartech.com.br
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hello Celso, The RDBMS rules [ CODD's 13 rules ] prohibit's such scenario. You could read up the following books to get a better idea :- 1] RDBMS COncepts - S. Korth 2] Informix SQL - Tutorial . There are some very basic rules to RDBMS and applicable to all database like Oracle, Informix, DB2, SQL-Server. Regards, Sagar
hi Sagar, thank you very much for your tips. I thought this doubts really was a little bit weird, so that is the reason asked everyone, because a consultant asked me as a basic feature of all RDBMS, as if the Informix was the worst database all over the world. It will help me a lot. Celso -----Mensagem original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]Em nome de SAGAR THANAWALA Enviada em: quarta-feira, 30 de agosto de 2006 06:29 Para: ids@iiug.org Assunto: Re: doubts on create index [7369] Hello Celso, The RDBMS rules [ CODD's 13 rules ] prohibit's such scenario. You could read up the following books to get a better idea :- 1] RDBMS COncepts - S. Korth 2] Informix SQL - Tutorial . There are some very basic rules to RDBMS and applicable to all database like Oracle, Informix, DB2, SQL-Server. Regards, Sagar ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
On 8/30/06, SAGAR THANAWALA <sagarthanawala@yahoo.com> wrote: > The RDBMS rules [ CODD's 13 rules ] prohibit's such scenario. You could read > up the following books to get a better idea :- > > 1] RDBMS COncepts - S. Korth > > 2] Informix SQL - Tutorial . > > There are some very basic rules to RDBMS and applicable to all database like > Oracle, Informix, DB2, SQL-Server. Interesting. The question (since the context wasn't quoted) was: > I'd like to know if is possible to create a index in Informix that contains > just value which is not null, Can you explain where you find out about Codd's 13 rules - I know of the 12 rules, and I am hard-pressed to work out which one addresses this issue, but maybe the 13th is one that does so. A follow-up suggested that the question was asked by a consultant. It would be interesting to know which DBMS the consultant used that had the feature. The obvious way to ensure no nulls are indexed is to ensure that there are no nulls in the data columns that are indexed. However, that's quibbling. The real reason for wanting to do that is because there are likely a lot of nulls in the indexed column (or columns) and by not including those rows in the index, you can reduce the size of the index and not harm queries. Queries that use the index with a constraint that implies a real (not null) value for the column will be quicker because the index is smaller - and might even be 'unique if not null'. Queries that insist on 'column IS NULL' will probably be better off doing a sequential scan anyway. So, overall, I don't consider the question as preposterous as some of the responses seem to think it is. I know we have a feature request - not near the top of the priority list - for a 'unique if not null' index type. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
Sagar, sorry for the late answer, The RDBMS that the consultant still uses is the Oracle and the propose of create an index without null is to reduce the space and the number of reading like you told, because the columns with null values does not matter to the application. Celso Coimbra -----Mensagem original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]Em nome de Jonathan Leffler Enviada em: sábado, 2 de setembro de 2006 04:28 Para: ids@iiug.org Assunto: Re: doubts on create index [7408] On 8/30/06, SAGAR THANAWALA <sagarthanawala@yahoo.com> wrote: > The RDBMS rules [ CODD's 13 rules ] prohibit's such scenario. You could read > up the following books to get a better idea :- > > 1] RDBMS COncepts - S. Korth > > 2] Informix SQL - Tutorial . > > There are some very basic rules to RDBMS and applicable to all database like > Oracle, Informix, DB2, SQL-Server. Interesting. The question (since the context wasn't quoted) was: > I'd like to know if is possible to create a index in Informix that contains > just value which is not null, Can you explain where you find out about Codd's 13 rules - I know of the 12 rules, and I am hard-pressed to work out which one addresses this issue, but maybe the 13th is one that does so. A follow-up suggested that the question was asked by a consultant. It would be interesting to know which DBMS the consultant used that had the feature. The obvious way to ensure no nulls are indexed is to ensure that there are no nulls in the data columns that are indexed. However, that's quibbling. The real reason for wanting to do that is because there are likely a lot of nulls in the indexed column (or columns) and by not including those rows in the index, you can reduce the size of the index and not harm queries. Queries that use the index with a constraint that implies a real (not null) value for the column will be quicker because the index is smaller - and might even be 'unique if not null'. Queries that insist on 'column IS NULL' will probably be better off doing a sequential scan anyway. So, overall, I don't consider the question as preposterous as some of the responses seem to think it is. I know we have a feature request - not near the top of the priority list - for a 'unique if not null' index type. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/ ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Just put the possibly NULL column at or near the beginning of the key column list and any search will eliminate that/those branch(es) of the index tree quickly. Art S. Kagel ----- Original Message ----- From: Celso Cabral Coimbra <ids@iiug.org> At: 9/14 14:56:36 Sagar, sorry for the late answer, The RDBMS that the consultant still uses is the Oracle and the propose of create an index without null is to reduce the space and the number of reading like you told, because the columns with null values does not matter to the application. Celso Coimbra -----Mensagem original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]Em nome de Jonathan Leffler Enviada em: sábado, 2 de setembro de 2006 04:28 Para: ids@iiug.org Assunto: Re: doubts on create index [7408] On 8/30/06, SAGAR THANAWALA <sagarthanawala@yahoo.com> wrote: > The RDBMS rules [ CODD's 13 rules ] prohibit's such scenario. You could read > up the following books to get a better idea :- > > 1] RDBMS COncepts - S. Korth > > 2] Informix SQL - Tutorial . > > There are some very basic rules to RDBMS and applicable to all database like > Oracle, Informix, DB2, SQL-Server. Interesting. The question (since the context wasn't quoted) was: > I'd like to know if is possible to create a index in Informix that contains > just value which is not null, Can you explain where you find out about Codd's 13 rules - I know of the 12 rules, and I am hard-pressed to work out which one addresses this issue, but maybe the 13th is one that does so. A follow-up suggested that the question was asked by a consultant. It would be interesting to know which DBMS the consultant used that had the feature. The obvious way to ensure no nulls are indexed is to ensure that there are no nulls in the data columns that are indexed. However, that's quibbling. The real reason for wanting to do that is because there are likely a lot of nulls in the indexed column (or columns) and by not including those rows in the index, you can reduce the size of the index and not harm queries. Queries that use the index with a constraint that implies a real (not null) value for the column will be quicker because the index is smaller - and might even be 'unique if not null'. Queries that insist on 'column IS NULL' will probably be better off doing a sequential scan anyway. So, overall, I don't consider the question as preposterous as some of the responses seem to think it is. I know we have a feature request - not near the top of the priority list - for a 'unique if not null' index type. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/ ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.