IOT – Index Organized Table no Oracle
Conceitos e aplicabilidade
Pedro Valiati
A grande especificidade exigida pelas regras de negócios, aliada à necessidade de atualizações dos sistemas de TI tem levado DBA’s e DA’s a buscarem novas alternativas em modelagem de dados e ganhos de performance. Com isso, os conceitos de IOT (Index Organized Table) vêm ganhando espaço. Sua utilização vem desde a versão 8i do Oracle e foi aperfeiçoada na versão 9i (vale ressaltar que este conceito também é utilizado em outros SGBDs como o SQL Server).
Conceito
Ao contrário do que normalmente se imagina, a aplicabilidade das IOT’s está bem próxima das necessidades dos sistemas de TI. Mas o que seria essa ‘tal’ de IOT?
Podemos defini-la como sendo uma tabela cuja organização de armazenamento seja uma variante da organização de uma chave-primária do tipo B-Tree (Nota 1). Inversamente a uma tabela regular (HOT - Heap Organized Table) a qual armazena dados de uma forma não ordenada, a IOT ordena os dados numa estrutura de índices B-Tree, classificando-os da mesma forma que uma chave primária.
Mas a grande ‘sacada’ da IOT é a possibilidade da variação do ‘endereço da linha’, também conhecido como ROWID, para conseguir efetuar a organização dos dados no momento de seu armazenamento.
Nota 1. Conceito de índice B-Tree
A palavra B-Tree (Binary Tree) significa 'árvore binária', que é uma das variações de tipo de índice que permite uma busca rápida em bases de dados. Praticamente todos os bancos de dados SQL baseiam-se nesse padrão de busca. Em uma consulta utilizando índices B-Trees, o Oracle percorrerá os blocos de dados da tabela do índice, sendo que cada bloco alcançado separará fisicamente dados que não estejam dentro do que foi solicitado na consulta. Isto ocorrerá até os dados solicitados serem encontrados.
Tenhamos como exemplo uma tabela cuja chave primária seja o campo ID do tipo numeral (Number), e cujos valores sejam ordenados de 1 a 1000. Além disso, devido a uma regra de negócios hipotética, o campo da coluna ID de valores entre 100 e 200 devem ser alterados para os valores de 1100 e 1200.
No caso de uma HOT, a mudança ocorrerá em nível de valores no campo, não haverá mudança do endereço da linha (ROWID). No caso de uma IOT a alteração irá além, será física, pois se dará em nível do armazenamento dos dados nos blocos de dados, podendo, inclusive, haver mudança do bloco de dados para a linha da tabela alterada. É a ROWID se movimentando de forma a organizar a chave primária numa seqüência lógica, possibilitando uma melhor performance no resgate de dados da tabela utilizando os campos de chave-primária, neste caso o campo ID.
Em linhas gerais, podemos dizer que uma IOT não possuirá uma tabela de índices, como em uma HOT, ela já se comporta movimentando as linhas para que a organização da mesma não necessite de uma tabela a parte.
Vejamos na Listagem 1 um exemplo simples da criação de uma IOT onde temos três campos, sendo que dois deles formam uma chave primária composta. No exemplo é possível notar a cláusula ‘ORGANIZATION INDEX’, que define a tabela como IOT.
Listagem 1. Conhecendo os DBAs (e sendo um deles), aí vai o primeiro e simples exemplo de uma IOT.
CREATE TABLE MINHA_1A_IOT (
IOT_ID INT,
IOT_DATE DATE,
IOT_NAME VARCHAR2(8),
CONSTRAINT PK_MINHA_1A_IOT –- CLÁUSULA ‘CONSTRAINT’ OBRIGATÓRIA
PRIMARY KEY (IOT_ID, IOT_DATE)) -– CLÁUSULA ‘PRIMARY KEY’ OBRIGATÓRIA
ORGANIZATION INDEX; -- CLÁUSULA ‘ORGANIZATION INDEX’ OBRIGATÓRIA
As IOTs fornecem o acesso rápido aos dados da tabela para consultas baseadas em chaves primárias que envolvam buscas exatas e escalares “In a Range”.
A alteração nos dados da tabela (tais como a add, update, insert, ou delete de linhas) resulta apenas na atualização de uma estrutura de dados, pois não há nenhuma área de armazenamento separada da tabela (o que ocorre com a estrutura de índices separada da tabela de dados em uma HOT). Além disso, as exigências de armazenamento são reduzidas porque as colunas chaves não são duplicadas na tabela e no índice. As colunas restantes, non key columns, são armazenadas na mesma estrutura do índice.
As IOTs são particularmente úteis na utilização de aplicações que consultam dados baseados em uma chave primária. Imagens e áudio também podem eficazmente serem modelados usando IOTs.
Aplicabilidade da IOT
Cargas de dados (Bulk Loads)
O Oracle inicia um processo de carga de dados fazendo uma ordenação nas chaves do índice para as linhas novas. A velocidade do sort depende do tamanho definido da chave, porque o Oracle carrega e faz o intercâmbio das chaves em memória.
Dessa forma, um sort numa HOT, por exemplo, ocorrerá somente nas colunas da chave primária (que se encontram numa estrutura separada), tornando mais rápido que um sort de IOT. No caso do sort de IOT, a chave primária não se encontra em uma estrutura de dados separada, movimentando, desta forma, a linha por completo. Estima-se que um sort de IOT seja mais lento em torno de 50%.
Aqui vai uma dica para aumentar o desempenho dos sorts nas IOT’s:
· Superestimar o parâmetro SORT_AREA_SIZE no Oracle. Como a velocidade do sort depende do tamanho definido da chave, e numa IOT chave primária não se encontra em uma estrutura de dados separada (movimentando, assim, a linha toda), ela necessita de uma maior área de sort;
· ...
