Mr. Fabiano gave a excellent idea that allows me to comply with the project
specifications. I use that idea to make a view that concatenates all fields
used in the search.
CREATE VIEW LISTA_CLIENTES_PESQUISA (IDCLIENTES, PESQUISA)
AS
select c.IDCLIENTES,cp.DESCRICAO||p.NOME||c.IDCLIENTES||c.CLIENTE||c.MORADA||
c.CP||c.LOCALIDADE||c.PAIS||c.TELEFONE||c.FAX||
c.EMAIL||
c.PER_DESCONTO||
c.CONTRIBUINTE||c.COD_CLIENTE_FAT
FROM CLIENTES c
inner join PAISES p on p.CODIGO_ISO_3166_1=c.PAIS
inner join COND_PAGAMENTO cp on cp.IDCOND_PAGAMENTO=c.CONDICOES_PAGAMENTO
order by c.CLIENTE;
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON LISTA_CLIENTES_PESQUISA TO SYSDBA WITH GRANT OPTION;
Then the slow query is replaced with this:
select first 20 IDCLIENTES from LISTA_CLIENTES_PESQUISA where PESQUISA like
'%crist%'
Now it runs fast as expected without duplicating any Kb of data.
I would like to thank you all for your help. You provided good ideas that can
be use in other situations.
