Este artigo est� escrito em Portugu�s e Ingl�s
English version:
Very short article for very simple feature: Since v11.7 (Panther) you can now define the extent size for indexes as we use to do with tables (data). Many people may find this a great feature, or a must have (specially if you worked with other RDBMS before), but for me it's relatively useless (unless it makes easier to port SQL scripts).
Why do I think this? Because Informix has been clever enough to find a good size for the index extents. It simply doesn't need us to tell it. It knows the size that the key will need for each index entry, and it knows the table's extent size. So it can calculate how many rows will fit in the data area (since it knows the extent size) and then it can calculate the space that number of rows will consume in the index.
I don't recall having a discrepancy between the index number of extents and the tables data extents. So I'm assuming it worked ok over the years.
The syntax is similar to the CREATE TABLE. But let's start by seeing how it works without specifying the index extent size:
panther_clone@pacman.onlinedomus.net:informix-> cat test_100k.sql
DROP TABLE test_100k;
CREATE TABLE test_100k
(
col1 INTEGER,
col2 CHAR(4)
) EXTENT SIZE 100 LOCK MODE ROW;
CREATE UNIQUE INDEX ix_test_100k ON test_100k(col1);
panther_clone@pacman.onlinedomus.net:informix-> dbaccess stores test_100k.sql
Database selected.
Table dropped.
Table created.
Index created.
Database closed.
panther_clone@pacman.onlinedomus.net:informix-> oncheck -pt stores:test_100k | awk '/Extents/,/^$/ {print $0}'
Extents
Logical Page Physical Page Size Physical Pages
0 4:4627 50 50
Extents
Logical Page Physical Page Size Physical Pages
0 4:4677 81 81
So, the data extent size of 100K generates an index extent size of 162K.
If we double the data extent size to 200K:
panther_clone@pacman.onlinedomus.net:informix-> cat test_200k.sql
DROP TABLE test_200k;
CREATE TABLE test_200k
(
col1 INTEGER,
col2 CHAR(4)
) EXTENT SIZE 200 LOCK MODE ROW;
CREATE UNIQUE INDEX ix_test_200k ON test_200k(col1);
panther_clone@pacman.onlinedomus.net:informix-> dbaccess stores test_200k.sql
Database selected.
Table dropped.
Table created.
Index created.
Database closed.
panther_clone@pacman.onlinedomus.net:informix-> oncheck -pt stores:test_200k | awk '/Extents/,/^$/ {print $0}'
Extents
Logical Page Physical Page Size Physical Pages
0 4:4758 100 100
Extents
Logical Page Physical Page Size Physical Pages
0 4:4858 162 162
So, doubling the data size doubles the index size.
What we can now do it establish the index extent size explicitly:
panther_clone@pacman.onlinedomus.net:informix-> cat test_100k_new.sql
DROP TABLE test_100k;
CREATE TABLE test_100k
(
col1 INTEGER,
col2 CHAR(4)
) EXTENT SIZE 100 LOCK MODE ROW;
CREATE UNIQUE INDEX ix_test_100k ON test_100k(col1) EXTENT SIZE 40 NEXT SIZE 162;
panther_clone@pacman.onlinedomus.net:informix-> dbaccess stores test_100k_new.sql
Database selected.
Table dropped.
Table created.
Index created.
Database closed.
panther_clone@pacman.onlinedomus.net:informix-> oncheck -pt stores:test_100k | awk '/Extents/,/^$/ {print $0}'
Extents
Logical Page Physical Page Size Physical Pages
0 4:4627 50 50
Extents
Logical Page Physical Page Size Physical Pages
0 4:4677 20 20
Truly simple and obvious. Hopefuly this will bring peace to some souls who think that this was needed. And let's hope people won't start making wrong calculations and choose wrong extent sizes.
Vers�o Portuguesa:
Um artigo muito curto para uma funcionalidade muito simples: A partir da vers�o 11.7 (Panther) podemos definir o tamanho de cada extent para os �ndices tal como fazemos para as tabelas (dados). Muita gente pode considerar esta como uma grande funcionalidade, ou algo mesmo necess�rio (especialmente se j� trabalhou com outros RDBMS antes), mas eu considero-a relativamente in�til (a menos que facilite a transposi��o de scripts SQL).
Porque penso assim? Porque o Informix sempre foi esperto o suficiente para encontrar um bom valor para os extents dos �ndices. Simplesmente n�o necessita que lhe digamos. Ele sabe o tamanho da chave necess�rio a cada entrada do �ndice, e sabe o tamanho dod extents da tabela. Portanto pode calcular quantas linhas caber�o na �rea de dados (pois sabe o tamanho do extent) e assim consegue calcular quantas p�ginas � que o �ndice ir� necessitar para esse mesmo n�mero de registos.
N�o me recordo de ver discrep�ncia entre o espa�o no �ndice e o espa�o dos dados. S� posso assumir que tem corrido bem ao longo dos anos.
A nova sintaxe ser� semelhante � do CREATE TABLE. Vejamos como funciona sem especificar:
panther_clone@pacman.onlinedomus.net:informix-> cat test_100k.sql
DROP TABLE test_100k;
CREATE TABLE test_100k
(
col1 INTEGER,
col2 CHAR(4)
) EXTENT SIZE 100 LOCK MODE ROW;
CREATE UNIQUE INDEX ix_test_100k ON test_100k(col1);
panther_clone@pacman.onlinedomus.net:informix-> dbaccess stores test_100k.sql
Database selected.
Table dropped.
Table created.
Index created.
Database closed.
panther_clone@pacman.onlinedomus.net:informix-> oncheck -pt stores:test_100k | awk '/Extents/,/^$/ {print $0}'
Extents
Logical Page Physical Page Size Physical Pages
0 4:4627 50 50
Extents
Logical Page Physical Page Size Physical Pages
0 4:4677 81 81
Um tamanho de 100K para o extent dos dados gera um extent para ind�ces de 162K
Se duplicarmos o extent de dados para 200K:
panther_clone@pacman.onlinedomus.net:informix-> cat test_200k.sql
DROP TABLE test_200k;
CREATE TABLE test_200k
(
col1 INTEGER,
col2 CHAR(4)
) EXTENT SIZE 200 LOCK MODE ROW;
CREATE UNIQUE INDEX ix_test_200k ON test_200k(col1);
panther_clone@pacman.onlinedomus.net:informix-> dbaccess stores test_200k.sql
Database selected.
Table dropped.
Table created.
Index created.
Database closed.
panther_clone@pacman.onlinedomus.net:informix-> oncheck -pt stores:test_200k | awk '/Extents/,/^$/ {print $0}'
Extents
Logical Page Physical Page Size Physical Pages
0 4:4758 100 100
Extents
Logical Page Physical Page Size Physical Pages
0 4:4858 162 162
Portanto, duplicando o tamanho para dados, automaticamene o motor aumenta o tamanho do �ndice na mesma propor��o
Podemos agora tentar estabelecer o tamanho do extent do �ndice:
panther_clone@pacman.onlinedomus.net:informix-> cat test_100k_new.sql
DROP TABLE test_100k;
CREATE TABLE test_100k
(
col1 INTEGER,
col2 CHAR(4)
) EXTENT SIZE 100 LOCK MODE ROW;
CREATE UNIQUE INDEX ix_test_100k ON test_100k(col1) EXTENT SIZE 40 NEXT SIZE 162;
panther_clone@pacman.onlinedomus.net:informix-> dbaccess stores test_100k_new.sql
Database selected.
Table dropped.
Table created.
Index created.
Database closed.
panther_clone@pacman.onlinedomus.net:informix-> oncheck -pt stores:test_100k | awk '/Extents/,/^$/ {print $0}'
Extents
Logical Page Physical Page Size Physical Pages
0 4:4627 50 50
Extents
Logical Page Physical Page Size Physical Pages
0 4:4677 20 20
Completamente simples e �bvio. Esperemos que isto ajude a pacificar algumas almas que acham isto necess�rio. E tamb�m que as pessoas n�o comecem agora a calcular mal o espa�o a alocar.
