IV - Criação e Utilização de Índices Textuais
A estrutura geral do índice Oracle Text é um índice invertido, ou seja, uma lista de palavras existentes em um documento, com cada palavra tendo uma lista de documentos onde elas aparecem. É chamado de invertido porque é o inverso da forma usual de analisar um texto, que neste caso é uma lista de documentos onde cada documento contém uma lista de palavras.
É possível criar um índice texto como um índice extensível para o Oracle utilizar o SQL padrão, ou seja, o índice Oracle Text poderia operar como um índice do Oracle. Provendo assim, um nome pelo qual seria referenciado e manipulado através de expressões SQL padrão.
A opção em escolher um índice Oracle Text é influenciada por um rápido tempo de resposta para consultas com as funções CONTAINS, CATSEARCH, e MATCHES do Oracle Text, que consultam os tipos de índices CONTEXT, CTXCAT, e CTXRULE respectivamente.
Tanto os índices baseados em função quanto os índices Oracle Text permitem navegação, podendo utilizá-los intercalados. Assim, é possível usar, primeiramente, o índice Oracle Text para remover todos os documentos que potencialmente coincidem com o critério, e então, aplicam-se posteriormente, filtros tais como as funções existsNode() ou extract() sobre o restante dos documentos.
Como um exemplo simples, iremos criar e popular algumas tabelas que relacionam uma publicação com autor e editora.
--CRIAÇÃO DAS TABELAS PARA OS TESTES
CREATE TABLE PUBLICACAO (
CODIGO_PUBLIC VARCHAR2 (4) NOT NULL,
CODIGO_EDITORA VARCHAR2 (4),
CODIGO_AUTOR VARCHAR2 (4),
DESCRICAO_TITULO VARCHAR2 (300));
CREATE TABLE AUTOR (
CODIGO_AUTOR VARCHAR2 (4) NOT NULL,
NOME_AUTOR_INVERTIDO VARCHAR2 (250));
CREATE TABLE EDITORA (
CODIGO_EDITORA VARCHAR2 (4) NOT NULL,
NOME_EDITORA VARCHAR2 (250));
--POPULANDO AS TABELAS
--TABELA AUTOR
INSERT INTO AUTOR VALUES('0001','Machado, Antônio Cláudio da Costa');
INSERT INTO AUTOR VALUES('0002','Houaiss, AnTOnio');
INSERT INTO AUTOR VALUES('0003','KNUTH, DONALD ERVIN');
--TABELA EDITORA
INSERT INTO EDITORA VALUES('0001','Manole');
INSERT INTO EDITORA VALUES('0002','Objetiva');
INSERT INTO EDITORA VALUES('0003','ADDISON WESLEY');
INSERT INTO EDITORA VALUES('0004','LTC');
--TABELA PUBLICACAO
INSERT INTO PUBLICACAO VALUES('0001','0001','0001','Código de Processo Civil Interpretado : artigo por artigo, parágrafo por parágrafo');
INSERT INTO PUBLICACAO VALUES('0002','0002','0002','Dicionário Houaiss da língua portuguesa');
INSERT INTO PUBLICACAO VALUES('0003','0002','0002','Dicionário Houaiss de sinônimos e antônimos');
INSERT INTO PUBLICACAO VALUES('0004','0003','0003','ART OF COMPUTER PROGRAMMING - V.3');
INSERT INTO PUBLICACAO VALUES('0005','0004','0003','MATEMATICA CONCRETA');
Neste momento apresento o primeiro problema, recuperar as publicações. Podemos fazer isso facilmente com o seguinte bloco de comandos sql:
SELECT
P.DESCRICAO_TITULO,A.NOME_AUTOR_INVERTIDO,E.NOME_EDITORA
FROM
AUTOR A, EDITORA E, PUBLICACAO P
WHERE
P.CODIGO_AUTOR=A.CODIGO_AUTOR
AND
P.CODIGO_EDITORA=E.CODIGO_EDITORA
Teríamos como retorno:
Neste momento desejamos fazer uma busca, por titulo(DESCRICAO_TITULO), nome do autor(NOME_AUTOR_INVERTIDO) e nome da editora(NOME_EDITORA), com as seguintes características:
- A busca deverá ser feita com qualquer parte do nome.
- A busca deve ser “case-insensitive”.
- A acentuação ou não deve ser irrelevante na busca.
Para isso iniciaremos com a criação das preferências que serão utilizados na criação dos índices:
Begin
Ctx_Ddl.Create_Preference ('mixed_case_lexer_pref','BASIC_LEXER');
Ctx_Ddl.Set_Attribute ( 'mixed_case_lexer_pref', 'mixed_case', 'FALSE');
Ctx_Ddl.Set_Attribute ( 'mixed_case_lexer_pref', 'base_letter','TRUE');
end;
O próximo passo é a criação dos índices, neste exemplo usaremos um índice do tipo CONTEXT.
create index MIX_TITULO_01_IDX on PUBLICACAO(DESCRICAO_TITULO)
indextype is ctxsys.context
parameters ('LEXER MIXED_CASE_LEXER_PREF') ;
create index MIX_NOM_AUTOR_01_IDX on AUTOR(NOME_AUTOR_INVERTIDO)
indextype is ctxsys.context
parameters ('LEXER MIXED_CASE_LEXER_PREF') ;
create index MIX_NOME_EDITORA_01_IDX on EDITORA(NOME_EDITORA)
indextype is ctxsys.context
parameters ('LEXER MIXED_CASE_LEXER_PREF') ;
Pronto, já podemos utilizar os índices criados para pesquisa, abaixo crio um bloco de código que permite a busca desejada:
SELECT
P.DESCRICAO_TITULO,A.NOME_AUTOR_INVERTIDO,E.NOME_EDITORA
FROM AUTOR A, EDITORA E, PUBLICACAO P
WHERE
P.CODIGO_AUTOR=A.CODIGO_AUTOR
AND
P.CODIGO_EDITORA=E.CODIGO_EDITORA
AND
(
CONTAINS( NOME_AUTOR_INVERTIDO, (:NOME_PESQ))>0
OR
CONTAINS(DESCRICAO_TITULO, (:NOME_PESQ))>0
OR
CONTAINS(NOME_EDITORA, (:NOME_PESQ))>0
)
Fazendo a busca pelo nome Antônio, teremos o seguinte retorno:
Como podemos constatar a busca foi “case-insensitive” e ignorou a posição do nome solicitado.
Notem que podemos realizar pesquisas com “OR” ou “AND” entradas como:
-
Antonio OR dicionario, são válidas, inclusive podemos deixar nosso código mais sofisticado de modo que
automaticamente o mesmo procure múltiplas palavras em vários campos:
SELECT P.DESCRICAO_TITULO,A.NOME_AUTOR_INVERTIDO,E.NOME_EDITORA FROM AUTOR A, EDITORA E, PUBLICACAO P WHERE P.CODIGO_AUTOR=A.CODIGO_AUTOR AND P.CODIGO_EDITORA=E.CODIGO_EDITORA AND ( CONTAINS( NOME_AUTOR_INVERTIDO,replace((TRIM(:NOME_PESQ)),' ',' OR '))>0 OR CONTAINS(DESCRICAO_TITULO, replace((TRIM(:NOME_PESQ)),' ',' OR '))>0 OR CONTAINS(NOME_EDITORA, replace((TRIM(:NOME_PESQ)),' ',' OR '))>0 )Obs: Apesar do TRIM, esse código necessita de uma função de tratamento para o caso do usuário digitar dois ou mais espaços no meio da busca, creio que é ótimo treino para o leitor.
Caso necessitemos “dropar” essas preferências usamos o seguinte comando:
begin
Ctx_Ddl.Drop_Preference ('mixed_case_lexer_pref');
end;
No caso dos índices:
drop index MIX_TITULO_01_IDX;
drop index MIX_NOM_AUTOR_01_IDX;
drop index MIX_NOME_EDITORA_01_IDX;
V - Referências
- Oracle Text – Reference – Release 9.2, March 2002 Part Nº A98518-01
