Full Text Search no Oracle - Parte 02

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (2)  (1)

Dando continuidade ao Full Text Search no Oracle.

Full Text Search no Oracle - Parte 02

Explorando o Oracle interMedia

Carlos Duarte, Ricardo Rezende e Rodrigo Righetti

O Oracle interMedia é um conjunto de ferramentas que possibilitam o armazenamento e manipulação de dados amplamente usados na web como texto, dados, imagens, áudio e vídeo. Este conjunto de ferramentas está incluído nas versões Standard e Enterprise Edition desde a versão 8i do produto.

Como característica, permite que o banco de dados Oracle armazene, gerencie e recupere informações de localização geográfica, imagens, áudio, vídeo ou qualquer outro dado multimídia de maneira integrada com outras informações da corporação.

Este artigo irá demonstrar características do Oracle interMedia Text, um pool de ferramentas do interMedia que irá basicamente trabalhar com a indexação e busca de conteúdo web e documentos com formatação Word, PDF, etc.

 

 

Índices CTXCAT

O índice do tipo CTXCAT pode fornecer um desempenho melhor do que os índices do tipo CONTEXT porque é possível criar sub-índices internos. Eles funcionarão como os índices B-Tree compostos. No caso de uma query com mais de coluna, pode-se criar estes sub-índices para ajudar na consulta.

Para exemplificar seu uso, iniciaremos criando a estrutura de armazenamento e carga dos dados com as Listagens 14 e 15.

 

Listagem 14. Criação da estrutura para armazenamento dos dados

CREATE TABLE my_items (

  id           NUMBER(10)      PRIMARY KEY,

  name         VARCHAR2(200)   NOT NULL,

  description  VARCHAR2(4000)  NOT NULL,

  price        NUMBER(7,2)     NOT NULL,

  REGION       NUMBER                  NOT NULL);

 

CREATE SEQUENCE my_items_seq;

 

Listagem 15. Carga dos dados.

BEGIN

  FOR i IN 1 .. 1000 LOOP

    INSERT INTO my_items

      VALUES (my_items_seq.NEXTVAL, 'Bike: '||i, 'Bike Description ('||i||')', i,1);

  END LOOP;

  FOR i IN 1 .. 1000 LOOP

    INSERT INTO my_items

      VALUES (my_items_seq.NEXTVAL, 'Car: '||i, 'Car Description ('||i||')', i,2);

  END LOOP;

  FOR i IN 1 .. 1000 LOOP

    INSERT INTO my_items

      VALUES (my_items_seq.NEXTVAL, 'House: '||i, 'House Description ('||i||')', i,3);

  END LOOP;

  COMMIT;

END;

/

 

O próximo passo é criar um índice CTXCAT nas colunas DESCRIPTION e PRICE e gerarmos as estatísticas da tabela. Para a criação deste índice, é mandatório criar um conjunto de índice, definido pela cláusula INDEX_SET, com um sub-índice para cada coluna (Listagem 16) referenciada pela função CATSEARCH (Listagem 17).

 

Listagem 16. Criação do índice e geração da estatística.

EXEC CTX_DDL.CREATE_INDEX_SET('my_items_iset');

EXEC CTX_DDL.ADD_INDEX('my_items_iset','price'); /* SUB-INDEX A */

EXEC CTX_DDL.ADD_INDEX('MY_ITEMS_ISET','PRICE,REGION'); /* SUB-INDEX b */

 

CREATE INDEX my_items_name_idx

  ON my_items(description)

  INDEXTYPE IS CTXSYS.CTXCAT

  PARAMETERS ('index set my_items_iset');

 

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'MY_ITEMS', cascade=>TRUE);

 

Note que foram incluídos dois sub-índices no index set (‘PRICE’ e ‘PRICE,REGION’). Isso ajudará em consultas que irão trabalhar com uma ou duas colunas. Note que foram criados dois sub-índices nas linhas 2 e 3 do exemplo da Listagem 16. Se a pesquisa for somente pela coluna PRICE, o primeiro sub-índice A será utilizado. Se forem utilizadas as colunas PRICE e REGION, o sub-índice B será utilizado internamente para melhorar a performance.

Para finalizar, fazemos a consulta na tabela procurando por itens com a descrição que contenha a palavra especificada e o preço apropriado (Listagem 17).

Perceba que para o uso do índice com CTXCAT é mandatório a utilização do operador CATSEARCH na query. CATSEARCH é o operador utilizado para informar ao interMedia Text que um índice do tipo CTXCAT está sendo utilizado. Serão mostrados mais exemplos de operadores nos próximos tópicos.

 

Listagem 17. Executando a consulta. 

SELECT id, price, name

  FROM   my_items

  WHERE  CATSEARCH(description, 'Bike', 'price BETWEEN 1 AND 5')> 0;

 

    ID      PRICE NAME

------ ---------- ------------------------------------------------

     1          1 Bike: 1

     2          2 Bike: 2

     3          3 Bike: 3

     4          4 Bike: 4

     5          5 Bike: 5

 

5 rows selected.

 

SELECT id, price, name

  FROM   my_items

  WHERE  CATSEARCH(description, 'Car', 'price BETWEEN 101 AND 105 ORDER BY price DESC')> 0;

 

    ID      PRICE NAME

------ ---------- ------------------------------------------------

  1105        105 Car: 105

  1104        104 Car: 104

  1103        103 Car: 103

  1102        102 Car: 102

  1101        101 Car: 101

 

5 rows selected.

 

SELECT ID, PRICE, NAME

  FROM   MY_ITEMS

  WHERE  CATSEARCH(description, 'Bike', 'price BETWEEN 1 AND 5 and region = 1')> 0

 

        ID      PRICE NAME

---------- ---------- -----------------------------------------------

      3001          1 Bike: 1

      3002          2 Bike: 2

      3003          3 Bike: 3

      3004          4 Bike: 4

      3005          5 Bike: 5

 

 

No terceiro exemplo da Listagem 17, perceba que foi “concatenado” a coluna region na função CATSEARCH. Internamente o Oracle irá procurar o melhor caminho utilizando o sub-índice criado no index-set (Listagem 16).

 

SELECT id, price, name

  FROM   my_items

  WHERE  (CATSEARCH(description, 'Bike')> 0 )

  AND (price >= 1 AND PRICE <=5)

 

Índices CTXRULE e CTXXPATH

Um índice CTXRULE é usado para construir aplicações de classificação de documentos por meio de regras. É um índice criado em uma tabela de consultas, onde as consultas servem como regras (rule) para definir o critério de classificação. O operador utilizado neste índice é o MATCHES.

Já índices do tipo CTXXPATH são utilizados somente para aperfeiçoar consultas em colunas do tipo XML em que é usada a função existsNode().

Estes dois últimos tipos de índices são menos utilizados, pois atendem a aplicações extremamente específicas. O mais comum é a utilização dos índices CONTEXT e CTXCAT.

O esquema CTXSYS

O esquema CTXSYS guarda algumas informações valiosas para criação e manipulação dos índices do Oracle Text que poderão ser utilizadas como guia de consulta.

A Listagem 18 mostra os “Operadores” que podem ser utilizados para trabalhar com os índices do Oracle Text.

 

Listagem 18. Objeto Operator, do esquema CTXSYS.

SQL> select OPERATOR_NAME, NUMBER_OF_BINDS from user_operators;

 

OPERATOR_NAME                       NUMBER_OF_BINDS

------------------------------ --------------------

CATSEARCH                                         2

CONTAINS                                         12

MATCHES                                           4

SCORE                                             7

XPCONTAINS                                        1

 

No decorrer do artigo, através dos exemplos, ficará bem claro o que cada um desses operadores faz.

Após a criação de um índice intermedia, alguns objetos são gerados automaticamente conforme a Listagem 19.

 

Listagem 19. Objetos criados após a geração de um índice interMedia.

CREATE TABLE TABLEDOC(ID NUMBER PRIMARY KEY, TEXTO VARCHAR2(100));

 

create index tabledoc_idx

on tabledoc(texto)

indextype is ctxsys.context;

 

select table_name

  from user_tables

 where table_name like '%TABLEDOC%';

 

TABLE_NAME

------------------------------

DR$TABLEDOC_IDX$I

DR$TABLEDOC_IDX$K

DR$TABLEDOC_IDX$N

DR$TABLEDOC_IDX$R

TABLEDOC

 

Estas novas tabelas são importantes para entendermos o funcionamento interno do interMedia. O nome sempre irá conter o prefixo DR$ + nome do índice + $I e $K e $N e $R. A

Listagem 20 mostra uma descrição de cada uma delas.

 

Listagem 20. Examinando as tabelas criadas com o índice interMedia.

SQL> desc DR$TABLEDOC_IDX$I

 Name                                      Null?    Type

 ----------------------------------------- -------- -------------------

 TOKEN_TEXT                                NOT NULL VARCHAR2(64)

 TOKEN_TYPE                                NOT NULL NUMBER(3)

 TOKEN_FIRST                               NOT NULL NUMBER(10)

 TOKEN_LAST                                NOT NULL NUMBER(10)

 TOKEN_COUNT                               NOT NULL NUMBER(10)

 TOKEN_INFO                                         BLOB

 

SQL> desc DR$TABLEDOC_IDX$K

 Name                                      Null?    Type

 ----------------------------------------- -------- -------------------

 DOCID                                              NUMBER(38)

 TEXTKEY                                   NOT NULL ROWID

 

SQL> desc DR$TABLEDOC_IDX$N

 Name                                      Null?    Type

 ----------------------------------------- -------- -------------------

 NLT_DOCID                                 NOT NULL NUMBER(38)

 NLT_MARK                                  NOT NULL CHAR(1)

 

SQL> desc DR$TABLEDOC_IDX$R

 Name                                      Null?    Type

 ----------------------------------------- -------- -------------------

 ROW_NO                                             NUMBER(3)

 DATA                                               BLOB

 

A tabela DR$TABLEDOC_IDX$I é a mais importante do índice interMedia porque mantém um mapa de bits para todas as palavras chave dos documentos armazenados na tabela principal.

As tabelas DR$TABLEDOC_IDX$K e DR$TABLEDOC_IDX$R fazem o mapeamento do Rowid do documento armazenado na tabela principal.

Já a tabela DR$TABLEDOC_IDX$N é usada para manter um registro de documentos/registros que foram apagados, muito úteis em uma situação em que seja necessário reconstruir determinada informação.

É possível fazer select diretamente nestas tabelas, mas é desaconselhável qualquer manipulação direta a não ser que isso seja instruído pelo suporte da Oracle.

 

Manutenção dos índices

As Listagens 21, 22 e 23 apresentam como a manutenção de um índice funciona ao utilizar o interMedia Text.

 

Listagem 21. DML’s na tabela TABLEDOC.

SQL> insert into tabledoc values(1,'Usar o intermedia e muito simples');

SQL> insert into tabledoc values(2,'Intermedia e uma ferramenta poderosa, e mesmo assim facil de aprender');

SQL> commit;

 

Na Listagem 21 alguns registros são inseridos na tabela TABLEDOC, mas se tentarmos consultar os registros inseridos não será possível. Isso porque o índice está desatualizado em relação à tabela.

É possível consultar quais registros estão desatualizados pesquisando a tabela CTX_USER_PENDING como mostra a Listagem 22.

 

Listagem 22. Verificando se o índice interMedia está ou não desatualizado.

SQL> select pnd_index_name, pnd_rowid from ctx_user_pending;

 

PND_INDEX_NAME                 PND_ROWID

------------------------------ ------------------

TABLEDOC_IDX                   AAAB2SAAGAAAAHXAAA

TABLEDOC_IDX                   AAAB2SAAGAAAAHXAAB

 

A primeira coluna informa qual o índice desatualizado enquanto a segunda coluna informa o ROWID da linha que não está em sincronismo. É possível ver o registro fora de sincronismo fazendo um select na tabela where rowid = PND_ROWID.

A forma mais simples para entender este funcionamento é a seguinte: quando um DML ocorre na tabela, o rowid da linha alterado é “armazenado” na view CTX_USER_PENDING. O que está nesta view está desatualizado, mesmo que o DML seja um DELETE. Para sincronizar os registros, basta executar a procedure como mostra a Listagem 23.

 

Listagem 23. Sincronizando um índice de interMedia Text.

SQL> exec CTX_DDL.SYNC_INDEX;

 

SQL> select pnd_index_name, pnd_rowid from ctx_user_pending;

 

no rows selected

 

A freqüência com que esta procedure deve ser utilizada irá depender da freqüência de modificação dos registros, o tempo que estes registros podem estar defasados para pesquisa e o tempo que irá levar para cada sincronismo.

Este sincronismo não irá re-sincronizar todo o índice, somente as linhas que estão defasadas na view CTX_USER_PENDING.

Gerenciando documentos DATASTORE

Com o Oracle intermedia Text é possível gerenciar e executar pesquisas em diversos tipos de documentos tais como: DOC, XLS, PPT, PDF, HTML, etc... A lista completa pode ser consultada em http://tahiti.oracle.com.

Para realizar tal tarefa, podemos usar alguns dos meios de armazenamento que já possuímos tais como LOB, External Tables e busca na internet onde o documento não está armazenado no BD.

Já observamos que os dados de um índice interMedia podem ser armazenados diretamente no banco de dados. Esse método é chamado de DIRECT_DATASTORE, onde podemos usar campos do tipo VARCHAR, VARCHAR2, BLOB, CLOB ou BFILE para compor este índice.

Também é possível utilizar LONG e LONG RAW, mas como sabemos, esse tipo de armazenamento só é mantido por compatibilidade e totalmente desaconselhável desde a versão 8.

O mais interessante do interMedia Text é que ele reconhece automaticamente o formato do documento que está sendo indexado.

No site http://download-east.oracle.com/docs/cd/B19306_01/text.102/b14218/afilsupt.htm#CCREF1300 há uma lista com todos os formatos suportados pelo interMedia Text.

Também é possível criar uma Stoplist. A Stoplist é uma lista de palavras que não devem ser indexadas. Nas versões mais atuais do interMedia Text há algumas já prontas no schema CTXSYS e em vários idiomas. Também é possível criar suas Stoplists utilizando as procedures na packages CTX_DDL CREATE_STOPLIST, ADD_STOPWORD e REMOVE_STOPWORD.

A Tabela 1 mostra exemplos de palavras em inglês e a Tabela 2, palavras em português.


28-01-2008pic24.JPG 

Tabela 1. Stoplist de palavras em inglês.

28-01-2008pic25.JPG 

Tabela 2. Stoplist de palavras em português.

 

Uma lista completa esta disponível no link:

http://download-west.oracle.com/docs/cd/B19306_01/text.102/b14218/astopsup.htm#CEGBGCDF

 

Outra forma de armazenamento é o DETAIL_DATASTORE que consiste na criação de um índice interMedia que será usado na tabela principal para pesquisas onde não será necessário fazer a junção entre a tabela Principal e a tabela Detalhe, facilitando assim o gerenciamento destes documentos.

Vejamos uma demonstração na Listagem 24 onde primeiro criaremos nossas tabelas Principal e Detalhe.

 

Listagem 24. Criação das tabelas Principal e Detalhe.

CREATE TABLE ORDEM_COMPRA

  (ORDID       NUMBER PRIMARY KEY,

   DESCRI      VARCHAR2(150),

   ITEM_DET_J  CHAR(1));

 

CREATE TABLE ORDEM_DET

  (DETID        NUMBER,

   DETSEQ       NUMBER,

   ITEM_DETAIL  VARCHAR2(1000));

 

INSERT INTO ORDEM_COMPRA(ORDID,DESCRI)

  VALUES(1,’Itens de Escritorio’);

INSERT INTO ORDEM_COMPRA(ORDID,DESCRI)

  VALUES(2,’Itens de Manutencao’);

INSERT INTO ORDEM_DET(DETID, DETSEQ,ITEM_DETAIL)

  VALUES(1,1,’Canetas para serem usadas pela engenharia’);

INSERT INTO ORDEM_DET(DETID, DETSEQ, ITEM_DETAIL)

  VALUES(1,2,’Papeis timbrados da Oracle’);

INSERT INTO ORDEM_DET(DETID, DETSEQ, ITEM_DETAIL)

  VALUES (1,3,’Teclado ergometrico’);

INSERT INTO ORDEM_DET(DETID, DETSEQ, ITEM_DETAIL)

  VALUES (2,1,’Hard Drive 80gb’);

COMMIT;

 

Para que o DETAIL_DATASTORE funcione, precisamos criar uma definição de preferência e o índice baseado sobre essa preferência. É necessário o privilégio de execução na package CTX_DDL. Veja o exemplo na Listagem 25.

 

Listagem 25. Criação da preferência e do índice baseado nela.

GRANT EXECUTE ON CTXSYS.CTX_DDL TO ORDSYS;

 

BEGIN

  CTX_DDL.CREATE_PREFERENCE(‘IT_PREF’,’DETAIL_DATASTORE’);

  CTX_DDL.SET_ATTRIBUTE(‘IT_PREF’,’DETAIL_TABLE’,’ORDEM_DET’);

  CTX_DDL.SET_ATTRIBUTE(‘IT_PREF’,’DETAIL_KEY’,’DETID’);

  CTX_DDL.SET_ATTRIBUTE(‘IT_PREF’,’DETAIL_LINENO’,’DETSEQ’);

  CTX_DDL.SET_ATTRIBUTE(‘IT_PREF’,’DETAIL_TEXT’,’ITEM_DETAIL’);

END;

/

 

CREATE INDEX PREF_IDX ON ORDEM_COMPRA(ITEM_DET_J)

  INDEXTYPE IS CTXSYS.CONTEXT

  PARAMETERS(‘DATASTORE IT_PREF’);

 

SELECT *

  FROM ORDEM_COMPRA

  WHERE CONTAINS(ITEM_DET_J,’engenharia’) >0;

 

     ORDID DESCRI                                              

---------- ----------------------------------------------

         1 Itens de Escritório

 

Veja que a pesquisa foi feita pela string “engenharia”. O banco, internamente, procurou na tabela de detalhes qual registro continha esta entrada. Após encontrar o registro, lê a definição da preferência no metadata e retorna o registro da tabela pai correspondente.

Outra forma de gerenciamento e pesquisa é o armazenamento externo usando o módulo FILE_DATASTORE (Listagem 26) e URL_DATASTORE (Listagem 27).

Esse método irá usar URLs (Uniform Resource Locators) para identificar os arquivos e não fará referência ao campo do tipo BFILE. Isto permite efetuarmos buscas diretamente em arquivos armazenados na web via requisição http e ftp, assim como as ferramentas de buscas mais avançadas.

 

Listagem 26. Criando um FILE_DATASTORE.

begin

 ctx_ddl.create_preference('COMMON_DIR','FILE_DATASTORE');

 ctx_ddl.set_attribute('COMMON_DIR','PATH','/mydocs');

end;

 

create table mytable

  (id number primary key,

   docs varchar2(2000));

 

insert into mytable

  values(111555,'first.txt');

insert into mytable

  values(111556,'second.txt');

commit;

 

create index myindex on mytable(docs)

  indextype is ctxsys.context

  parameters ('datastore COMMON_DIR');

 

Na Listagem 26 criamos uma referência de file datastore chamada COMMON_DIR cujo caminho é /mydocs.

Ao inserir dados na tabela, é necessário apenas indicar o nome do arquivo. O atributo PATH informa ao sistema onde procurar durante o processo de indexação. Depois é só criar o índice.

Já no exemplo da Listagem 27 foi criada uma preferência de URL_DATASTORE chamada URL_PREF para a qual foram definidos os atributos http_proxy, no_proxy e timeout. Depois foi criada a tabela e inseridos os valores, contendo as URLs em questão. Finalmente, foi criado o índice especificando URL_PREF como um datastore.

 

Listagem 27. Criando uma URL datastore.

begin

 ctx_ddl.create_preference('URL_PREF','URL_DATASTORE');

 ctx_ddl.set_attribute('URL_PREF','HTTP_PROXY','www-proxy.us.oracle.com');

 ctx_ddl.set_attribute('URL_PREF','NO_PROXY','us.oracle.com');

 ctx_ddl.set_attribute('URL_PREF','Timeout','300');

end;

 

create table urls

  (id number primary key,

   docs varchar2(2000));

 

insert into urls

  values(111555,'http://context.us.oracle.com');

insert into urls

  values(111556,'http://www.sun.com');

commit;

 

create index datastores_text on urls ( docs )

  indextype is ctxsys.context

  parameters ( 'Datastore URL_PREF' );

 

A grande mágica do FILE_DATASTORE e URL_DATASTORE é que a informação gerada no banco é somente o índice.

Imagine que você tem um website interno e deseja indexá-lo sem carregar todas as páginas que são dinâmicas para o seu banco de dados. Isso é possível somente fornecendo o link para o interMedia Text. Ele irá ler toda a página e indexá-la da forma mais eficiente para consulta. Isso sem precisar gravar a página no disco.

Queries

Como regra geral, a única ferramenta necessária para utilização do interMedia é a clausula “CONTAINS”. Todo o restante da query é exatamente igual, como cláusula where, group, having, count e as demais. Para ajudar na pesquisa e trazer resultados mais seletivos, é possível utilizar alguns operadores. Abaixo segue alguns nomes, exemplos e funcionalidades para cada um deles (uma lista completa pode ser encontrada no site: http://download-east.oracle.com/docs/cd/B19306_01/text.102/b14218/cqoper.htm#sthref1006)

Todos os operadores citados aqui deverão ser utilizados dentro da clausula CONTAINS.

NEAR

Este operador avalia se uma palavra está perto da outra, com qual distancia e se deve respeitar a ordem na função.

Na Listagem 28, a query deve respeitar se a palavra DOG esta perto da palavra CAT em 50 posições e deve respeitar a ordem. Primeiro encontrar DOG, depois CAT.

 

Listagem 28. Query com o operador NEAR.

SQL> select *

      from ANIMAIS

      where contains(TIPO,'near((dog, cat), 50, TRUE)') > 0;

MINUS

Este operador remove uma palavra da expressão pesquisada. Pode também ser utilizado o operador “-“. O exemplo da Listagem 29 irá selecionar todos os modelos de carro da FORD que não tenha a palavra ESPORTIVO.

 

Listagem 29. Query com o operador MINUS.

SQL> select *

      from CARROS

      where contains(MODELO,'FORD - esportivo') > 0;

 

SQL> select *

      from CARROS

      where contains(MODELO,'FORD MINUS esportivo') > 0;

STEM

Este operador pesquisa palavras com a mesma lingüística. Ele irá considerar que quando o usuário faz pesquisa por cat, também seja aceito cats ou qualquer outra variação da palavra como na Listagem 30.

Pode também ser utilizado o operador “$“.

 

Listagem 30. Query com o operador STEM.

SQL> select *

      from ANIMAIS

      where contains(TIPO,'$CAT') > 0;

 

TIPO

---------------

cat

cats

 

O mais importante neste tópico é notar que as queries são sempre seguidas do “CONTAINS” e o operador desejado. O restante é só intercalar os valores e funções.

Estas funções podem ser utilizadas em conjunto como segue na Listagem 31 com os operadores AND (&) e OR ( | ). A query irá buscar qualquer ocorrência da palavra “cat” e suas variações e também deverá trazer palavras “dog” ou “dogs” e não deverá trazer palavras “cobra”.

 

Listagem 31. Query com mais de um operador.

SQL> select *

      from ANIMAIS

      where contains(TIPO,'$CAT & (DOg | DOGS) MINUS COBRA') > 0;

Conclusões

A grande questão em termos de banco de dados é: “Tenho a informação armazenada, mas como recuperá-la rapidamente quando eu precisar dela?”.

Para responder esta pergunta, o Oracle nos traz o Oracle interMedia e, em especial, o Oracle Text capaz de recuperar informações de forma eficiente. Uma bela ferramenta para buscar a informação onde quer que ela esteja armazenada.

 

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?