Performance - Comparacao entre dados da mesma tabela usando LIKE

[jcemb 0]

Pessoal, minha primeira vez aqui e ja comeco com um problema dos grandes. Irei primeiro explicar o cenario e depois o problema.

O cliente possui uma tabela J2AATIVT com milhoes de registros. Essa tabela possui 2 campos como indice J2AIDATI (identificador do registro - campo unico na tabela) e J2AIDCTS (identificador do sistema) alem de varios campos VARCHAR2.

O cliente necessita que seja construida uma procedure que compare os registros dessa mesma tabela, so que a comparacao nao utiliza indice, a comparacao precisa ser feita nos campos varchar2 e para piorar utilizando like, segue exemplo:

SELECT ORIGEM.*, DESTINO.* 
FROM  (SELECT * FROM J2AATIVT A WHERE A.J2AIDCTS = 6) ORIGEM 
     , (SELECT * FROM J2AATIVT C WHERE C.J2AIDCTS = 1 ) DESTINO 
WHERE 
(ORIGEM.J2ANUMPL IS NOT NULL AND DESTINO.J2ANUMPL IS NOT NULL) AND (ORIGEM.J2ANUMPL <> ' ' AND DESTINO.J2ANUMPL <> ' ') AND (ORIGEM.J2ANUMPL like '%' || DESTINO.J2ANUMPL || '%' OR DESTINO.J2ANUMPL like '%' || ORIGEM.J2ANUMPL || '%')
OR ((ORIGEM.J2ANUMTI IS NOT NULL AND DESTINO.J2ANUMTI IS NOT NULL) AND (ORIGEM.J2ANUMTI <> ' ' AND DESTINO.J2ANUMTI <> ' ') AND (ORIGEM.J2ANUMTI like '%' || DESTINO.J2ANUMTI || '%' OR DESTINO.J2ANUMTI like '%' || ORIGEM.J2ANUMTI || '%')); 

Como voces podem ver, e criado 2 sub-queries, onde a Origem lista todos os registros pertencentes ao sistema (J2AIDCTS) 6 e o Destino, sistema 1. Alem disso a comparacao entre as sub-queries e feita utilizando (ORIGEM LIKE DESTINO) OR (DESTINO LIKE ORIGEM). E para piorar mais um pouco a comparacao nao e feita apenas nessas 2 colunas e sim em 10 colunas, no mesmo formato e para piorar ainda mais a comparacao no exemplo esta acontecendo apenas entre origem e destino, sistemas 6 e 1, mas o cliente quer que a comparacao seja feita entre a origem e todos os sistemas que existem na base (hoje existem 10), portanto, seriam 9 destinos.

Ja coloquei isso na query e vi que ficou inviavel, a nao ser que exista outro tipo de comparacao mais eficaz que eu desconheca.

Estava pensando em fazer essas comparacoes separadas, guardar na memoria em cursores e depois fazer a comparacao entre todos os sistemas num loop, mas ainda assim acho que ficara muito lento e pesado.

Nao sei se ficou muito complicado o entendimento, mas caso nao entendam algo e so perguntar.

Estou aberto a sugestoes e realmente estou precisando muito de alguma ajuda...

Abs....

[Motta 645]

Já pensou em criar colunas indexidas nas tabelas e montar uma aplicação plsql que faria o ponteiramento estre as tabelas ?

 

As queries de consulta ficariam mais simples e rápidas.

[jcemb 0]

Já pensou em criar colunas indexidas nas tabelas e montar uma aplicação plsql que faria o ponteiramento estre as tabelas ?

 

As queries de consulta ficariam mais simples e rápidas.

 

Entao cara, nao posso mexer na tabela (lembrando que nao sao tabelas, existe uma unica tabela). E mesmo se pudesse, nada adiantaria, estou usando OR e LIKE, os indices nao seriam utilizados.

[Motta 645]

jcemb :

 

1) faz um chinês simulando o problema, deve ficar mais fácil de entender.

 

2) existe um tipo de índice (baeado em funções) que poderia ter ajudar, veja aqui.

 

Obs : Talvez assim fique um pouco melhor

 

SELECT *
FROM   J2AATIVT A , J2AATIVT C
WHERE  (A.J2AIDCTS = 6 AND C.J2AIDCTS = 1)
AND    (
(ORIGEM.J2ANUMPL IS NOT NULL AND DESTINO.J2ANUMPL IS NOT NULL) AND (ORIGEM.J2ANUMPL <> ' ' AND DESTINO.J2ANUMPL <> ' ') 
AND (ORIGEM.J2ANUMPL like '%' || DESTINO.J2ANUMPL || '%' OR DESTINO.J2ANUMPL like '%' || ORIGEM.J2ANUMPL || '%')
OR ((ORIGEM.J2ANUMTI IS NOT NULL AND DESTINO.J2ANUMTI IS NOT NULL) AND (ORIGEM.J2ANUMTI <> ' ' AND DESTINO.J2ANUMTI <> ' ') 
AND (ORIGEM.J2ANUMTI like '%' || DESTINO.J2ANUMTI || '%' OR DESTINO.J2ANUMTI like '%' || ORIGEM.J2ANUMTI || '%')); 
)

[jcemb 0]

jcemb :

 

1) faz um chinês simulando o problema, deve ficar mais fácil de entender.

 

2) existe um tipo de índice (baeado em funções) que poderia ter ajudar, veja aqui.

 

Obs : Talvez assim fique um pouco melhor

 

SELECT *
FROM   J2AATIVT A , J2AATIVT C
WHERE  (A.J2AIDCTS = 6 AND C.J2AIDCTS = 1)
AND    (
(ORIGEM.J2ANUMPL IS NOT NULL AND DESTINO.J2ANUMPL IS NOT NULL) AND (ORIGEM.J2ANUMPL <> ' ' AND DESTINO.J2ANUMPL <> ' ') 
AND (ORIGEM.J2ANUMPL like '%' || DESTINO.J2ANUMPL || '%' OR DESTINO.J2ANUMPL like '%' || ORIGEM.J2ANUMPL || '%')
OR ((ORIGEM.J2ANUMTI IS NOT NULL AND DESTINO.J2ANUMTI IS NOT NULL) AND (ORIGEM.J2ANUMTI <> ' ' AND DESTINO.J2ANUMTI <> ' ') 
AND (ORIGEM.J2ANUMTI like '%' || DESTINO.J2ANUMTI || '%' OR DESTINO.J2ANUMTI like '%' || ORIGEM.J2ANUMTI || '%')); 
)

 

Entao, vamos por partes.

Vou colocar aqui a minha tabela e tentar desenhar o que preciso.

TABELA: J2AATIVT

CAMPOS: J2FIDATI (PK), J2AIDCTS (FK), J2ANUMPL, J2ANUMTI, ...

 

O campo J2AIDCTS e referente aos sistemas, que hoje meu range e de 1 a 10.

Preciso comparar os campos J2ANUMPL e J2ANUMTI do sistema (J2AIDCTS) 1 com os mesmos campos do sistema 2, e assim por diante, lembrando que a comparacao nao pode ser feita com (=) e sim com LIKE.

Exemplo: (ORIGEM (1) LIKE DESTINO (2) OR DESTINO (2) LIKE ORIGEM (1)) AND (ORIGEM (1) LIKE DESTINO (3) OR DESTINO (3) LIKE ORIGEM (1)) AND (ORIGEM (1) LIKE DESTINO (4) OR DESTINO (4) LIKE ORIGEM (1)) e assim por diante.

Nao sei se melhorou....

 

Quanto a utilizacao de function indexes, sinceramente nao consegui visualizar como criar uma para ajudar nesta consulta.

Testei a sua opcao de query e ficou com um custo um pouco maior do que a que utilizo hoje.

 

No aguardo.

[Motta 645]

Teria alguma lei de formação ao menos para a posição dos campos ?

 

Ex: "123456" x "abc123456"

 

Ou seja a posição de uma string na outra teria uma posição fixa, neste caso o Function Index poderia funcionar

 

Realmente sem um indice de busca não existe muita oisa a ser feita.

[jcemb 0]

Teria alguma lei de formação ao menos para a posição dos campos ?

 

Ex: "123456" x "abc123456"

 

Ou seja a posição de uma string na outra teria uma posição fixa, neste caso o Function Index poderia funcionar

 

Realmente sem um indice de busca não existe muita oisa a ser feita.

 

 

Entao, eu meio que ja tinha perdido as esperancas quanto a melhorar a query, por isso comecei a pensar em alguma acao numa proc, tentar desmembrar essa query em procs e com isso melhorar a performance do processo, sem utilizar essa query. Alguma sugestao?

Estava pensando em abrir 10 cursores (1 para a origem e outros 9 para os destinos) e depois fazer a comparacao em loops. O que acha?

[Motta 645]

Tente uma tabela auxiliar que seria algo assim

 

auxiliar

--------

chave_tabela_origem

chave_tabela_destino

 

A sua query faria joins com esta tabela, uma processo batch poderia rodar a noite e povar esta tabela.