Re: [oracle_br] Re: Melhor alternativa para leitura de arquivo e atualiz ação em tabela
Chiappa, decidi seguir em frente com a external table. No fim do processamento eu pego o conteúdo do log e do bad file, mostro no fim do log do concurrent e depois apago esses 2 arquivos. Não sei se você tem experiência com Oracle EBS mas não faria sentido construir uma tela só para isso. Eu já desenhei essa customização para evitar ao máximo objetos extras ao core do produto. Acho que assim ficou bom, vamos ver nos testes. Valeu pelas dicas. Abs -- Eduardo Schurtz 2014-04-17 14:33 GMT-03:00 : > > > Bem, imho a pessoa ** não ** vai ter que "usuário vai ter que analisar o > log do concurrent e depois o log do LOADER/external table", pois os logs > detalhados que são gerados para a external table são em caso de EXCEÇÕES > (ie, o BADFILE eo DISCARDFILE), enquanto o LOGFILE propriamente dito tem > poucas e curtas msgs ...Por exemplo, veja abaixo o logfile de uma query > bem-sucedida (sem exceções) numa external table : > > /tmp$ cat POP_EXT_3688.log > > > LOG file opened at 04/17/14 13:08:54 > > Field Definitions for table POP_EXT >Record format DELIMITED BY NEWLINE >Data in file has same endianness as the platform >Rows with all null fields are accepted > >Fields in Data Source: > > CITYCHAR (255) >Terminated by "," >Trim whitespace same as SQL Loader > COUNTRY CHAR (255) >Terminated by "," >Trim whitespace same as SQL Loader > POPULATION CHAR (255) >Terminated by "," >Trim whitespace same as SQL Loader > > /tmp$ > > ==> é informação técnica INTERNA, absolutamente Não Interessante para o > usuário-final, yep ?? Então imho vc simplesmente teria uma tela customizada > aonde, SE / QUANDO o usuário precisar, ele lê o BADFILE e o DISCARDFILE, > Acho que é isso que o usuário precisaria ter... > > Já sobre o ponto de verificar se o registro que está vindo existe, e > gerar uma mensagem se não existir : não sei se o MERGE aceita, mas veja lá > se vc não poderia simplesmente pedir pro MERGE fazer o INSERT duma string > numa tabela de erros, que depois vc apresentaria na tela de log pro > usuário... Eu ACHO que não daria, mas veja lá... > > Sobre a questão de leitura dos dados e comparação, sim : para evitar o > trabalho de carregar o arquivo-texto pra uma GTT antes de poder acessar, é > o que vc falou, seria um OUTER JOIN, sim: > > for r in (SELECT dadosdesejados FROM tabelareal A, externaltable B > WHERE A.colunachave (+) = B.colunachave > loop > > IF r.colunachave IS NULL then -- não houve match >gera uma mensagem... > > >claro, o PL/SQL sempre trabalha logicamente registro-a-registro mas > OBVIAMENTE vc colocaria os registros lidos e a updatear/inserir num ARRAY, > é isso que faz a cláusula de BULK COLLECT e o FORALL yes ?? Se > REALMENTE vc precisa gerar uma ação espeífica para cada registro > processado, aí a solução de apenas SQL (que sria o MERGE) não atende, aí > toca a aceitar a performance provavelmente inferior e programar em PL/SQL, > sim > > > E o último ponto, sobre a falta de índice na external table - veja, eu > entendo que a. a tabela real vai ser ** muito MAIOR ** que o arquivo-texto > com os dados a carregar E QUE necessariamente os dados TODOS que estarão no > arquivo-texto TEM QUE SER PROCESSADOS do primeiro ao último, Integralmente, > sim ??? Notar que o índice só é positivo para a performance quando vc quer > recuperar UM, ou no máximo uma pequena FRAÇÃO do todos, aí o fato de vc > precisar ler o arquivo-texto TODINHO inviabilizaria o uso de índice, okdoc > ??? Por isso que a Oracle não colocou ainda a opção de indexação em > external tables, tipicamente esses caras são dados a carregar/procesar, TEM > que ser lidos integralmente, não compensaria para a performance. E o > fato da tabela real de destino ser (imagino) MUITO MUITO maior e o fato de > que é nela que vc tem que procurar para ver se o valor lido da external > existe ou não indicaria que o índice útil aí seria é na TABELA REAL, que é > Muito maior E é onde queremos pesquisar apenas uma linha, yep ?? > > []s > > Chiappa > >
Re: [oracle_br] Re: Melhor alternativa para leitura de arquivo e atualiz ação em tabela
Bem, imho a pessoa ** não ** vai ter que "usuário vai ter que analisar o log do concurrent e depois o log do LOADER/external table", pois os logs detalhados que são gerados para a external table são em caso de EXCEÇÕES (ie, o BADFILE eo DISCARDFILE), enquanto o LOGFILE propriamente dito tem poucas e curtas msgs ...Por exemplo, veja abaixo o logfile de uma query bem-sucedida (sem exceções) numa external table : /tmp$ cat POP_EXT_3688.log LOG file opened at 04/17/14 13:08:54 Field Definitions for table POP_EXT Record format DELIMITED BY NEWLINE Data in file has same endianness as the platform Rows with all null fields are accepted Fields in Data Source: CITYCHAR (255) Terminated by "," Trim whitespace same as SQL Loader COUNTRY CHAR (255) Terminated by "," Trim whitespace same as SQL Loader POPULATION CHAR (255) Terminated by "," Trim whitespace same as SQL Loader /tmp$ ==> é informação técnica INTERNA, absolutamente Não Interessante para o usuário-final, yep ?? Então imho vc simplesmente teria uma tela customizada aonde, SE / QUANDO o usuário precisar, ele lê o BADFILE e o DISCARDFILE, Acho que é isso que o usuário precisaria ter... Já sobre o ponto de verificar se o registro que está vindo existe, e gerar uma mensagem se não existir : não sei se o MERGE aceita, mas veja lá se vc não poderia simplesmente pedir pro MERGE fazer o INSERT duma string numa tabela de erros, que depois vc apresentaria na tela de log pro usuário... Eu ACHO que não daria, mas veja lá... Sobre a questão de leitura dos dados e comparação, sim : para evitar o trabalho de carregar o arquivo-texto pra uma GTT antes de poder acessar, é o que vc falou, seria um OUTER JOIN, sim: for r in (SELECT dadosdesejados FROM tabelareal A, externaltable B WHERE A.colunachave (+) = B.colunachave loop IF r.colunachave IS NULL then -- não houve match gera uma mensagem... claro, o PL/SQL sempre trabalha logicamente registro-a-registro mas OBVIAMENTE vc colocaria os registros lidos e a updatear/inserir num ARRAY, é isso que faz a cláusula de BULK COLLECT e o FORALL yes ?? Se REALMENTE vc precisa gerar uma ação espeífica para cada registro processado, aí a solução de apenas SQL (que sria o MERGE) não atende, aí toca a aceitar a performance provavelmente inferior e programar em PL/SQL, sim E o último ponto, sobre a falta de índice na external table - veja, eu entendo que a. a tabela real vai ser ** muito MAIOR ** que o arquivo-texto com os dados a carregar E QUE necessariamente os dados TODOS que estarão no arquivo-texto TEM QUE SER PROCESSADOS do primeiro ao último, Integralmente, sim ??? Notar que o índice só é positivo para a performance quando vc quer recuperar UM, ou no máximo uma pequena FRAÇÃO do todos, aí o fato de vc precisar ler o arquivo-texto TODINHO inviabilizaria o uso de índice, okdoc ??? Por isso que a Oracle não colocou ainda a opção de indexação em external tables, tipicamente esses caras são dados a carregar/procesar, TEM que ser lidos integralmente, não compensaria para a performance. E o fato da tabela real de destino ser (imagino) MUITO MUITO maior e o fato de que é nela que vc tem que procurar para ver se o valor lido da external existe ou não indicaria que o índice útil aí seria é na TABELA REAL, que é Muito maior E é onde queremos pesquisar apenas uma linha, yep ?? []s Chiappa
Re: [oracle_br] Re: Melhor alternativa para leitura de arquivo e atualiz ação em tabela
Então Chiappa, até montei um script aqui pra testar a external table, está funcionando certinho. Mas acho que realmente terei que fazer algo para avaliar registro-a-registro, pra indicar no log do concurrent a situação de cada registro. Eu até poderia continuar usando o external table pra carregar e depois abrir um cursor para analisar cada registro e decidir o que fazer com ele (Mostrar no LOG DO CONCURRENT se o registro tiver problemas, atualizar na tabela destino se estiver tudo OK, etc...)... MAS, pensei agora numa situação desse jeito vou ter 2 LOGs, um próprio do external table que ele vai gerar automaticamente quando ler o arquivo e carrega a ext. table e outro meu no log do concurrent com a minha própria análise (regras de negócio, validação de conteúdo, etc...). Aí acho que o negócio já começa a ficar "feio", entende? O usuário vai ter que analisar o log do concurrent e depois o log do LOADER? Mesmo se jogar o conteúdo do log do loader no log do concurrent, vai ficar complicado de entender... um log depois do outro, misturado... vai ser uma bagunça... Ainda tem o fato de eu ter que verificar se o registro que está vindo no arquivo existe na tabela destino. Se não existir, terei que colocar uma mensagem do tipo: "Funcionário número X ainda não está cadastrado!". Se existir, eu faço as validações e executo o UPDATE. Pra isso, não teria que abrir um cursor SOMENTE na ext. table e ir verificando registro-a-registro se existe ou não na tabela destino? Ou uma query com as duas, usando um outer join, oq viesse nulo na parte da tabela destino, entenderia como "não existente"... e poderia tratar também, sem ter que ficar fazendo select um-a-um... E um terceiro ponto que fiquei na dúvida, o fato da external table não poder ter índice, não poderia ter uma performance ruim? Imaginando uma carga de 300K registros, por exemplo. -- Eduardo Schurtz 2014-04-17 7:24 GMT-03:00 : > > > Então : veja que tanto a EXTERNAL TABLE quanto a operação de MERGE que eu > recomendo como sendo (e na Esmagadora maioria das vezes é MESMO) mais > Rápida são built-ins, nativos DO DATABASE, então por isso se por um lado te > dão a performance TOP (internamente elas são código C , ** compilado ** > dentro do database, e que usa & abusa dos hooks internos do database, > coisas essas inerentemente VEDADAS ao teu código Pl/SQL coitadinho > INTERPRETADO e EXTERNO), por Outro lado são fechados e algo inflexíveis, > fazem o que fazem e cabou Veja em > https://asktom.oracle.com/pls/apex/f?p=100:11:0P11_QUESTION_ID:6618304976523#6623196184466um > pouco sobre as questões de performance, em > https://asktom.oracle.com/pls/apex/f?p=100:11:0P11_QUESTION_ID:150047150034606a > opção de log de erros e em > https://asktom.oracle.com/pls/apex/f?p=100:11:0P11_QUESTION_ID:35615502072484#35632246331433os > detalhes sobre qtdade de registros processados , e decida se essas > coisas te atendem OU se realmente vc quer trocar performance e eficiência > por controle e log detalhado... > SE quiser/precisar optar pela opção menos performática porém mais > controlada de fazer o processamento EXTERNAMENTE ao database, sem ser por > um só comando SQL, AUTOMATICAMENTE vc vai ter que escrever mais código e > VAI ter que se preocupar com limites dos resultados E que fique CLARO, > esses limites (que incluiriam o LIMIT da cláusula de COLLECT, e tamanhos de > arrays) não são GENÉRICOS, eles dependem fundamentalmente do SEU ambiente, > de quanta RAM vc tem livre, da capacidade/poder de processamento... É > TOTALMENTE POR SUA CONTA, portanto, testar no SEU ambiente e ver até onde > vc pode chegar sem queda de performance e sem erros ORA-40xxx de falta de > recursos : os valores de 100 registros coletados por vez que vc vai > encontrar tanto na documentação quanto (principalmente) na maioria dos > links que goolglar e/ou que vou indicar) são simplesmente um valor TÍPICO, > mas nem de longe isso vai ser Ótimo no seu ambiente... > > Eu ** IMAGINO ** que a opção menos ruim de processamento PL/SQL aonde vc > teria a opção de controle detalhado/log detalhado de cada registro seja a > external table sendo joineada num cursor PL/SQL com a tabela a atualizar, > sendo lida com BULK e o UPDATE é em múltiplos registros de uma vez com > FORALL - veja > http://www.morganslibrary.org/reference/plsql/array_processing.html , > http://ksadba.wordpress.com/2008/06/16/updating-millions-of-rows-merge-vs-bulk-collect/, > http://www.oracle-base.com/articles/9i/bulk-binds-and-record-processing-9i.php(e > seus links do 10g) e > http://psoug.org/reference/array_processing.html para refs/exemplos, BEM > COMO os manuais Oracle correspondentes > > REPITO, o que não faz sentido é vc ler 2x o arquivo de texto, sendo uma > vez pra carregar na GTT e Outra vez para processar a GTT - talvez a > exceção seja se vc quiser ter o log detalhado da leitura do arquivo-texto > (digamos, log dos registros inválidos) : como vc aprendeu, o log da > external tabl
Re: [oracle_br] Re: Melhor alternativa para leitura de arquivo e atualiz ação em tabela
Então : veja que tanto a EXTERNAL TABLE quanto a operação de MERGE que eu recomendo como sendo (e na Esmagadora maioria das vezes é MESMO) mais Rápida são built-ins, nativos DO DATABASE, então por isso se por um lado te dão a performance TOP (internamente elas são código C , ** compilado ** dentro do database, e que usa & abusa dos hooks internos do database, coisas essas inerentemente VEDADAS ao teu código Pl/SQL coitadinho INTERPRETADO e EXTERNO), por Outro lado são fechados e algo inflexíveis, fazem o que fazem e cabou Veja em https://asktom.oracle.com/pls/apex/f?p=100:11:0P11_QUESTION_ID:6618304976523#6623196184466 um pouco sobre as questões de performance, em https://asktom.oracle.com/pls/apex/f?p=100:11:0P11_QUESTION_ID:150047150034606 a opção de log de erros e em https://asktom.oracle.com/pls/apex/f?p=100:11:0P11_QUESTION_ID:35615502072484#35632246331433 os detalhes sobre qtdade de registros processados , e decida se essas coisas te atendem OU se realmente vc quer trocar performance e eficiência por controle e log detalhado... SE quiser/precisar optar pela opção menos performática porém mais controlada de fazer o processamento EXTERNAMENTE ao database, sem ser por um só comando SQL, AUTOMATICAMENTE vc vai ter que escrever mais código e VAI ter que se preocupar com limites dos resultados E que fique CLARO, esses limites (que incluiriam o LIMIT da cláusula de COLLECT, e tamanhos de arrays) não são GENÉRICOS, eles dependem fundamentalmente do SEU ambiente, de quanta RAM vc tem livre, da capacidade/poder de processamento... É TOTALMENTE POR SUA CONTA, portanto, testar no SEU ambiente e ver até onde vc pode chegar sem queda de performance e sem erros ORA-40xxx de falta de recursos : os valores de 100 registros coletados por vez que vc vai encontrar tanto na documentação quanto (principalmente) na maioria dos links que goolglar e/ou que vou indicar) são simplesmente um valor TÍPICO, mas nem de longe isso vai ser Ótimo no seu ambiente... Eu ** IMAGINO ** que a opção menos ruim de processamento PL/SQL aonde vc teria a opção de controle detalhado/log detalhado de cada registro seja a external table sendo joineada num cursor PL/SQL com a tabela a atualizar, sendo lida com BULK e o UPDATE é em múltiplos registros de uma vez com FORALL - veja http://www.morganslibrary.org/reference/plsql/array_processing.html , http://ksadba.wordpress.com/2008/06/16/updating-millions-of-rows-merge-vs-bulk-collect/ , http://www.oracle-base.com/articles/9i/bulk-binds-and-record-processing-9i.php (e seus links do 10g) e http://psoug.org/reference/array_processing.html para refs/exemplos, BEM COMO os manuais Oracle correspondentes REPITO, o que não faz sentido é vc ler 2x o arquivo de texto, sendo uma vez pra carregar na GTT e Outra vez para processar a GTT - talvez a exceção seja se vc quiser ter o log detalhado da leitura do arquivo-texto (digamos, log dos registros inválidos) : como vc aprendeu, o log da external table em si é o que é, no formato e na localização que é : CASO realmente precise muito de algo diferente E não seja viável ler/transformar o log da external, não tem jeito é engolir o sapo e a GTT sem tempero, sim ... Aí o BULK/FOR ALL vai usar a GTT ao invés da external... []s Chiappa
Re: [oracle_br] Re: Melhor alternativa para leitura de arquivo e atualiz ação em tabela
Então Chiappa, eu não conhecia não. Já tinha utilizado o SQL LOADER,que é bem parecido com esse recurso da external table, que utiliza o ORACLE_LOADER. Logo que o Alexandre comentou sobre a external table, troquei uma ideia com ele. Realmente é uma alternativa bem interessante, o único porém é a questão do log de saída. Como vou executar esse programa de um concurrent no EBS, o ideal era ter o log todo no log do próprio concurrent, para o usuário poder identificar qualquer problema. Pelo que vi, o external table gera um arquivo de log separado. Eu teria que ter um outro comando para ler esse arquivo de log e colocar na saída do concurrent. Sem contar que não teria como formatar, pois não tenho controle sobre como esse arquivo é gerado. Fazendo pelo processo normal, lendo linha a linha via PL/SQL, eu consigo saber em cada linha o que acontece e posso mostrar da maneira que eu quiser no log. Não sei se fui claro... -- Eduardo Schurtz 2014-04-16 18:44 GMT-03:00 : > > > Blz ? Então, a opção de carregar os dados do arquivo pra GTT (seja como > for, com SQL ou PL/SQL, bulk ou não, etc), simplesmente ** NÃO FAZ SENTIDO > ** frente à opção de EXTERNAL TABLE - não sei se vc a conmhece, mas é uma > feature relativamente antiga que permite que vc use o arquivo-texto > DIRETAMENTE NUM SQL, como se ele fosse uma tabela , SEM a necessidade de > carregar os dados pra dentro do banco, okdoc ?? veja vc, com GTT vc iar > fazer ** DUAS ** (2) leituras completas no arquivo-texto, UMA para carregar > o texto pra dentro da GTT e OUTRA para ir lendo os dados da GTT, com > EXTERNAL TABLE vc simplesmente faria um JOIN (ou mesmo um MERGE - veja > http://www.akadia.com/services/ora_etl.html#The%20MERGE%20Statement para > um exemplo, e o manual de SQL Reference e o de DW Oracle para mais > detalhes) !! Com isso num ÚNICO SQL vc já lê os dados do arquivo-texto e > faz a comparação com os dados da tabela, SEM precisar se preocupar com > limites de arrays, SEM precisar se preocupar com tamanhos de resultsets > (pois COMO SABEMOS um SQL pode manipular Qualquer Quantidade de registros > num database Oracle através do mecanismo de FETCH automático que possui), e > ainda por cima via de regra vc obtém a melhor performance (pois um único > SQL significa menos código Pl/SQL a analisar, um SQL puro pode se > beneficiar das transformações/otimizações que o RDBMS pode fazer, o que um > cursor PL/SQL ** TOTALMNENTE nÃO CONSEGUE **), é tudo de bom... > > []s > > Chiappa > >
