REFRESH GROUP

Refresh group (RefGroup) é uma das milhares de funcionalidades que nosso querido Oracle DB possui.

O RefGroup serve para agrupar materialized views e atualizá-las de maneira única. O que pode facilitar a vida do DBA mas complicar o entendimento do banco por novos administradores.

Alguns problemas decorrentes de RefGroups.
no uso de RefGroups é que:
– O tempo de refresh da MView passa a ser do grupo na qual ela faz parte;
– O Enterprise Manager (versões antigas) não enxerga isto;
– A MView não precisa ter uma JOB associada ( o que dificulta encontrar aonde ela é atualizada );

Neste artigo exibirei como criar um RefGroup e como atualizar as views dele, algo bem simples 🙂

CRIANDO UM REFRESH GROUP

Para criar um RefGroup usaremos a biblioteca DBMS_REFRESH e será necessário termos uma MaterializedView.

CRIANDO UMA MATERIALIZED VIEW

Como o foco de nosso artigo é Refresh Groups, vou criar uma tabela bem simples com uma Materialized View atualizada de 10 em 10 minutos.

Para isto, executei:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”ZHJvcCB0YWJsZSBkYXJ0YW5naGFuLmxvZzsKZHJvcCBtYXRlcmlhbGl6ZWQgdmlldyBkYXJ0YW5naGFuLk1WX0xPRzsKCmNyZWF0ZSB0YWJsZSBkYXJ0YW5naGFuLmxvZyAoCglpZCBudW1iZXIgcHJpbWFyeSBrZXksCglkZXNjciB2YXJjaGFyMig1MTIpCik7CmNyZWF0ZSBtYXRlcmlhbGl6ZWQgdmlldyBsb2cgb24gZGFydGFuZ2hhbi5sb2c7CmNyZWF0ZSBtYXRlcmlhbGl6ZWQgdmlldyBkYXJ0YW5naGFuLk1WX0xPRwpyZWZyZXNoIGZhc3QgbmV4dCBzeXNkYXRlKzEwLzI0LzYwCmFzIHNlbGVjdCBpZCxkZXNjciBmcm9tIGxvZzs=” hover_enabled=”0″]ZHJvcCB0YWJsZSBkYXJ0YW5naGFuLmxvZzsKZHJvcCBtYXRlcmlhbGl6ZWQgdmlldyBkYXJ0YW5naGFuLk1WX0xPRzsKCmNyZWF0ZSB0YWJsZSBkYXJ0YW5naGFuLmxvZyAoCglpZCBudW1iZXIgcHJpbWFyeSBrZXksCglkZXNjciB2YXJjaGFyMig1MTIpCik7CmNyZWF0ZSBtYXRlcmlhbGl6ZWQgdmlldyBsb2cgb24gZGFydGFuZ2hhbi5sb2c7CmNyZWF0ZSBtYXRlcmlhbGl6ZWQgdmlldyBkYXJ0YW5naGFuLk1WX0xPRwpyZWZyZXNoIGZhc3QgbmV4dCBzeXNkYXRlKzEwLzI0LzYwCmFzIHNlbGVjdCBpZCxkZXNjciBmcm9tIGxvZzs=[/et_pb_dmb_code_snippet]

Criando uma materialized view, o Oracle já cria uma Job e um grupo de atualização ( com apenas a MView criada ).
Com nossa materialized view criada, vamos verificar de quanto em quanto tempo ela será atualizada. Para verificar isto, podemos ver as jobs cadastradas em USER_JOBS

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”c2V0IHBhZ2VzaXplIDUwMDsKY29sIGZvcm1hdCB3aGF0IGE0NTsKY29sIGludGVydmFsIGZvcm1hdCBhMzA7CnNlbGVjdCBqb2IsaW50ZXJ2YWwsd2hhdCBmcm9tIHVzZXJfam9iczsKCiAgICAgICBKT0IgSU5URVJWQUwgICAgICAgICAgICAgICAgICAgICAgIFdIQVQKLS0tLS0tLS0tLSAtLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0gLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQogICAgICAgICA0IHN5c2RhdGUrMTAvMjQvNjAgICAgICAgICAgICAgICBkYm1zX3JlZnJlc2gucmVmcmVzaCgnIkRBUlRBTkdIQU4iLiJNVl9MT0ciJyk7″ hover_enabled=”0″]c2V0IHBhZ2VzaXplIDUwMDsKY29sIGZvcm1hdCB3aGF0IGE0NTsKY29sIGludGVydmFsIGZvcm1hdCBhMzA7CnNlbGVjdCBqb2IsaW50ZXJ2YWwsd2hhdCBmcm9tIHVzZXJfam9iczsKCiAgICAgICBKT0IgSU5URVJWQUwgICAgICAgICAgICAgICAgICAgICAgIFdIQVQKLS0tLS0tLS0tLSAtLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0gLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQogICAgICAgICA0IHN5c2RhdGUrMTAvMjQvNjAgICAgICAgICAgICAgICBkYm1zX3JlZnJlc2gucmVmcmVzaCgnIkRBUlRBTkdIQU4iLiJNVl9MT0ciJyk7[/et_pb_dmb_code_snippet]

Como previsto, ao criar nossa Materialized View com a opção NEXT, o banco criou uma job para fazer a atualização.

CRIANDO (FINALMENTE) O REFRESH GROUP

Agora que temos uma tabela e uma materialized view, podemos pensar no nosso Refresh Group.

Para criar um refresh group, usamos:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”YmVnaW4KREJNU19SRUZSRVNILk1BS0UoCm5hbWU9PidjZXJ0YmRfcmVmZ3JvdXAnLApsaXN0PT4nJywKbmV4dF9kYXRlID0+IHN5c2RhdGUsCmludGVydmFsPT4nc3lzZGF0ZSsxLzI0LzYwJywKbGF4PT50cnVlCik7CmVuZDsKLw==” hover_enabled=”0″]YmVnaW4KREJNU19SRUZSRVNILk1BS0UoCm5hbWU9PidjZXJ0YmRfcmVmZ3JvdXAnLApsaXN0PT4nJywKbmV4dF9kYXRlID0+IHN5c2RhdGUsCmludGVydmFsPT4nc3lzZGF0ZSsxLzI0LzYwJywKbGF4PT50cnVlCik7CmVuZDsKLw==[/et_pb_dmb_code_snippet]

* Observe que não é sempre necessário criar uma refresh group. Estamos criando apenas para exemplificar o agrupamento de várias mviews em um só job.

No parâmetro LIST poderíamos ter informado o nome das Materialized Views, mas resolvi deixar para adicionar separadamente para vermos como funciona.

Para vermos os RefGroups cadastrados podemos executar:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”c2VsZWN0ICogZnJvbSB1c2VyX3JlZnJlc2g7″ hover_enabled=”0″]c2VsZWN0ICogZnJvbSB1c2VyX3JlZnJlc2g7[/et_pb_dmb_code_snippet]

e para ver os filhos de cada RefGroup:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”c2VsZWN0ICogZnJvbSB1c2VyX3JlZnJlc2hfY2hpbGRyZW47″ hover_enabled=”0″]c2VsZWN0ICogZnJvbSB1c2VyX3JlZnJlc2hfY2hpbGRyZW47[/et_pb_dmb_code_snippet]

Como ficou no nosso exemplo?

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U1FMPiBzZWxlY3QgKiBmcm9tIHVzZXJfcmVmcmVzaDsKClJPV05FUiAgICAgICAgICAgICAgICAgICAgICAgICBSTkFNRSAgICAgICAgICAgICAgICAgICAgICAgICAgICBSRUZHUk9VUCBJIFAgUiBST0xMQkFDS19TRUcgICAgICAgICAgICAgCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSAtLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0gLS0tLS0tLS0tLSAtIC0gLSAtLS0tLS0tLS0tLS0tLS0tLS0tLS0KREFSVEFOR0hBTiAgICAgICAgICAgICAgICAgICAgIENFUlRCRF9SRUZHUk9VUCAgICAgICAgICAgICAgICAgICAgICAgICA4IE4gWSBOICAgICAgICAgICAgICAgICAgICAgICAgICAKREFSVEFOR0hBTiAgICAgICAgICAgICAgICAgICAgIE1WX0xPRyAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICA3IFkgWSBOICAgICAgICAgICAgICAgICAgICAgICAgICAKClNRTD4gc2VsZWN0ICogZnJvbSB1c2VyX3JlZnJlc2hfY2hpbGRyZW47CgpPV05FUiAgICAgICAgICAgICAgICAgICAgICAgICAgTkFNRSAgICAgICAgICAgICAgICAgICAgICAgICAgIFRZUEUgICAgICAgICAgICAgICAgICAgICAgICAgICBST1dORVIgICAgIAotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0gLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tIC0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSAtLS0tLS0tCkRBUlRBTkdIQU4gICAgICAgICAgICAgICAgICAgICBNVl9MT0cgICAgICAgICAgICAgICAgICAgICAgICAgU05BUFNIT1QgICAgICAgICAgICAgICAgICAgICAgIERBUlRBTkcKClNRTD4=” hover_enabled=”0″]U1FMPiBzZWxlY3QgKiBmcm9tIHVzZXJfcmVmcmVzaDsKClJPV05FUiAgICAgICAgICAgICAgICAgICAgICAgICBSTkFNRSAgICAgICAgICAgICAgICAgICAgICAgICAgICBSRUZHUk9VUCBJIFAgUiBST0xMQkFDS19TRUcgICAgICAgICAgICAgCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSAtLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0gLS0tLS0tLS0tLSAtIC0gLSAtLS0tLS0tLS0tLS0tLS0tLS0tLS0KREFSVEFOR0hBTiAgICAgICAgICAgICAgICAgICAgIENFUlRCRF9SRUZHUk9VUCAgICAgICAgICAgICAgICAgICAgICAgICA4IE4gWSBOICAgICAgICAgICAgICAgICAgICAgICAgICAKREFSVEFOR0hBTiAgICAgICAgICAgICAgICAgICAgIE1WX0xPRyAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICA3IFkgWSBOICAgICAgICAgICAgICAgICAgICAgICAgICAKClNRTD4gc2VsZWN0ICogZnJvbSB1c2VyX3JlZnJlc2hfY2hpbGRyZW47CgpPV05FUiAgICAgICAgICAgICAgICAgICAgICAgICAgTkFNRSAgICAgICAgICAgICAgICAgICAgICAgICAgIFRZUEUgICAgICAgICAgICAgICAgICAgICAgICAgICBST1dORVIgICAgIAotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0gLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tIC0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSAtLS0tLS0tCkRBUlRBTkdIQU4gICAgICAgICAgICAgICAgICAgICBNVl9MT0cgICAgICAgICAgICAgICAgICAgICAgICAgU05BUFNIT1QgICAgICAgICAgICAgICAgICAgICAgIERBUlRBTkcKClNRTD4=[/et_pb_dmb_code_snippet]

* Veja que nosso grupo CERTBD_REFGROUP não possui MViews associadas.

ADICIONANDO MATERIALIZED VIEW A UM REFGROUP

Agora que temos um grupo de atualização ( RefGroup ) que atualiza de minuto em minuto, vamos colocar nossa MView dentro dele e fazer com que ela seja atualizada na mesma forma:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”YmVnaW4KREJNU19SRUZSRVNILkFERCAoCm5hbWU9PidDRVJUQkRfUkVGR1JPVVAnLApsaXN0PT4nZGFydGFuZ2hhbi5NVl9MT0cnLApsYXg9PnRydWUKKTsKZW5kOwov” hover_enabled=”0″]YmVnaW4KREJNU19SRUZSRVNILkFERCAoCm5hbWU9PidDRVJUQkRfUkVGR1JPVVAnLApsaXN0PT4nZGFydGFuZ2hhbi5NVl9MT0cnLApsYXg9PnRydWUKKTsKZW5kOwov[/et_pb_dmb_code_snippet]

E agora, como será que ficou nossos “Refresh Children”?

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U1FMPiBzZWxlY3QgKiBmcm9tIHVzZXJfcmVmcmVzaDsKClJPV05FUiAgICAgICAgICAgICAgICAgICAgICAgICBSTkFNRSAgICAgICAgICAgICAgICAgICAgICAgICAgICBSRUZHUk9VUCBJIFAgUiBST0xMQkFDS19TRUcgICAgICAgICAgICAgCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSAtLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0gLS0tLS0tLS0tLSAtIC0gLSAtLS0tLS0tLS0tLS0tLS0tLS0tCkRBUlRBTkdIQU4gICAgICAgICAgICAgICAgICAgICBDRVJUQkRfUkVGR1JPVVAgICAgICAgICAgICAgICAgICAgICAgICAgOCBOIFkgTiAgICAgICAgICAgICAgICAgICAgICAgICAgCgpTUUw+IHNlbGVjdCBuYW1lLHJuYW1lLHJlZmdyb3VwLGpvYiBmcm9tIHVzZXJfcmVmcmVzaF9jaGlsZHJlbjsKCk5BTUUgICAgICAgICAgICAgICAgICAgICAgICAgICBSTkFNRSAgICAgICAgICAgICAgICAgICAgICAgICAgICBSRUZHUk9VUCAgICAgICAgSk9CCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSAtLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0gLS0tLS0tLS0tLSAtLS0tLS0tLS0tCk1WX0xPRyAgICAgICAgICAgICAgICAgICAgICAgICBDRVJUQkRfUkVGR1JPVVAgICAgICAgICAgICAgICAgICAgICAgICAgOCAgICAgICAgIDEwCgpTUUw+” hover_enabled=”0″]U1FMPiBzZWxlY3QgKiBmcm9tIHVzZXJfcmVmcmVzaDsKClJPV05FUiAgICAgICAgICAgICAgICAgICAgICAgICBSTkFNRSAgICAgICAgICAgICAgICAgICAgICAgICAgICBSRUZHUk9VUCBJIFAgUiBST0xMQkFDS19TRUcgICAgICAgICAgICAgCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSAtLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0gLS0tLS0tLS0tLSAtIC0gLSAtLS0tLS0tLS0tLS0tLS0tLS0tCkRBUlRBTkdIQU4gICAgICAgICAgICAgICAgICAgICBDRVJUQkRfUkVGR1JPVVAgICAgICAgICAgICAgICAgICAgICAgICAgOCBOIFkgTiAgICAgICAgICAgICAgICAgICAgICAgICAgCgpTUUw+IHNlbGVjdCBuYW1lLHJuYW1lLHJlZmdyb3VwLGpvYiBmcm9tIHVzZXJfcmVmcmVzaF9jaGlsZHJlbjsKCk5BTUUgICAgICAgICAgICAgICAgICAgICAgICAgICBSTkFNRSAgICAgICAgICAgICAgICAgICAgICAgICAgICBSRUZHUk9VUCAgICAgICAgSk9CCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSAtLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0gLS0tLS0tLS0tLSAtLS0tLS0tLS0tCk1WX0xPRyAgICAgICAgICAgICAgICAgICAgICAgICBDRVJUQkRfUkVGR1JPVVAgICAgICAgICAgICAgICAgICAgICAgICAgOCAgICAgICAgIDEwCgpTUUw+[/et_pb_dmb_code_snippet]

REMOVENDO UM REFRESH GROUP CHILDREN

Para remover uma Materialized View do RefGroup, usamos a proc SUBTRACT.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”YmVnaW4KREJNU19SRUZSRVNILlNVQlRSQUNUICgKbmFtZT0+J0NFUlRCRF9SRUZHUk9VUCcsCmxpc3Q9PidkYXJ0YW5naGFuLk1WX0xPRycKKTsKZW5kOwov” hover_enabled=”0″]YmVnaW4KREJNU19SRUZSRVNILlNVQlRSQUNUICgKbmFtZT0+J0NFUlRCRF9SRUZHUk9VUCcsCmxpc3Q9PidkYXJ0YW5naGFuLk1WX0xPRycKKTsKZW5kOwov[/et_pb_dmb_code_snippet]

REMOVENDO UM REFRESH GROUP

Muito simples, você precisa apenas do nome do grupo:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”YmVnaW4KREJNU19SRUZSRVNILmRlc3Ryb3koCm5hbWU9PidjZXJ0YmRfcmVmZ3JvdXAnCik7CmVuZDsKLw==” hover_enabled=”0″]YmVnaW4KREJNU19SRUZSRVNILmRlc3Ryb3koCm5hbWU9PidjZXJ0YmRfcmVmZ3JvdXAnCik7CmVuZDsKLw==[/et_pb_dmb_code_snippet]

Para maiores detalhes dos parâmetros do DBMS_REFRESH, por favor, acesse:
http://docs.oracle.com/cd/E11882_01/server.112/e10707/rarrefreshpac.htm

Pessoal, este artigo é realmente muuuiiito pequeno sobre o conteúdo, mas espero que este possa ter ajudado em algum ponto no uso de Refresh Groups.

Caso existe algo incoerente, por favor, me ajudem a corrigir 😉