Para fazer busca em campos com textos longos no Oracle é possível utilizar o operador “CONTAINS”, basta criar um índice do tipo CONTEXT e você poderá realizar buscas de texto nessa coluna.
Para criar o índice do tipo CONTEXT utilize o seguinte comando:
CREATE INDEX [idx_name] ON [table] ([column]) indextype is ctxsys.context;E para realizar a busca no campo, utilize:
SELECT * FROM [table] WHERE CONTAINS([column], '1', 1) > 0;Para demostrar como isso funciona, vamos ver um exemplo:
1- Criamos uma tabela:
CREATE TABLE "Conteudo"
(
"ConteudoID" INTEGER NOT NULL ,
"Titulo" VARCHAR2 (50) NOT NULL ,
"Detalhe" CLOB
)
;
ALTER TABLE "Conteudo"
ADD CONSTRAINT "Conteudo_PK" PRIMARY KEY ( "ConteudoID" );2- Inserimos um registro para teste:
INSERT INTO "Conteudo" ("ConteudoID", "Titulo", "Detalhe") values (1, 'Teste 01', 'Primeiro Conteudo Teste');
commit;3- Criamos o índice na coluna:
<pre class="brush: sql; title: ; notranslate" title="">
CREATE INDEX conteudo_cidx01 ON "Conteudo" ("Detalhe") indextype is ctxsys.context;
commit;4- Agora, podemos realizar buscas nesse campo utilizando o operador CONTAINS, por exemplo:
select * from "Conteudo" where CONTAINS("Detalhe", 'Primeiro Conteudo', 1) > 0;
select * from "Conteudo" where CONTAINS("Detalhe", '%Prim%', 1) > 0;
select * from "Conteudo" where CONTAINS("Detalhe", '"Primeiro" and "Conteudo"', 1) > 0;5- Agora, vamos inserir mais um registro para teste:
INSERT INTO "Conteudo" ("ConteudoID", "Titulo", "Detalhe") values (2, 'Teste 02', 'Segundo Conteudo Teste');
commit;6- No entanto, quando realizamos o comando SELECT abaixo nenhum registro é retornado.
select * from "Conteudo" where CONTAINS("Detalhe", 'Segundo Conteudo', 1) > 0;A documentação do Oracle diz o seguinte sobre esse tipo de índice:
3.3.5.1 CONTEXT Index and DML
A CONTEXT index is not transactional. When a record is deleted, the index change is immediate. That is, your own session will no longer find the record from the moment you make the change, and other users will not find the record as soon as you commit. For inserts and updates, the new information will not be visible to text searches until an index synchronization has occurred. Therefore, when you perform inserts or updates on the base table, you must explicitly synchronize the index with CTX_DDL.SYNC_INDEX.
link: http://docs.oracle.com/cd/B28359_01/text.111/b28303/ind.htm
Ou seja, quando registros são inseridos ou atualizados no banco de dados utilizando o admin do portal, o índice não é automaticamente atualizado, então o resultado não aparece na busca.
Esse outro documento (http://docs.oracle.com/cd/B28359_01/text.111/b28303/ind.htm#i1008452) tem um exemplo de como fazer essa sincronização:
begin
ctx_ddl.sync_index('myindex', '2M');
end;7- Então, fazemos a sincronização:
begin
ctx_ddl.sync_index('conteudo_cidx01', '2M');
end;É possível atualizar todos os índices em uma Stored Procedure e agendar um job para executar essa proc a cada uma hora, essa procedure ficaria assim:
CREATE OR REPLACE PROCEDURE pr_index_context_update
IS
BEGIN
ctx_ddl.sync_index('conteudo_cidx01', '2M');
END;
/8- Após rodar o comando, podemos repetir o comando SELECT irá trazer os resultados da linha incluída:
select * from "Conteudo" where CONTAINS("Detalhe", 'Segundo Conteudo', 1) > 0;