OLAP Window Functions

This article is written in English and Portuguese
Este artigo está escrito em Inglês e Português


English version:
In this article I'll mention  some new functionality in 12.1, but I'll use that to answer a recent question on IIUG mailing list. The question initially posted was about when did Informix update the index involved in a transaction. But after a few interactions with the original poster, it was clear what the problem was and it's a bit tricky:
The user was UNLOADing a table that contained a primary key and there were duplicate records (regarding the primary key) in the result set. This naturally looks like a bug, but there is a technical explanation for that. Let's describe a simple reproduction. For that I wrote three simple SQL scritps that will be run through dbaccess:

#-------- main.sql
DROP TABLE IF EXISTS test;
CREATE TABLE test
(
        col1 INTEGER,
        col2 INTEGER,
        col3 CHAR(2000),
        PRIMARY KEY (col1,col2)
)
EXTENT SIZE 16 NEXT SIZE 16
LOCK MODE ROW;

INSERT INTO test VALUES ( 1, 1, 'a');
INSERT INTO test VALUES ( 1, 2, 'a');
INSERT INTO test VALUES ( 1, 3, 'a');
INSERT INTO test VALUES ( 1, 4, 'a');
INSERT INTO test VALUES ( 1, 5, 'a');

BEGIN WORK;
UPDATE TEST SET COL3 = 'a1' WHERE col1 = 1 AND col2 = 3;
!/bin/sleep 30
COMMIT WORK;


#-------- unload.sql
SET LOCK MODE TO WAIT;
UNLOAD TO test.unl
SELECT *, ROWID FROM test;

#-------- move_rows.sql
BEGIN WORK;
DELETE FROM test WHERE col1 = 1 AND col2 = 1;
INSERT INTO test VALUES (1, 1, 'b');
COMMIT WORK;

The first script, called "main.sql" will create a table, with row level locking and a particularity that helps the reproduction: Each page will contain only one row. I did this on Linux where the default page size is 2KB. If you run it on AIX (4KB pages) please change the col3 definition to CHAR(4000). Then the script will populate the table with 5 rows. Note that I'm using a composite primary key, but a simple primary key or any unique index should be enough.
Then I start a transaction and do an update on row "3" and just after that I run an external command to sleep for 30 seconds before executing the COMMIT. This gives me plenty of time to run the other scripts in parallel.

Next script is called "unload.sql" and it simply runs an UNLOAD of that table after setting the LOCK MODE TO WAIT. As you may expect this script will be stuck on the lock created by the first one.

The third script has the "magic". It DELETEs a row with a certain primary key and INSERTs it again, but with a different col3 value.
I will use the following SHELL script to run the reproduction scripts:

#!/bin/bash
dbaccess stores main.sql 1>main.out 2>&1 &
sleep 5
dbaccess stores unload.sql 1>unload.out 2>&1 &
sleep 5
dbaccess stores move_rows.sql 1>move_rows.out 2>&1 &
wait

So... It runs the "main.sql" in background. Sleeps for 5 seconds (enough so that the "main.sql" is able to enter the sleep after locking record 3). Then is launches the "unload.sql" script which will be waiting on the first one, also in the background. And then it runs the script that DELETEs and INSERTs a row with a certain primary key.

This is the resulting unload file:
1|1|a|257|
1|2|a|513|
1|3|a1|769|
1|4|a|1025|
1|5|a|1281|
1|1|b|1537|

As you can see we have 6 rows in the file. And only 5 on the table. We have a problem.
The row with primary key (1,1) is duplicated. How can this happen? In order to understand it, we need to understand how the queries are processed and that Informix is not a multi-version database (just like SQL Server, DB2 in most cases, Sybase and some others).
The sequence of events is:

1. We have a table with 5 rows. And we lock row number "3"
2. In parallel we start a full scan of the same table. We read rows 1 and 2 and we get stuck on row number 3
3. While we're still holding the lock we do another parallel operation where we remove row 1, and insert it again (with a different value for col3 for better understanding) on a different physical location
4. We unlock row 3
5. The full scan continues and reads row 1 again, on another physical location

If you're scared with this, don't be. This requires several factors to happen:

1. You must access the table with a full scan
2. You must DELETE a row that was already read, and INSERT the same row in a physical location yet to be read
3. You need to DELETE and INSERT rows with the same primary key or unique index values

And there are ways to avoid this:

1. Force an index scan
2. Use some query condition that will skip rows that are inserted after your query starts (usable if the table has some sort of timestamp condition)
3. Run the query in REPEATABLE READ isolation level
4. Use a new functionality in 12.1

Most of the ways to avoid this effect may not be usable. The index scan can cause a serious performance impact. The timestamp condition depends on the table schema. The REPEATABLE READ will cause a lot of locks if the table is big, and would stop all change activity on the table (for that we could use a LOCK TABLE test IN SHARE MODE)
So, how can 12.1 help us avoid this? Well it introduces the so called OLAP window functions, and one example is ROW_NUMBER(). You might recall I've written an article about ROWNUM and there I stated that to achieve somethings we would need to change the engine. And that the proposed UDR on that article would have to be customized to implement PARTITION BY clauses. Well, with 12.1 we can use the standard syntax and we could write the UNLOAD query like:

UNLOAD TO test.unl
SELECT * FROM
(
SELECT
t.*, ROWID, ROW_NUMBER() OVER (PARTITION BY col1,col2) AS rown
FROM test t
)
WHERE rown = 1;

This looks a bit more complex, but it's not hard to understand.
I'm introducing the ROW_NUMBER() function, that will add a sequential number to each row, but with one particularity: The number will restart (1) for each pair of col1, col2 which I specify in the PARTITION BY clause. The result set is this one:
1|1|a|257|1|
1|2|a|513|1|
1|3|a1|769|1|
1|4|a|1025|1|
1|5|a|1281|1|

As you can see the new insert row is not there. Why? Because I have a constraint that the ROW_NUMBER() must be equal to one. If we remove it we get:
1|1|a|257|1|
1|1|b|1537|2|
1|2|a|513|1|
1|3|a1|769|1|
1|4|a|1025|1|
1|5|a|1281|1|

Note that I used ROWID and the ROW_NUMBER() in the projection clause, but that was only for academic purposes. ROWID shows the physical location and the result of ROW_NUMBER() shows how it works and how I used it.

There are many other OLAP window aggregate functions. You can discover them here and check on the PARTITION BY, ORDER BY and other clauses that can be used together.

It's important to note that the functionality of "OLAP window aggregates" introduced new aggregate functions, but it also extended the use of some "old" functions like SUM(), COUNT(), AVG() etc. which now allow the use of the OVER clause.
From an informix user perspective this is a great step forward, because some of these functions allows us to write SQL statements to generate reports that otherwise would require some programming. This means not only that the usability is improved, but also that Informix can now handle queries sent by BI tools (like Cognos and others). This is very important from a supportability point of view. These functions are present in other databases for some years and this was really something that was missing. This is even more important because queries with these functions can still benefit from the presence of the Informix warehouse accelerator. Meaning the BI user can take advantage of better integration with the tools normally used in BI environments and also from the speed provided by IWA.



Versão Portuguesa:
 Neste artigo vou referir uma nova funcionalidade da versão 12.10, mas irei usar isso para responder a uma questão que apareceu recentemente na lista de correio do IIUG. A questão original referia-se a quando é que o Informix atualiza os índices durante uma transação. Mas após algumas trocas de impressões parecia claro qual era o problema e é um pouco complexo:
O utilizador estava a fazer um UNLOAD de uma tabela que continha um chave primária e apareciam registos duplicados (relativamente à chave primária) no resultado. Naturalmente isto parecia um bug, mas há uma explicação técnica para o fato. Vamos descrever um procedimento simples que permite reproduzir o problema. Para tal temos três simples scripts SQL que serão executados através do dbaccess:

#-------- main.sql
DROP TABLE IF EXISTS test;
CREATE TABLE test
(
        col1 INTEGER,
        col2 INTEGER,
        col3 CHAR(2000),
        PRIMARY KEY (col1,col2)
)
EXTENT SIZE 16 NEXT SIZE 16
LOCK MODE ROW;

INSERT INTO test VALUES ( 1, 1, 'a');
INSERT INTO test VALUES ( 1, 2, 'a');
INSERT INTO test VALUES ( 1, 3, 'a');
INSERT INTO test VALUES ( 1, 4, 'a');
INSERT INTO test VALUES ( 1, 5, 'a');

BEGIN WORK;
UPDATE TEST SET COL3 = 'a1' WHERE col1 = 1 AND col2 = 3;
!/bin/sleep 30
COMMIT WORK;


#-------- unload.sql
SET LOCK MODE TO WAIT;
UNLOAD TO test.unl
SELECT *, ROWID FROM test;

#-------- move_rows.sql
BEGIN WORK;
DELETE FROM test WHERE col1 = 1 AND col2 = 1;
INSERT INTO test VALUES (1, 1, 'b');
COMMIT WORK;

O primeiro script chama-se "main.sql" e cria uma tabela com lock ao registo e esta tabela tem uma particularidade que ajuda à reprodução: Cada página de dados contém apenas um registo. Como executei isto em Linux, onde o tamanho pré-definido das páginas é de 2KB, usei um tamanho de CHAR(2000). Se executar em AIX por exemplo, onde a página será de 4KB, deverá usar CHAR(4000). Depois de criar a tabela, o script carrega 5 registos. Note que estou a usar uma chave primária composta, mas usar uma chave primária simples ou mesmo um índice único seria o mesmo.
Depois inicio uma transação e altero a linha "3", executando depois um comando externo que força uma pausa de 30 segundos antes de fazer o COMMIT. Isto dá-nos tempo mais que suficiente para executar os outros scripts em paralelo.

O próximo script chama-se "unload.sql" e executa um simples UNLOAD da tabela, depois de alterar o LOCK MODE para WAIT. Como seria de esperar, nestas condições o script irá ficar em espera pelo primeiro que causa um lock na linha "3".

O último script tem a "magia". Efetua um DELETE a uma linha com uma determinada chave primária e depois volta a fazer o INSERT de uma linha com essa mesma chave, mas um valor diferente na coluna "col3" (apenas por uma questão de clareza)
Usarei o script SHELL seguinte para executar os scripts SQL que reproduzem o problema:

#!/bin/bash
dbaccess stores main.sql 1>main.out 2>&1 &
sleep 5
dbaccess stores unload.sql 1>unload.out 2>&1 &
sleep 5
dbaccess stores move_rows.sql 1>move_rows.out 2>&1 &
wait

Portanto... Lança o "main.sql" em background, depois adormece durante 5 segundos (para garantir que o "main.sql" chega à parte em que cria o lock no registo "3"). De seguida lança o "unload.sql" que irá ficar bloqueado ao chegar ao registo "3", sendo que este também é lançado em backgroud. Por fim executa o script que efectua o DELETE e o INSERT da linha com determinada chave primária (1,1) e espera pelo retorno dos três processos.
O resultado do ficheiro de unload é:
1|1|a|257|
1|2|a|513|
1|3|a1|769|
1|4|a|1025|
1|5|a|1281|
1|1|b|1537|

Como pode verificar temos 6 linhas no ficheiro. E apenas 5 na tabela. Temos um problema.
A linha com a chave primária (1,1) aparece duplicada. Como pode tal acontecer? Para entedermos isto temos de entender como é que as queries são processadas e também que o Informix não é o que se designa por base de dados multi-version (bem como SQL Server, DB2 na maioria dos casos, Sybase e várias outras).
A sequência de eventos é:

1. Temos uma tabela com 5 linhas e bloqueamos a linha "3"
2. Em paralelo iniciamos um varrimento completo da tabela. Lemos as linhas "1" e "2" e ficamos bloqueados na linha "3"
3. Enquanto ainda temos o lock, fazemos outra operação em paralelo onde se remove a linha 1 (já lida pelo varrimento) e inserimos novamente a mesma linha (com um valor diferente na coluna col3 para maior clareza) numa zona ainda não lida
4. Desbloqueamos a linha "3"
5. O varrimento continua e lê a linha 1 novamente, desta feita numa localização física diferente

Se isto o assusta, sossegue. São necessários vários fatores para que tal aconteça:

1. O acesso à tabela tem de ser feito por varrimento (full scan)
2. Tem de existir um DELETE a uma linha já lida, e um INSERT da mesma linha (em termos de chave primária), numa localização física que ainda vá ser acedida pelo varrimento
3. Tem de fazer o DELETE e INSERT de linhas com o mesmo valor da chave primária (ou de valores de índices únicos)

E existem formas de evitar isto:

1. Forçar o acesso por índice
2. Usar alguma condição na query que faça descartar linhas inseridas após o início da query (pode ser feito se a tabela contiver algum tipo de "carimbo" com o momento de inserção
3. Executar a query no modo de isolamento REPEATABLE READ
4. Usar uma nova funcionalidade da versão 12.1

A maioria das formas de evitar o problema podem não ser práticas. O acesso por índice pode ter impactos sérios na velocidade de execução da query. A condição adicional depende da estrutura da tabela e forma de inserção dos registos. O REPEATABLE READ irá causar imensos locks se a tabela for grande, e impediria toda a atividade de alteração sobre a tabela durante a execução da query. Aliás nessa situação seria melhor bloquear a tabela em modo SHARE)
Assim, como pode a versão 12.10 ajudar-nos a evitar isto? Bom, esta versão introduz o que se chamam de OLAP window functions. Um exemplo será a função ROW_NUMBER(). Poderá lembrar-se que escrevi um artigo sobre o tema, onde mencionei que para alcançar algumas funcionalidades seria necessário alterar código interno do motor. E que a proposta função (UDR) desse artigo necessitaria de ser adaptada a cada caso, se desejássemos implementar as cláusulas PARTITION BY. Bom, com a versão 12.10 temos acesso à sintaxe standard e podemos reescrever o UNLOAD da seguinte forma:

UNLOAD TO test.unl
SELECT * FROM
(
SELECT
t.*, ROWID, ROW_NUMBER() OVER (PARTITION BY col1,col2) AS rown
FROM test t
)
WHERE rown = 1;

Isto parece um pouco mais complexo, mas não é difícil de entender.
Apenas introduzi a função ROW_NUMBER() que irá adicionar um número sequencial a cada linha, mas com uma particularidade: O número será re-inicializado (1) para cada par das colunas col1, col2 tal como especifico com a cláusula PARTITION BY. O resultado será assim:
1|1|a|257|1|
1|2|a|513|1|
1|3|a1|769|1|
1|4|a|1025|1|
1|5|a|1281|1|

Como pode ver a nova linha inserida não aparece. Porquê? Porque a nova query tem uma condição que elimina registos onde o resultado do ROW_NUMBER() não seja 1. Se removermos esta condição passaremos a ter no resultado isto:
1|1|a|257|1|
1|1|b|1537|2|
1|2|a|513|1|
1|3|a1|769|1|
1|4|a|1025|1|
1|5|a|1281|1|

Convém referir que o uso do ROWID e ROW_NUMBER na lista de colunas da lista de projeção é meramente académico. O ROWID mostra a localização física da linha e o ROW_NUMBER serve para mostrar como funciona e como o usei.

Existem muitas outras funções OLAP window aggregates. Pode descobri-las aqui e verificar as cláusulas PARTITION BY, ORDER BY e outras que podem ser usadas em conjunto.

É importante notar que esta nova funcionalidade introduziu novas funções, mas também estendeu a utilização de algumas funções "antigas" como o SUM, COUNT(), AVG() etc. que agora permitem usar a cláusula OVER
Da perspetiva de um utilizador Informix, isto é um grande passo em frente, porque algumas destas funções permitem-nos escrever queries SQL para gerar relatórios que de outra forma necessitariam de algum tipo de programação. Isto significa que não apenas se melhorou a utilização, mas também que o Informix pode agora lidar com queries enviadas por ferramentas de BI (como Cognos e outras). Isto é muito importante do ponto de vista do suporte a aplicações. Estas funções já existem noutras bases de dados há vários anos e esta era realmente uma falha que foi agora colmatada.
Há ainda a registar que queries com estas funções podem mesmo assim beneficiar da presença do Informix Warehouse Accelerator (IWA). Quer isto dizer que o utilizador de BI pode aproveitar a melhor integração com as ferramentas normalmente usadas nesses ambientes e ao mesmo tempo aproveitar também a velocidade proporcionada pelo IWA.