Carlos,
Primeiro, precisamos de uma tabela grandinha para simular o índice e a trigger.
O número de registros é de aproximadamente 1,5 milhões de linhas. Chegamos a
essa tabela inserindo nela mesmo 5 vezes, ou seja,
--
-- insert /*+ append */ into t select * from t;
-- commit;
-- insert /*+ append */ into t select * from t;
-- commit;
-- insert /*+ append */ into t select * from t;
-- commit;
-- insert /*+ append */ into t select * from t;
-- commit;
-- insert /*+ append */ into t select * from t;
-- commit;
--
[EMAIL PROTECTED]> create table t as
2 select object_name nome_acento, created, object_name nome_sem
3 from all_objects
4 /
Table created.
[EMAIL PROTECTED]> select count(*) from t;
COUNT(*)
-------------
1574913
1 row selected.
Agora a trigger, a idéia é usar uma trigger *before* insert para que
possamos alterar o valor da :new.nome_sem (nome sem acento).
[EMAIL PROTECTED]>
[EMAIL PROTECTED]> create or replace trigger t_bi_fer
2 before insert on t for each row
3 begin
4 :new.nome_sem := translate(:new.nome_acento,
5 'ÁÉÍÓÚáéíóúÇçÀàÃãÕõÂâÊêÔô','AEIOUaeiouCcAaAaOoAaEeOo');
6 -- voce poderia colocar o TRIM aqui
7 -- para outro propósito
8 end;
9 /
Trigger created.
Testando: fazemos um insert na tabela com o valor abaixo e a trigger
fará a alteração (translate), logo abaixo, criamos dois índices. Um normal
e outro por função.
[EMAIL PROTECTED]> insert into t ( nome_acento, created )
2 values ('MÁRCIO COM ACÊNTO PORTES', sysdate);
1 row created.
[EMAIL PROTECTED]> select * from t
2 where nome_sem like 'MARCIO%'
3 /
NOME_ACENTO CREATED NOME_SEM
------------------------------ -------------------
------------------------------
MÁRCIO COM ACÊNTO PORTES 30/10/2005 19:10:37 MARCIO COM ACENTO PORTES
1 row selected.
Criação dos índices, o primeiro é um FBI (Function Based Index) em cima da
função
translate(nome_acento, ..., ...) e o segundo um índice normal na coluna nome_sem
que terá o valor da coluna nome_acento *sem* os acentos.
[EMAIL PROTECTED]>
[EMAIL PROTECTED]> create index t_idx_acento on t ( translate(nome_acento,
2 'ÁÉÍÓÚáéíóúÇçÀàÃãÕõÂâÊêÔô','AEIOUaeiouCcAaAaOoAaEeOo') );
Index created.
[EMAIL PROTECTED]>
[EMAIL PROTECTED]> create index t_idx_sem on t( nome_sem );
Index created.
Para que o otimizador saiba como escolher melhor os índices, precisamos coletar
as
estatísticas corretamente. Pesquise sobre histogramas se for seu caso, para o
exemplo
eu não precisei coletar histogramas.
[EMAIL PROTECTED]>
[EMAIL PROTECTED]> exec dbms_stats.gather_table_stats( user, 'T', cascade =>
true );
PL/SQL procedure successfully completed.
[EMAIL PROTECTED]>
[EMAIL PROTECTED]> set autotrace on
[EMAIL PROTECTED]>
[EMAIL PROTECTED]> select * from t
2 where nome_sem like 'MARCIO%'
3 /
NOME_ACENTO CREATED NOME_SEM
------------------------------ -------------------
------------------------------
MÁRCIO COM ACÊNTO PORTES 30/10/2005 19:10:37 MARCIO COM ACENTO PORTES
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2635768789
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56 | 3248 | 41 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 56 | 3248 | 41 (0)|
00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX_SEM | 56 | | 3 (0)|
00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NOME_SEM" LIKE 'MARCIO%')
filter("NOME_SEM" LIKE 'MARCIO%')
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
588 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
[EMAIL PROTECTED]> select * from t
2 where translate(nome_acento,
3 'ÁÉÍÓÚáéíóúÇçÀàÃãÕõÂâÊêÔô','AEIOUaeiouCcAaAaOoAaEeOo') like 'MARCIO%'
4 /
NOME_ACENTO CREATED NOME_SEM
------------------------------ -------------------
------------------------------
MÁRCIO COM ACÊNTO PORTES 30/10/2005 19:10:37 MARCIO COM ACENTO PORTES
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3012642426
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56 | 3248 | 42
(0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 56 | 3248 | 42
(0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX_ACENTO | 57 | | 3
(0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access(TRANSLATE("NOME_ACENTO",'ÁÉÍÓÚáéíóúÇçÀàÃãÕõÂâÊêÔô','AEIOUaeiouCcAaAaOo
AaEeOo') LIKE 'MARCIO%')
filter(TRANSLATE("NOME_ACENTO",'ÁÉÍÓÚáéíóúÇçÀàÃãÕõÂâÊêÔô','AEIOUaeiouCcAaAaOo
AaEeOo') LIKE 'MARCIO%')
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
7 consistent gets
4 physical reads
0 redo size
584 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Tá ai o exemplo dos dois métodos, tanto mantendo o campos sem acentos com
trigger,
quanto ter um índice baseado em função.
--
Marcio Portes
http://mportes.blogspot.com
Carlos Geiser wrote:
> Marcio,
> Desculpe a singela pergunta, mas seria essa trigger ?
> Também aproveito para perguntar, se tem como adicionar nessa trigger a
> função TRIM( ), assim forçaria a remoção dos espaços em brancos a direita.
>
> Grato,
> Carlos Geiser
>
> ----- Original Message -----
> From: "Marcio Portes" <[EMAIL PROTECTED]>
> To: <[email protected]>
> Sent: Saturday, October 29, 2005 7:49 AM
> Subject: Re: [oracle_br] RES: Função para remover acentuações
>
>
> Sobre as views, elas usam o índice da tabela sim.
>
> Sobre o campo, excelente idéia, voce pode manter esse campo usando uma
> trigger simples onde voce faria o translate do
> campo e guardaria ele "cru" sem acentos, no momento da consulta, voce usa
> ele (indexado).
> Grande Idéia!
>
> Carlos Geiser wrote:
>
>>Olá Marcio,
>>
>>é uma pena que os índices não serão usados, pois já o FireBird a
>
> utilização
>
>>dessa função de remover acentos os índices são com certeza usando !!
>>Mas é muito interessante essa sua sugestão de criar o índice da tabela
>>utilizando essa função. Vou dar uma estudada sobre essa assunto
>>Será que as Vistas/Views herdaria esse tipo de indice gerado com essa
>>função ?
>>
>>Já até pensei em criar um segundo campo, paralelo ao campo original, para
>>armazenar os nomes sem acentuações, pois nesse caso não teria queda de
>>performance para consultas, mas contra partida teria um quase dublo
>>cadastro. Mas isso só vou fazer em ultimo caso.
>>
>>Grato,
>>
>>Carlos Geiser
>>
>>----- Original Message -----
>>From: "Marcio Portes" <[EMAIL PROTECTED]>
>>To: <[email protected]>
>>Sent: Saturday, October 29, 2005 12:58 AM
>>Subject: Re: [oracle_br] RES: Função para remover acentuações
>>
>>
>>Eu não sou muito fã de usar função no sql. Isso mata a performance,
>>principalmente se a tabela for grande.
>>No seu caso eu criaria um FBI em
>>
>>create index tira_acento_idx on vista_arquivo_pai( translate(nome_pessoa,
>>..., ...));
>>
>>Usaria CBO e faria o sql direto sem a função. Neste caso evitaria a
>
> chamada
>
>>da função para cada linha do fetch. Sua
>>query executaria mais rápido.
>>
>>Pequeno exemplo em
>>
>
> http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1547006324238#10897337104699
>
>>E respondendo sua pergunta, *não* os índices não serão usados! Como saber
>
> se
>
>>a query utilizou algum índice ou não basta
>>dar um explain na sessão.
>>
>>set autotrace on
>>
>>
>>Carlos Geiser wrote:
>>
>>
>>>Ai está a função para fazer a consulta ignorando as acentuações:
>>>
>>>CREATE OR REPLACE FUNCTION FNC_TIRA_ACENTOS
>>>(V_TEXTO_ACENTUADO VARCHAR2) Return String
>>>Is
>>>v_temp VARCHAR2(2000);
>>>BEGIN
>>>SELECT TRANSLATE(V_TEXTO_ACENTUADO,
>>>'ÁÉÍÓÚáéíóúÇçÀàÃãÕõÂâÊêÔô','AEIOUaeiouCcAaAaOoAaEeOo') INTO v_temp
>>
>>>FROM DUAL;
>>
>>>RETURN v_temp;
>>>END FNC_TIRA_ACENTOS;
>>>
>>>Exemplo::
>>>select * from vista_arquivo_pai where FNC_TIRA_ACENTOS (nome_pessoa)
>>>FNC_TIRA_ACENTOS (like(tiraacentos('JOSÉ%')))
>>>
>>>Agora é saber se com a utilização dessa função os índices são usados !
>>>Alguém saberia informar como saber se a consulta utiliza os índices ou não
>>
>>?
>>
>>
>>>Grato,
>>>Carlos Geiser.
>>>
>>>
>>>
>>>
>>>
>>>----- Original Message -----
>>>From: "Gari Julio Einsfeldt" <[EMAIL PROTECTED]>
>>>To: <[email protected]>
>>>Sent: Thursday, October 27, 2005 1:19 PM
>>>Subject: RES: [oracle_br] RES: Função para remover acentuações
>>>
>>>
>>>Estava acampanhando o caso meio por cima.
>>>Alimentei ele com uma idéia.
>>>
>>>Vi que estavas tentando usar o SOUNDEX e não conseguisse, então tens
>>
>>algumas
>>
>>
>>>alternativas:
>>>1. Se quiser que os caracteres acentuados sejam substituidos nas tabelas,
>>>vale o comentario do Ederson;
>>>2. Se quiseres manter o dado original e por alguma situação queres usar o
>>>dado que vem da base sem a acentuação, basta criar uma função (ou algo
>>>parecido), conforme exemplos;
>>>3. Se quiseres manter o dado original e queres apensa fazer buscas sem
>>>considerar a acentuação (ex: considerar Joao e João a mesma coisa), usas a
>>>função soundex.
>>>
>>>Fora estas tres alternativas, não me aprofundei muito no caso, mas do
>>
>>pouco
>>
>>
>>>que li vislumbro apenas as alternativa acima.
>>>
>>>
>>>
>>>-----Mensagem original-----
>>>De: [email protected] [mailto:[EMAIL PROTECTED]
>>>nome de Ederson
>>>Enviada em: quinta-feira, 27 de outubro de 2005 14:09
>>>Para: [email protected]
>>>Assunto: [oracle_br] RES: Função para remover acentuações
>>>
>>>
>>>Carlos,
>>>
>>>O exemplo do colega Gari apenas formata a saída, para substituir os
>>>caracteres na tabela, deve-se usar o comando UPDATE tabela set
>>>campo=replace(campo.....
>>>
>>>
>>>Ederson Elias de Oliveira
>>>DBA Oracle
>>>Setransp - GO
>>>-------------------------------------------------------------------
>>>
>>>-----Mensagem original-----
>>>De: [email protected] [mailto:[EMAIL PROTECTED] Em
>>>nome de Carlos Geiser
>>>Enviada em: quinta-feira, 27 de outubro de 2005 11:16
>>>Para: [email protected]
>>>Assunto: Re: [oracle_br] Re: Função para remover acentuações
>>>
>>>Olá,
>>>
>>>não sei se compreendi direito,
>>>mas está parecendo que esse seu exemplo seria para repassar ou substituir
>>>fisicamente os caracteres acentuados pelos correspondents não acentuados !
>>>
>>>Eu que almejo é uma solução para que o select faça uma consulta ingnorando
>>>as acentuações !
>>>
>>>Grato,
>>>Carlos Geiser
>>>
>>>----- Original Message -----
>>>From: "Gari Julio Einsfeldt" <[EMAIL PROTECTED]>
>>>To: <[email protected]>
>>>Sent: Thursday, October 27, 2005 11:29 AM
>>>Subject: RES: [oracle_br] Re: Função para remover acentuações
>>>
>>>
>>>SQL> select replace('CARRO','R','S') FROM DUAL;
>>>
>>>REPLACE('CARRO','R','S')
>>>-----------------------------------------------
>>>CASSO
>>>
>>>
>>>No teu caso:
>>>
>>>select replace(campo,
>>> 'ÁÉÍÓÚáéíóúÀÈÌÒÙàèìòùÂÊÎÔÛâêîôûÄËÏÖÜäëïöüÃÕãõÇçÑñºª',
>>> 'AEIOUaeiouAEIOUaeiouAEIOUaeiouAEIOUaeiouAOaoCcNnoa')
>>
>>>from tabela;
>>
>>>
>>>Creio que teu problema se resolva com isso.
>>>
>>>
>>>
>>>
>>>ORACLE_BR APOIA 2ºENPO-BR
>>>_____________________________________________________________________
>>>O 2º Encontro Nacional de Profissionais Oracle será realizado no dia
>>>05/11/2005 no auditório da FIAP em São Paulo. Serão apresentadas Palestras
>>
>>e
>>
>>
>>>Cases dirigidos exclusivamente por profissionais especialistas e renomados
>>>no mercado. Confira a programação no site do evento!
>>
>>http://www.enpo-br.org/
>>
>>
>>>_____________________________________________________________________
>>>Area de download oracle_br - http://www.4shared.com/dir/101727/a4dcc423
>>>Links do Yahoo! Grupos
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>ORACLE_BR APOIA 2ºENPO-BR
>>>_____________________________________________________________________
>>>O 2º Encontro Nacional de Profissionais Oracle será realizado no dia
>>>05/11/2005 no auditório da FIAP em São Paulo. Serão apresentadas Palestras
>>
>>e
>>
>>
>>>Cases dirigidos exclusivamente por profissionais especialistas e renomados
>>>no mercado. Confira a programação no site do evento!
>>
>>http://www.enpo-br.org/
>>
>>
>>>_____________________________________________________________________
>>>Area de download oracle_br - http://www.4shared.com/dir/101727/a4dcc423
>>>
>>>
>>> Yahoo! Grupos, um serviço oferecido por:
>>>
>>>
>>>
>>>--------------------------------------------------------------------------
>>
>>--
>>
>>
>>>----
>>>Links do Yahoo! Grupos
>>>
>>> a.. Para visitar o site do seu grupo na web, acesse:
>>> http://br.groups.yahoo.com/group/oracle_br/
>>>
>>> b.. Para sair deste grupo, envie um e-mail para:
>>> [EMAIL PROTECTED]
>>>
>>> c.. O uso que você faz do Yahoo! Grupos está sujeito aos Termos do
>>
>>Serviço
>>
>>
>>>do Yahoo!.
>>>
>>>
>>>
>>>
>>>
>>>ORACLE_BR APOIA 2ºENPO-BR
>>
>>_____________________________________________________________________
>>
>>
>>>O 2º Encontro Nacional de Profissionais Oracle será realizado no dia
>>
>>05/11/2005 no auditório da FIAP em São Paulo. Serão apresentadas Palestras
>
> e
>
>>Cases dirigidos exclusivamente por profissionais especialistas e renomados
>>no mercado. Confira a programação no site do evento!
>
> http://www.enpo-br.org/
>
>>>_____________________________________________________________________
>>>Area de download oracle_br - http://www.4shared.com/dir/101727/a4dcc423
>>>Links do Yahoo! Grupos
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>
--
Marcio Portes
http://mportes.blogspot.com
ORACLE_BR APOIA 2ºENPO-BR
_____________________________________________________________________
O 2º Encontro Nacional de Profissionais Oracle será realizado no dia 05/11/2005
no auditório da FIAP em São Paulo. Serão apresentadas Palestras e Cases
dirigidos exclusivamente por profissionais especialistas e renomados no
mercado. Confira a programação no site do evento! http://www.enpo-br.org/
_____________________________________________________________________
Area de download oracle_br - http://www.4shared.com/dir/101727/a4dcc423
Links do Yahoo! Grupos
<*> Para visitar o site do seu grupo na web, acesse:
http://br.groups.yahoo.com/group/oracle_br/
<*> Para sair deste grupo, envie um e-mail para:
[EMAIL PROTECTED]
<*> O uso que você faz do Yahoo! Grupos está sujeito aos:
http://br.yahoo.com/info/utos.html
