Informix

Hello, have you any idea how to correctly join 2 tables using unique composite index which contains nulls in some of columns, but combination of all is unique?
Ex.
table a (i integer not null, j smallint not null, k char(5), l decimal(5), val integer, checksum integer not null)
table b(i integer not null, j smallint not null, k char(5), l decimal(5), checksum not null)
there is unique index on i,j,k,l and k and l contains nulls in some rows, one or other o both

join 
select a.* 
from a, b
where
a.i = b.i and
a.j = b.j and
a.k = b.k and
a.l = b.l
and a.checksum !=b.checksum

do not produces correct values for rows with null values on k and l.
And join condition for table in select I want to generate from info in sysindexes 

Thanks a lot


------------------------------
Milan Rafaj
------------------------------

#Informix

You have to explicitly call out the nulls:

where (a=b or (a is null and b is null))
and ......

j.


Original Message------

Hello, have you any idea how to correctly join 2 tables using unique composite index which contains nulls in some of columns, but combination of all is unique?
Ex.
table a (i integer not null, j smallint not null, k char(5), l decimal(5), val integer, checksum integer not null)
table b(i integer not null, j smallint not null, k char(5), l decimal(5), checksum not null)
there is unique index on i,j,k,l and k and l contains nulls in some rows, one or other o both

join 
select a.* 
from a, b
where
a.i = b.i and
a.j = b.j and
a.k = b.k and
a.l = b.l
and a.checksum !=b.checksum

do not produces correct values for rows with null values on k and l.
And join condition for table in select I want to generate from info in sysindexes 

Thanks a lot


------------------------------
Milan Rafaj
------------------------------

#Informix

Correct. And if there are no restrictions that make this a light nested loop (or in other words if you're joining large sets of both tables), then you're probably very lucky because a few years ago Informix became one of the first, if not the first, or even not the only RDBMS that will not refuse an HASH JOIN for such case (due to the addition of "(a is null and b is null)".
And trust me, it can make such a huge difference :)

To cut the story short, someone on a small country caught a query running for a week (!). When we dig up into it, we solve it by query re-writing using a view (1-2H). It was not me who came up with the solution. But then I proceeded with the analysis... one of the steps was to reproduce it on Oracle... Which had an interesting feature: Estimation for the execution time of the query, updated while the query was running.... we stop it after 1H or so, when the estimation reached more than 1 month... :). I'm not saying it would take 1 month... it was Oracle optimizer who thought so....

After a PMR I got a pre-release version of 11.70.FC5 (may be wrong about the fixpack, but it was around this one)... Without changes the query run a little bit slower (15m or so) then the option with the view... It was using an HASH JOIN. Investigation showed that none of the Databases I investigated would be able to do an HASH JOIN with those conditions... Not even some that don't have indexes, and for which a NESTED LOOP is generally not a good thing... :)

And to be honest, the improvement was not created for us... we were very luck that a big Australian partner (shouldn't be too hard to put a name here..) was getting those queries in Cognos and it was killing them... :)

Just a bit of history... Those conditions brought back memories...

Regards and keep safe. #stayhome

​

------------------------------
FERNANDO NUNES
------------------------------

Original Message:
Sent: Thu March 19, 2020 03:02 PM
From: Jack Parker
Subject: joins using unique index with some null values

You have to explicitly call out the nulls:

where (a=b or (a is null and b is null))
and ......

j.


Original Message------

Hello, have you any idea how to correctly join 2 tables using unique composite index which contains nulls in some of columns, but combination of all is unique?
Ex.
table a (i integer not null, j smallint not null, k char(5), l decimal(5), val integer, checksum integer not null)
table b(i integer not null, j smallint not null, k char(5), l decimal(5), checksum not null)
there is unique index on i,j,k,l and k and l contains nulls in some rows, one or other o both

join 
select a.* 
from a, b
where
a.i = b.i and
a.j = b.j and
a.k = b.k and
a.l = b.l
and a.checksum !=b.checksum

do not produces correct values for rows with null values on k and l.
And join condition for table in select I want to generate from info in sysindexes 

Thanks a lot


------------------------------
Milan Rafaj
------------------------------

#Informix

Hi!

Sometime we use NVL for this (because it´s easier to read):
NVL(a, 0) = NVL(b, 0)

(it´s important to use a non available-value for the second NVL-parameter).

-stefan

------------------------------
Kind Regards
Stefan
------------------------------

Original Message:
Sent: Thu March 19, 2020 03:02 PM
From: Jack Parker
Subject: joins using unique index with some null values

You have to explicitly call out the nulls:

where (a=b or (a is null and b is null))
and ......

j.


Original Message------

Hello, have you any idea how to correctly join 2 tables using unique composite index which contains nulls in some of columns, but combination of all is unique?
Ex.
table a (i integer not null, j smallint not null, k char(5), l decimal(5), val integer, checksum integer not null)
table b(i integer not null, j smallint not null, k char(5), l decimal(5), checksum not null)
there is unique index on i,j,k,l and k and l contains nulls in some rows, one or other o both

join 
select a.* 
from a, b
where
a.i = b.i and
a.j = b.j and
a.k = b.k and
a.l = b.l
and a.checksum !=b.checksum

do not produces correct values for rows with null values on k and l.
And join condition for table in select I want to generate from info in sysindexes 

Thanks a lot


------------------------------
Milan Rafaj
------------------------------

#Informix

Original Message------

Hi!

Sometime we use NVL for this (because it´s easier to read):
NVL(a, 0) = NVL(b, 0)

(it´s important to use a non available-value for the second NVL-parameter).

-stefan

------------------------------
Kind Regards
Stefan
------------------------------

#Informix

Original Message------

Stefan:

Using NVL() would prevent using the indexes though!

Arr

#Informix

Original Message------

Functional index 

Paul Watson

Oninit LLC

+1-913-387-7529

www.oninit.com

Oninit®️ is a registered trademark of Oninit LLC

#Informix

Hi!

Many to many will happen in both solutions:
NVL(a, 0) = NVL(b, 0) OR where (a=b or (a is null and b is null))

-
Stefan


------------------------------
Kind Regards
Stefan
------------------------------

Original Message:
Sent: Fri March 20, 2020 08:27 AM
From: Art Kagel
Subject: joins using unique index with some null values

Agreed Paul, but at this point we're assuming that this is an existing schema.

Besides, NVL() doesn't solve the real problem: If both tables include NULLs in parts of the join key in multiple rows then what rows are the correct targets of joining any given row that has a NULL? It becomes a many-to-many join with no intervening "join" table when it should be a straight one-to-one or one-to-many join.

That's why I asked the OP to give us some example of what the data looks like and what he expects the results to be.

Art

Art S. Kagel, President and Principal Consultant

ASK Database Management

www.askdbmgt.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 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.

Original Message------

Functional index 

Paul Watson

Oninit LLC

+1-913-387-7529

www.oninit.com

Oninit®️ is a registered trademark of Oninit LLC

#Informix

Hi 
Yeah will happen in both solutions but the result is not the same when a = 0 or b = 0 
First you have to determind if 0 equals NULL or not. Usually not.


------------------------------
Gábor Fekete
Software Engineer
Capital Systems
Budapest
------------------------------

Original Message:
Sent: Fri March 20, 2020 08:56 AM
From: Stefan Wilfling
Subject: joins using unique index with some null values

Hi!

Many to many will happen in both solutions:
NVL(a, 0) = NVL(b, 0) OR where (a=b or (a is null and b is null))

-
Stefan


------------------------------
Kind Regards
Stefan

Original Message:
Sent: Fri March 20, 2020 08:27 AM
From: Art Kagel
Subject: joins using unique index with some null values

Agreed Paul, but at this point we're assuming that this is an existing schema.

Besides, NVL() doesn't solve the real problem: If both tables include NULLs in parts of the join key in multiple rows then what rows are the correct targets of joining any given row that has a NULL? It becomes a many-to-many join with no intervening "join" table when it should be a straight one-to-one or one-to-many join.

That's why I asked the OP to give us some example of what the data looks like and what he expects the results to be.

Art

Art S. Kagel, President and Principal Consultant

ASK Database Management

www.askdbmgt.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 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.

Original Message------

Functional index 

Paul Watson

Oninit LLC

+1-913-387-7529

www.oninit.com

Oninit®️ is a registered trademark of Oninit LLC

#Informix

Hello, using nvl have some issues a) you need to choose value, which cannot be in columns b) you need to know data type of column, which can be good in apps but not when I want to generate joins for many tables with different keys.
a = b or ( a is null and b is null) is ok for me and gives correct results (ie key 1,2,null in table a matches key 1,2,null in table b) but is terrible slow on tables with many rows even b has an index on join keys, because for a=b index access is fast, but for (a is null and b is null, sequential scan of index for nulls have to be done (mainly, i b i not the first part of an index)

------------------------------
Milan Rafaj
------------------------------

Original Message:
Sent: Fri March 20, 2020 08:56 AM
From: Stefan Wilfling
Subject: joins using unique index with some null values

Hi!

Many to many will happen in both solutions:
NVL(a, 0) = NVL(b, 0) OR where (a=b or (a is null and b is null))

-
Stefan


------------------------------
Kind Regards
Stefan

Original Message:
Sent: Fri March 20, 2020 08:27 AM
From: Art Kagel
Subject: joins using unique index with some null values

Agreed Paul, but at this point we're assuming that this is an existing schema.

Besides, NVL() doesn't solve the real problem: If both tables include NULLs in parts of the join key in multiple rows then what rows are the correct targets of joining any given row that has a NULL? It becomes a many-to-many join with no intervening "join" table when it should be a straight one-to-one or one-to-many join.

That's why I asked the OP to give us some example of what the data looks like and what he expects the results to be.

Art

Art S. Kagel, President and Principal Consultant

ASK Database Management

www.askdbmgt.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 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.

Original Message------

Functional index 

Paul Watson

Oninit LLC

+1-913-387-7529

www.oninit.com

Oninit®️ is a registered trademark of Oninit LLC

#Informix

Original Message------

Hello, using nvl have some issues a) you need to choose value, which cannot be in columns b) you need to know data type of column, which can be good in apps but not when I want to generate joins for many tables with different keys.
a = b or ( a is null and b is null) is ok for me and gives correct results (ie key 1,2,null in table a matches key 1,2,null in table b) but is terrible slow on tables with many rows even b has an index on join keys, because for a=b index access is fast, but for (a is null and b is null, sequential scan of index for nulls have to be done (mainly, i b i not the first part of an index)

------------------------------
Milan Rafaj
------------------------------

#Informix

Original Message------

Hello, have you any idea how to correctly join 2 tables using unique composite index which contains nulls in some of columns, but combination of all is unique?
Ex.
table a (i integer not null, j smallint not null, k char(5), l decimal(5), val integer, checksum integer not null)
table b(i integer not null, j smallint not null, k char(5), l decimal(5), checksum not null)
there is unique index on i,j,k,l and k and l contains nulls in some rows, one or other o both

join 
select a.* 
from a, b
where
a.i = b.i and
a.j = b.j and
a.k = b.k and
a.l = b.l
and a.checksum !=b.checksum

do not produces correct values for rows with null values on k and l.
And join condition for table in select I want to generate from info in sysindexes 

Thanks a lot


------------------------------
Milan Rafaj
------------------------------

#Informix