Data Warehousing - Computação UFCG
Propaganda
Data Warehousing Disciplina: Banco de Dados II Professor: Carlos Eduardo Pires [email protected] Bibliografia KIMBALL, R., ROSS, M., THORNTHWAITE, W., MUNDY, J., BECKER, B. The Data Warehouse Lifecycle Toolkit. Wiley, 2nd Edition, 2008. SILBERSCHATZ, A., KORTH, H., SUDARSHAN, S. Sistema de Banco de Dados. Campus, 5ª Edição, 2006. INMON, W. H. Building the Data Warehouse. Wiley, 4th Edition, 2005. UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 2 Agenda Introdução (Integração de Dados) Data Warehousing: Conceitos e Terminologias Processo de Data Warehousing Modelagem Multidimensional Ferramentas OLAP SQL para Data Warehousing Otimização de DW UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 3 Abordagens para Integração de Dados Integração de Dados Objetivo de um Sistema de Integração de Dados (SID) Fornecer uma interface uniforme para acesso a múltiplas fontes de dados Permite ao usuário especificar o que ele deseja e o sistema determina como e onde a informação será conseguida Estudada no campo da Inteligência Artificial e de Banco de Dados UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 5 Integração de Dados Problemas da Integração de Dados Fontes são compostas por dados muito específicos (granularidade) Dados são armazenados em diferentes esquemas e modelos Dados podem ser não estruturados, semiestruturados ou estruturados Fontes de dados têm diferentes linguagens de consulta UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 6 Integração de Dados Autonomia das Fontes de Dados Continuam a suportar aplicações locais Alterações podem ocorrer tanto nos dados quanto nos esquemas Um SID necessita lidar com as constantes mudanças nas fontes que estão sendo integradas UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 7 Integração de Dados Principais Abordagens Abordagem Virtual (Mediadores) Abordagem Materializada Consulta as fontes em tempo de execução Consulta um repositório com dados materializados Vantagens e desvantagens Dados atuais X Tempo de resposta UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 8 Arquiteturas Clássicas Arquitetura de Mediadores Abordagem virtual Características Domínio específico Mapeamentos Apenas consultas Fontes de dados de diferentes tipos UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 9 Arquiteturas Clássicas Arquitetura de Data Warehouse Abordagem materializada Estratégias de manutenção Rematerialização da visão integrada Manutenção incremental A Arquitetura de Data Warehouse será abordada nesta disciplina UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 10 Data Warehouse Data Warehouse – Definições “Coleção de dados orientada a assunto, integrada, não-volátil e variante no tempo, utilizada para tomada de decisões” W. H. Inmon “Repositório estruturado e corporativo de dados orientados a assunto, variantes no tempo e históricos, usados para recuperação de informações e suporte à decisão. O DW armazena dados atômicos e sumariados” Oracle Corporation UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 12 Data Warehouse – Definições “Uma cópia de dados transacionais estruturada especificamente para consulta e análise” R. Kimball UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 13 Propriedades de um Data Warehouse Integrado Orientado a Assunto Data Warehouse Não-volátil Variante no Tempo UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 14 Orientado a Assunto Os dados são divididos e armazenados por áreas de negócio Aplicações OLTP Data Warehouse Aposentadoria Investimento Seguro Empréstimo Poupança Informações Financeiras dos Clientes UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 15 Integrado Os dados de um determinado assunto são definidos e armazenados apenas uma vez Poupança Contas Empréstimos Aplicações OLTP Cliente Data Warehouse UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 16 Variante no Tempo Os dados são armazenados como uma série de fotografias, cada uma representando um período no tempo Data Warehouse UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 17 Não-Volátil Dados são materializados no DW Operacional Data Warehouse Carga Inserção, Atualização, Remoção e/ou Leitura Leitura UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 18 Alterando os Dados em um Data Warehouse Bancos de Dados Operacionais Banco de Data Warehouse Primeira Carga Atualizar Atualizar Atualizar Eliminar ou Arquivar UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 19 Data Mart Data Warehouse departamental Espelho parcial de um Data Warehouse Oferece melhoria no desempenho Armazena menos dados Desenvolvimento Construído e “povoado” mais rapidamente Satisfação imediata do Cliente UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 20 Data Mart UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 21 Data Mart DW Corporativo Clientes 1998_Vendas 1999_Vendas 2000_Vendas 1998_Garantia 1998_Suporte ... Data Mart Vendas & Marketing Clientes 1998_Vendas 1999_Vendas 2000_Vendas ... Data Mart Garantia & Suporte Clientes 1998_Garantia ... 1998_Suporte ... UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 22 Data Warehouse x Data Mart Propriedade Data Warehouse Data Mart Escopo Empresa Departamento Assuntos Vários Um único assunto Fonte de Dados Várias Poucas Tempo de Implementação Meses a anos Meses UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 23 Movendo Dados de um Data Warehouse para Data Marts Mart Vendas Fonte 1 Fonte 2 Fonte 3 Data Warehouse Mart Finanças Mart Suporte a clientes Desvantagens Vantagens Tempo mais longo de Campos compartilhados desenvolvimento Fonte comum Processamento distribuído UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 24 Movendo Dados de Data Marts para um Data Warehouse Fonte 1 Mart Vendas Fonte 2 Mart Finanças Fonte 3 Mart Suporte a clientes Vantagens Mais simples e rápido Dados específicos de cada departamento Data Warehouse Desvantagens Duplicação de dados Data Marts incompatíveis UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 25 Processo de Data Warehousing Componentes do Processo de Data Warehousing Fontes de Dados Área de Preparação Área de Armazenamento Ferramentas de Acesso Legado Data Warehouse Externa ODS Operacional Data Marts Repositório de Metadados UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 27 Um Sistema de Data Warehousing Dados de Sistemas Operacionais Data Warehouse Vendas OLTP OLTP OLTP Data Marts Dados da Empresa Dados no Ambiente OLAP Produção Contábil Compras UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 28 Principais Profissionais Envolvidos em um Projeto de Data Warehousing Analista de Negócios Administrador de Dados Administrador de Banco de Dados Desenvolvedor de DW Desenvolvedor de Relatórios OLAP Pessoal da própria empresa! UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 29 Coleção de Ferramentas Obtenção de dados Limpeza, integração, ... Consulta, relatório, análise Mineração de dados Monitoração e administração do DW Monitoração do ETL UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 30 Processo de Extração, Transformação e Carga “Extrai dados necessários das fontes a serem integradas, transforma-os e carrega-os no DW” Extraction, Transformation and Load (ETL) Limpeza de Dados Corrige e pré-processa os dados Origem Área de Preparação UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires Destino 31 Transformação de Dados UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 32 Limpeza de Dados Candidato Bairro Candidato Bairro José Boa Viagem José Boa Viagem Maria B. Viagem Maria Boa Viagem Pedro BV Pedro Boa Viagem Mário Boa Viag. Mário Boa Viagem Marta B.V. Marta Boa Viagem Priscila B Viagem Priscila Boa Viagem Adolf Boa Viajem Adolf Boa Viagem ... ... UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 33 Ferramentas de ETL: Oracle Warehouse Builder UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 34 Modelando um Data Warehouse Comparando Ambientes de Modelagem Operacional: OLTP Entidades normalizadas Segue terceira forma normal ou maior Produz um design complexo de BD Armazena dados no nível transacional mais baixo Aumenta o nível de JOIN de tabelas em consultas Estrutura tipicamente estática Analítico: Data Warehouse Entidades desnormalizadas Produz um único design de BD mais facilmente compreensível pelos usuários Armazena dados Nível de transação Nível de sumário Diminui o número de JOINs de tabelas em consultas Estrutura dinâmica UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 36 Análise de Negócio “Conjunto de atividades e técnicas utilizadas para servir como ligação entre partes interessadas no intuito de compreender a estrutura, políticas e operações de uma organização e para recomendar soluções que permitam que a organização alcance suas metas” BABoK (Business Analysis Body of Knowledgement) Como analisar? Ponha-se no lugar de um Gerente de Vendas O que ele gostaria de analisar? UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 37 Modelagem Multidimensional Dados operacionais visualizados sob diversos ângulos Os ângulos são chamados dimensões do negócio Produto Loja Tempo Região Uma das dimensões é “sempre” o Tempo UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 38 Modelagem Multidimensional Outros Exemplos de Dimensões UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 39 Modelagem Multidimensional Atributos de Dimensão São atributos qualitativos que caracterizam os ramos do negócio envolvidos na medida de desempenho de determinado fato Exemplo Dimensão “Produto” Descrição, embalagem, preço, etc. UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 40 Modelagem Multidimensional Granularidade Define o nível de detalhe das dimensões Influencia o tamanho das dimensões Exemplo Dimensão “Loja” País Região Estado Cidade Maior Granularidade Menor Granularidade UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 41 Modelagem Multidimensional Hierarquias entre os atributos das dimensões Úteis para a geração de relatórios Dimensão “Região” Fornecedor Cidade Estado Região Dimensão “Tempo” Dia Semana Mês Trimestre Ano UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 42 Modelagem Multidimensional Associados a cada dimensão existem fatos: Vendas Compras Sinistro Fatos São valores quantitativos referentes ao desempenho de um grupo de dimensões Exemplo Fato “Vendas” (Loja, Produto e Tempo) Quantidade, lucro, valor, etc. UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 43 Processamento OLAP OLAP (On-line Analytical Processing) Aplicado sobre estruturas dimensionais O termos “On-line” significa que os resumos solicitados são obtidos rapidamente Difere substancialmente daquele utilizado por aplicações do nível operacional (OLTP) UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 44 Processamento OLAP x OLTP Propriedade OLTP OLAP Tempo de Resposta Milisegundos para segundos Segundos para horas Operações DML Leitura Natureza dos dados 30 – 60 dias Snapshots no tempo Organização dos Dados Aplicação Assunto, tempo Tamanho Pequeno para grande Fontes de Dados Operacional, Interna Grande para Muito Grande Operacional, Interna, Externa Atividades Processos Análise UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 45 Modelagem Multidimensional Principais vertentes para Modelagem Multidimensional Multidimensional OLAP (MOLAP) Relational OLAP (ROLAP) Hybrid OLAP (HOLAP) Outras variações Web-based OLAP (WOLAP) Desktop OLAP (DOLAP) Real-Time OLAP (RTOLAP) UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 46 MOLAP (Multidimensional OLAP) Baseia-se nos Bancos de Dados Multidimensionais Estrutura utilizada: Cubo Multidimensional Cada aresta representa uma dimensão do negócio As células do cubo são preenchidas com valores quantitativos (medidas ou fatos) Data Warehouse ou Data Mart Cubo Armazena cópia da tabela fato e dimensões Armazena agregações SGBD Dados MOLAP Agregações MOLAP UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 47 MOLAP (Multidimensional OLAP) PRODUTO Qual o total de vendas de 'Meias' do 'Cliente 1' em '2001'? TEMPO 1999 2000 Vendas 2001 Cliente 2 2002 Cliente 1 Mais informações: http://download.oracle.com/docs/cd/E10530_01/doc/epm.931/html_esb_dbag/frameset.htm?dinconc.htm UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 48 ROLAP (Relational OLAP) Tabelas relacionais simulam o cubo multidimensional O esquema proposto concentra numa tabela as medidas ou fatos do negócio (tabela de fatos) Os componentes das dimensões são armazenados em outras tabelas (tabelas de dimensão) Data Warehouse ou Data Mart Dados ROLAP Agregações ROLAP SGBD Dados-base são mantidos no SGBD-fonte Agregações são armazenadas em tabelas relacionais Estrutura totalmente relacional UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 49 HOLAP (Hybrid OLAP) Data Warehouse ou Data Mart Dados-base mantidos no BD fonte Cubo Agregações calculadas e armazenadas no cubo Dados ROLAP SGBD Agregações MOLAP UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 50 MOLAP vs. ROLAP vs. HOLAP Armazenamento MOLAP HOLAP ROLAP Dados de base Cubo Tabela Relacional Tabela Relacional Agregações Cubo Cubo Tabela Relacional MOLAP HOLAP ROLAP Imediato Mais rápido Rápido Consumo em disco Alto Médio Baixo Manutenção do cubo Alto Médio Baixo Perspectiva do Cliente Desempenho de consulta UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 51 Modelo Estrela (“Star Schema”) Dimensão Produto Dimensão Loja Produto_id Descrição,... Loja_id Cidade,... Fatos “Vendas” Produto_id Loja_id Tempo_id Cliente_id Valor_vendas Total_vendas,... Tabela de Fatos Dimensão Tempo Tempo_id Dia Mês Ano,... Dimensões Desnormalizadas Dimensão Cliente Cliente_id Nome,... UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 52 Modelo Estrela (“Star Schema”) Tabelas de Dimensão Geográfica Dimensão Tabela-Fato Geográfica Produto Medidas Tempo Und. Produto $ Fatos Tempo UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 53 Modelo Estrela (“Star Schema”) Dim_Funcionario Chave_Funcionario Codigo_Funcionario . . . Dimension Table Dim_Tempo Chave_Tempo Data Tabela Fato Fato_Vendas Dim_Produto Chave_Produto Codigo_Produto Chave_Tempo Chave_Funcionario Chave_Produto Chave_Cliente Chave_Entregador . . . . . . Qtd-Prod Valor-Total Dim_Entregador Chave_Entreegador Codigo_Entregador . . . …. . . Dim_Cliente Chave_Cliente Codigo_Cliente . . . UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 54 Modelo Estrela (“Star Schema”) product prodId name price sale orderId date custId prodId storeId qty amt customer custId name address city store storeId city UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 55 Modelo Estrela (“Star Schema”) product prodId p1 p2 name price bolt 10 nut 5 sale oderId date o100 1/7/97 o102 2/7/97 105 3/8/97 customer custId 53 81 111 custId 53 53 111 name joe fred sally prodId p1 p2 p1 storeId c1 c1 c3 address 10 main 12 main 80 willow store storeId c1 c2 c3 qty 1 2 5 amt 12 11 50 city nyc sfo la city sfo sfo la UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 56 Exercício de Sala A partir do Modelo Conceitual do Projeto da Disciplina, tente montar um esquema estrela Mostrar Fato, Dimensões e Relacionamentos, além dos atributos UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 57 Modelo Flocos de Neve (“Snowflakes”) Tabela-Dimensão Principal Fato_Vendas Dim_Produto Chave_Tempo Chave_Funcionario Chave_Produto Chave_Cliente Chave_Entregador Chave_Produto RequiredDate CodigoMarca . . . Nome Tamanho Tabelas-Dimensão Secundárias MarcaProduto CodigoMarca CodigoCategoria Categoria CodigoCategoria Nome UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 58 Modelo Flocos de Neve (“Snowflakes”) ESTADO CATEGORIA Dimensões Normalizadas SUBCATEGORIA CIDADE PRODUTO LOJA Vendas Custo TEMPO CLIENTE UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 59 Modelo Flocos de Neve (“Snowflakes”) Hierarquia entre Dimensões sType store store storeId s5 s7 s9 city cityId sfo sfo la tId t1 t2 t1 mgr joe fred nancy snowflake schema constellations region sType tId t1 t2 city size small large cityId pop sfo 1M la 5M location downtown suburbs regId north south region regId name north cold region south warm region UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 60 Escolhendo uma Modelagem Multidimensional Clareza Número de tabelas Complexidade de consultas Desempenho de consulta Star Schema Snowflake + fácil + difícil < > + simples + complexo + rápido + lento UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 61 Exercício de Sala Montar um esquema snowflakes referente ao fato Vendas Responda a seguinte consulta no esquema snowflakes: Quantidade de vendas de carros da marca W, feitas ao cliente X, no ano de Z UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 62 Escolhendo a Granularidade Determinar requisitos dos dados Escolher o nível mais baixo de detalhe Requer espaço em disco Envolve maior tempo de processamento Provê capacidade detalhada de análise de dados Adaptar medidas à granularidade estabelecida Considerações de projeto Usar medidas aditivas e numéricas UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 63 Definindo Características de Dimensões Aplicar características às tabelas de dimensão Definir PRIMARY KEY Incluir colunas altamente correlacionadas e descritivas Projetando para Usabilidade e Extensibilidade Minimizar ou evitar uso de códigos e abreviações Criar colunas úteis para níveis de agregação Evitar valores nulos ou em branco Tentar minimizar o número de registros que mudam ao longo do tempo UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 64 Identificando Hierarquias de Dimensões Hierarquia Consolidada Hierarquia em Separado Local da Loja Continente Local da Loja Continente País Região Cidade Loja Continente País País Região Região Cidade Cidade Loja Loja 01 UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 65 Dicas Importantes Levar a PK original da tabela fonte para a dimensão Carga incremental Durante a carga, carregar as dimensões primeiramente Dimensões são bem menores que os fatos Vale a pena desnormalizar UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 66 Terminologia Local Atlanta Uvas Denver Membro Detroit Produto Cerejas Dimensão Melões Célula Maçãs Pêras Dia 1 Dia 2 Jan. 1998 ... Fev.. ... 1999 ... Q1 Níveis Q2 Q3 Tempo Q4 Tempo Início Fim Trimestre 1 1 deJulho 30 de Setembro Trimestre 2 1 de Outubro 31 de Dezembro 1 de Janeiro 31 de Março 1 de Abril 30 de Junho Trimestre 3 Propriedades Trimestre 4 UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 67 Operações no Cubo Local Atlanta Uvas Denver Detroit Produto Cerejas Melões Sales Sales Maçãs Peras Pêras Q1 Q2 Q3 Tempo Q4 UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 68 Dimensões Compartilhadas Dimensão 1 Dimensão 3 Fatos 1 Dimensão 2 Dimensão 5 Fatos 2 Dimensão 4 UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires Dimensão 6 69 Fatos & Fatos? Dimensão 1 Dimensão 5 Fatos 2 Fatos 1 Dimensão 2 Dimensão 6 UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 70 Tamanho de um DW Fato: Vendas Data WarehousingUFCG/CEEI/DSC - Prof. Carlos- Banco Eduardo Santos Pires de Dados II - Prof. Carlos Eduardo Pires 71 71 Tamanho de um DW É medido pelo tamanho da Tabela de Fatos Em geral, o tamanho das dimensões é desprezado Dimensão Tempo: 2 anos x 365 dias = 730 dias (linhas) Dimensão Loja: 300 lojas Dimensão Produto: 30.000 produtos Data WarehousingUFCG/CEEI/DSC - Prof. Carlos- Banco Eduardo Santos Pires de Dados II - Prof. Carlos Eduardo Pires 72 72 Tamanho de um DW Dimensão Promoção: cada item vendido está associado a uma única promoção Número de registros da tabela de fatos (média de 3.000 produtos vendidos ao dia em cada loja): 730 (dias) x 300 (lojas) x 3000 (produtos/dia) x 1 (promoção)= 657 milhões de registros Data WarehousingUFCG/CEEI/DSC - Prof. Carlos- Banco Eduardo Santos Pires de Dados II - Prof. Carlos Eduardo Pires 73 73 Tamanho de um DW Número de campos chaves = 4 Número de campos fatos = 4 Total de campos = 8 (de 4 bytes, cada um) Tamanho da Tabela de Fatos 657.000.000 x 8 x 4 = 21GB Data WarehousingUFCG/CEEI/DSC - Prof. Carlos- Banco Eduardo Santos Pires de Dados II - Prof. Carlos Eduardo Pires 74 74 Ferramentas OLAP Ferramentas OLAP CAMADA DE “FRONT-END” Ferramentas de Apresentação Executivo Ferramentas de Apresentação Ferramentas OLAP Ferramentas de Mineração de Dados UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires DW BD 76 Ferramentas OLAP Admitem análise interativa de informações resumidas Fornecem ao usuário a visualização dos dados sob diferentes ângulos Comportam as necessidades da atividade de análise Características de um Relatório OLAP Séries temporais Comparações Ajuda à identificação de anomalias ou exceções Operações: Drill Up, Drill Down, Slicing/Dicing UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 77 Exemplo de Relatório OLAP Produto Região Vendas no Mês Pasta Colgate Pasta Colgate Pasta Colgate Total Sul Sudeste Nordeste 110 179 55 344 Comparação com o Mês Anterior **12% -3% 5% **6% Data WarehousingUFCG/CEEI/DSC - Prof. Carlos- Banco Eduardo Santos Pires de Dados II - Prof. Carlos Eduardo Pires 78 78 Exemplo de Relatório OLAP Produto Região Tamanho Vendas no Mês Colgate Colgate Colgate Colgate Colgate Colgate Colgate Colgate Colgate Colgate Colgate Colgate Total Sul Sul Sul Total Sudeste Sudeste Sudeste Total Nordeste Nordeste Nordeste Total A B C 34 36 40 110 63 60 56 179 19 17 19 55 344 A B C A B C Data WarehousingUFCG/CEEI/DSC - Prof. Carlos- Banco Eduardo Santos Pires de Dados II - Prof. Carlos Eduardo Pires Comp. Com o Mês Anterior **10% **13% **11% **12% -2.8% -3.1% -2.9% -3% 5% 4% 6% 5% 6% 79 79 Exemplo de Relatório OLAP Produto Região Equipe de Vendas Vendas no Mês Colgate Colgate Colgate Colgate Colgate Colgate Colgate Colgate Colgate Colgate Colgate Colgate Total Sul Sul Sul P. Alegre Curitiba Florianóp. Sudeste Sudeste Sudeste São Paulo Rio Belo Horiz. Nordeste Nordeste Nordeste Salvador Fortaleza Recife 52 28 30 110 93 75 11 179 21 18 16 55 344 Comp. Com o Mês Anterior **21% 5% 6% **12% 4% 5% -15% -3% 5% 4% 6% 5% 6% Data WarehousingUFCG/CEEI/DSC - Prof. Carlos- Banco Eduardo Santos Pires de Dados II - Prof. Carlos Eduardo Pires 80 Ferramentas OLAP: Oracle Discoverer TABULAÇÃO CRUZADA UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 81 Tabela Dinâmica no Excel É uma tabela interativa que resume uma grande quantidade de dados rapidamente, ou os combina de tabelas diferentes É possível girar suas linhas e colunas para ver resumos diferentes dos dados de origem, filtrar os dados exibindo páginas diferentes ou exibir os detalhes das áreas de interesse UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 82 Tabela Dinâmica no Excel Exemplo: AULAPED.xls (http://www.virtual.epm.br/material/tis/currmed/tab_dinamica/tabdin.html) Selecione a área compreendida entre as células A1 e H1612 Menu Dados "Relatório da Tabela Dinâmica" Onde estão os dados para análise? BD ou Lista do Excel Intervalos de dados a serem usados? $A$1:$H$1612 Layout UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 83 SQL para OLAP Extensões SQL para Ferramentas OLAP Comando Merge Operador ROLLUP Produz um conjunto de resultados que contém as linhas agrupadas e os valores de subtotais Operador CUBE A partir de uma tabela origem, insere ou atualiza linhas em uma tabela destino Produz um conjunto de resultados que contém as linhas de ROLLUP e as linhas de tabulação cruzada Operador Grouping Sets Extensão da cláusula GROUP BY Possibilita explicitar os agrupamentos desejados UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 85 Merge Sintaxe: MERGE <hint> INTO <table_name> USING <table, view or query> ON (<condition>) WHEN MATCHED THEN <update_clause> WHEN NOT MATCHED THEN <insert_clause> [LOG ERRORS <log_errors_clause> <reject limit <integer | unlimited>]; UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 86 Merge – Exemplo 01 INSERT XOR UPDATE ALUNO Origem ALUNO2 Destino UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 87 Merge – Exemplo 01 CREATE TABLE aluno ( codigo NUMBER PRIMARY KEY, nome VARCHAR2(10), curso VARCHAR2(10), telefone VARCHAR2(20)); INSERT INTO aluno VALUES (1,'JOSE','CC','88847654'); INSERT INTO aluno VALUES (2,'MARIA','EE','89887112'); INSERT INTO aluno VALUES (3,'PAULO','EM','99817638'); CREATE TABLE aluno2 ( id NUMBER PRIMARY KEY, nome VARCHAR(10), curso VARCHAR2(10), telefone VARCHAR2(20), codigo NUMBER); UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 88 Merge – Exemplo 01 CREATE SEQUENCE seq_aluno; CREATE OR REPLACE PROCEDURE atualiza IS BEGIN MERGE INTO aluno2 a2 USING aluno a ON (a.codigo = a2.codigo) WHEN MATCHED THEN UPDATE SET a2.curso = a.curso, a2.telefone = a.telefone WHEN NOT MATCHED THEN INSERT (a2.id, a2.nome, a2.curso, a2.telefone, a2.codigo) (seq_aluno.nextval, a.nome, a.curso, a.telefone, a.codigo); COMMIT; END; VALUES EXECUTE atualiza; SELECT * FROM aluno2; UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 89 Merge – Exemplo 01 INSERT INTO aluno VALUES (4,'ZILDA','CC','81318630'); COMMIT; EXECUTE atualiza SELECT * FROM aluno2; UPDATE aluno SET curso = 'CC' WHERE codigo = 2; UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 90 Merge – Exemplo 02 CREATE TABLE employee ( employee_id NUMBER(5), first_name VARCHAR2(20), last_name VARCHAR2(20), dept_no NUMBER(2), salary NUMBER(10)); INSERT INTO employee VALUES (1,'Dan', 'Morgan',10,100000); INSERT INTO employee VALUES (2,'Helen','Lofstr',20,100000); INSERT INTO employee VALUES (3,'Akiko','Toyota',20,50000); INSERT INTO employee VALUES (4,'Jackie','Stough',20,40000); INSERT INTO employee VALUES (5,'Richard','Foote',20,70000); INSERT INTO employee VALUES (6,'Joe', 'Johnson',20,30000); INSERT INTO employee VALUES (7,'Clark', 'Urling',20,90000); UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 91 Merge – Exemplo 02 CREATE TABLE bonuses ( employee_id NUMBER, bonus NUMBER DEFAULT 100); INSERT INTO bonuses (employee_id) VALUES (1); INSERT INTO bonuses (employee_id) VALUES (2); INSERT INTO bonuses (employee_id) VALUES (4); INSERT INTO bonuses (employee_id) VALUES (6); INSERT INTO bonuses (employee_id) VALUES (7); COMMIT; SELECT * FROM employee; SELECT * FROM bonuses; UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 92 Merge – Exemplo 02 subconjunto de employee MERGE INTO bonuses b USING ( SELECTemployee_id, salary, dept_no FROM employee WHERE dept_no = 20) e ON (b.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET b.bonus = e.salary * 0.1 WHEN NOT MATCHED THEN INSERT (b.employee_id, b.bonus) VALUES (e.employee_id, e.salary * 0.05); UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 93 Cubo 2D GROUP BY storeId, prodId Fact table: sale prodId storeId p1 c1 p2 c1 p1 c3 p2 c2 Multi-dimensional cube: amt 12 11 50 8 p1 p2 c1 12 11 c2 c3 50 8 dimensions = 2 UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 94 Cubo 3D GROUP BY storeId, prodId, date Fact table: sale prodId p1 p2 p1 p2 p1 p1 Multi-dimensional cube: storeId c1 c1 c3 c2 c1 c2 date 1 1 1 1 2 2 amt 12 11 50 8 44 4 day 2 day 1 p1 p2 c1 p1 12 p2 11 c1 44 c2 4 c2 c3 c3 50 8 dimensions = 3 UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 95 Agregação • Soma de quantidade (amt) em um dia (dia 1) • Em SQL: SELECT SUM(amt) FROM sale WHERE date = 1; sale prodId storeId p1 c1 p2 c1 p1 c3 p2 c2 p1 c1 p1 c2 date 1 1 1 1 2 2 amt 12 11 50 8 44 4 81 UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 96 Agregação • Soma quantidade (amt) por dia • Em SQL: SELECT date, SUM(amt) FROM sale GROUP BY date; sale prodId storeId p1 c1 p2 c1 p1 c3 p2 c2 p1 c1 p1 c2 date 1 1 1 1 2 2 amt 12 11 50 8 44 4 ans date 1 2 UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires sum 81 48 97 Agregação • Soma quantidade (amt) por dia e produto • Em SQL: SELECT date, prodId, SUM(amt) FROM sale GROUP BY date, prodId sale prodId storeId p1 c1 p2 c1 p1 c3 p2 c2 p1 c1 p1 c2 date 1 1 1 1 2 2 amt 12 11 50 8 44 4 sale prodId p1 p2 p1 date 1 1 2 amt 62 19 48 Desconsidera storeId rollup drill-down UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 98 Agregação Operadores Cláusula “HAVING” SUM, COUNT, MAX, MIN, AVG Eliminar determinados grupos Uso da hierarquia entre dimensões Média de vendas por região (região está dentro de loja (“store”)) Maior venda por mês (mês está dentro de ano) UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 99 Agregação no Cubo GROUP BY storeId, prodId, date day 2 day 1 p1 p2 c1 p1 12 p2 11 c1 44 c2 4 c2 c3 ... c3 50 GROUP BY storeId 8 GROUP BY storeId, prodId p1 p2 c1 56 11 c2 4 8 rollup drill-down c3 50 sum c1 67 c2 12 c3 50 129 p1 p2 sum 110 19 GROUP BY prodId UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 100 Operadores do Cubo day 2 day 1 p1 p2 c1 p1 12 p2 11 p1 p2 c1 56 11 c1 44 c2 4 c2 c3 ... c3 50 sale(c1,*,*) 8 c2 4 8 c3 50 sale(c2,p2,*) sum c1 67 c2 12 c3 50 129 p1 p2 sum 110 19 UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires sale(*,*,*) 101 Operadores do Cubo c2 4 8 c312 p1 p2 c1 * 12 p1 p2 c1* 44 c1 56 11 c267 4 c2 44 c3 4 50 11 23 8 8 50 * 62 19 81 * day 2 day 1 p1 p2 * c3 50 * 50 48 * 110 19 129 48 UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires sale(*,p2,*) 102 Extensões SQL para Ferramentas OLAP Cláusula GROUP BY tradicional SELECT department_id, job_id, AVG(salary) FROM employees GROUP BY department_id, job_id UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 103 Operador ROLLUP Total de Combinações: n + 1 SELECT FROM WHERE GROUP BY department_id, job_id, SUM(salary) employees department_id < 60 ROLLUP(department_id, job_id); 1 2 3 UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 104 Operador CUBE Total de Combinações: 2n SELECT FROM WHERE GROUP BY department_id, job_id, SUM(salary) employees department_id < 60 CUBE (department_id, job_id) ; 1 2 3 4 UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 105 Grouping Sets SELECT department_id, job_id, manager_id,avg(salary) FROM employees GROUP BY GROUPING SETS ((department_id,job_id), (job_id,manager_id),()); 1 … 2 UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 106 Grouping Sets SELECT department_id, job_id, manager_id,avg(salary) FROM employees WHERE salary > 12000 GROUP BY GROUPING SETS ((department_id,job_id), (job_id,manager_id),()) DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY) ------------- ---------- ---------- ----------20 MK_MAN 13000 80 SA_MAN 13750 90 AD_VP 17000 90 AD_PRES 24000 AD_VP 100 17000 MK_MAN 100 13000 SA_MAN 100 13750 AD_PRES 24000 16416,6667 UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 107 Otimizações Visão (Virtual) Crie uma visão, EMPVU80, que contenha detalhes dos funcionários do departamento 80 CREATE VIEW empvu80 AS SELECT employee_id, last_name, salary FROM employees WHERE department_id = 80; View created. UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 109 Visões (Virtual) Servidor de Banco de Dados Aplicação SELECT * FROM USER_VIEWS EMPVU80 empvu80; SELECT employee_id, last_name, salary FROM employees WHERE department_id=80; EMPLOYEES UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 110 Visões Materializadas Armazenam fisicamente os resultados de uma consulta Podem ser atualizadas periodicamente Ajudam a aumentar a velocidade de resposta das consultas que envolvem funções de agregação ou junções entre tabelas Usadas em soluções de Data Warehousing e Replicação de Dados No SGBD Oracle, eram chamadas de snapshots UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 111 Visões Materializadas CREATE MATERIALIZED VIEW relatorio_cliente REFRESH COMPLETE ENABLE QUERY REWRITE AS SELECT c.nome_cliente, SUM(f.valor) AS total FROM fatos_vendas f, dim_cliente c WHERE f.cliente_id = c.cliente_id GROUP BY c.nome_cliente; UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 112 Visões Materializadas – Exemplo 1 CREATE TABLE products (id_product NUMBER PRIMARY KEY, nm_product VARCHAR2(40), ds_product VARCHAR2(200)); CREATE TABLE sales (id_sales NUMBER, id_product NUMBER, amount NUMBER, value NUMBER, PRIMARY KEY (id_sales, id_product)); INSERT INTO products VALUES (1,'NESCAU',null); INSERT INTO products VALUES (2,'AGUA MINERAL',null); INSERT INTO products VALUES (3,'DESODORANTE',null); INSERT INTO sales VALUES (101,1,20,450); INSERT INTO sales VALUES (100,1,10,225); INSERT INTO sales VALUES (100,2,5,30); INSERT INTO sales VALUES (100,3,30,160); INSERT INTO sales VALUES (102,2,2,10); COMMIT; UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 113 Visões Materializadas – Exemplo 1 CREATE MATERIALIZED VIEW product_sales_mv BUILD IMMEDIATE REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS SELECT p.nm_product, SUM(s.value) FROM products p, sales s WHERE p.id_product = s.id_product GROUP BY nm_product; SELECT * FROM product_sales_mv; NM_PRODUCT SUM(S.VALUE) ---------------------------- -------------------AGUA MINERAL 40 DESODORANTE 160 NESCAU 675 UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 114 Visões Materializadas – Exemplo 1 INSERT INTO sales VALUES (103,2,3,14); SELECT * FROM product_sales_mv; NM_PRODUCT SUM(S.VALUE) ------------------------------ -------------------AGUA MINERAL 40 DESODORANTE 160 Mesmo resultado anterior NESCAU 675 COMMIT; SELECT * FROM product_sales_mv; Provoca a rematerialização da visão NM_PRODUCT SUM(S.VALUE) ------------------------------- --------------------AGUA MINERAL 54 Resultado atualizado após o COMMIT DESODORANTE 160 NESCAU 675 UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 115 Visões Materializadas – Exemplo 2 CONN hr/hr @d:\oracle\ora92\rdbms\admin\utlxplan.sql SET autotrace traceonly CREATE MATERIALIZED VIEW dept_emp REFRESH COMPLETE ENABLE QUERY REWRITE AS SELECT d.department_name, SUM(e.salary) AS total FROM departments d, employees e WHERE d.department_id = e.department_id GROUP BY d.department_name; UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 116 Visões Materializadas – Exemplo 2 ALTER SESSION set query_rewrite_enabled = true; SELECT FROM WHERE GROUP BY 11 linhas selecionadas. Plano de Execução ---------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=327 Bytes=9810) 1 0 TABLE ACCESS (FULL) OF 'DEPT_EMP' (Cost=2 Card=327 Bytes=9810) d.department_name, SUM(e.salary) AS total departments d, employees e d.department_id = e.department_id d.department_name; UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 117 Visões Materializadas Vantagens Útil para sumarização, cálculos pré-computados e replicação de dados Acesso mais rápido para consultas envolvendo junções complexas Transparente para usuários finais Podem ser adicionadas ou eliminadas sem invalidar código SQL Desvantagens Custos de desempenho (rematerialização) Custos de armazenamento UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 118 Particionamento de Tabelas Tabelas Particionadas Decomposição de grandes tabelas em pedaços menores chamados partições Tipos de Particionamento Faixa, Lista, Hash CREATE TABLE employee_r ( Particionamento por empno NUMBER(10) PRIMARY KEY, name VARCHAR2(40), Faixa de Valores deptno NUMBER(2)) PARTITION BY RANGE (deptno) (PARTITION P1 VALUES LESS THAN (11) TABLESPACE p1_ts, PARTITION P2 VALUES LESS THAN (21) TABLESPACE p2_ts, PARTITION P3 VALUES LESS THAN (31) TABLESPACE p3_ts, PARTITION P4 VALUES LESS THAN (MAXVALUE) TABLESPACE p4_ts); UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 119 Particionamento de Tabelas CREATE TABLE cliente ( codigo NUMBER(5) NOT NULL, Particionamento por nome VARCHAR2(100), Lista de Valores estado CHAR(2)) TABLESPACE users PARTITION BY LIST (estado) (PARTITION p_regional_sudeste VALUES ('SP', 'RJ', 'MG', 'ES') TABLESPACE users, PARTITION p_regional_nordeste VALUES ('BA', 'PE', 'PB', 'RN') TABLESPACE users); UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 120 Particionamento de Tabelas BEGIN FOR i in 1..1000 LOOP IF MOD(i,2) = 0 THEN INSERT INTO cliente VALUES (i,'AAAAAA','SP'); ELSE INSERT INTO cliente VALUES (i,'BBBBBB','PB'); END IF; END LOOP; COMMIT; END; SELECT FROM WHERE table_name, partition_name, high_value, num_rows user_tab_partitions able_name = 'CLIENTE' TABLE_NAME --------------CLIENTE CLIENTE PARTITION_NAME ------------------------P_REGIONAL_SUDESTE P_REGIONAL_NORDESTE HIGH_VALUE ------------------------'SP', 'RJ', 'MG', 'ES' 'BA', 'PE', 'PB', 'RN' NUM_ROWS ---------500 500 INSERT INTO cliente VALUES (1001, 'CCCCCC', 'RS'); ERRO na linha 1: ORA-14400: chave de partição inserida não está mapeada para partição alguma UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 121 Particionamento de Tabelas Vantagens Acelera o desempenho Aumenta a disponibilidade Acesso 24x7 a informações críticas Melhora a capacidade de gerenciamento Baixa o tempo de consulta Gerencia porções menores de dados Desvantagens Consultas devem usar os mesmos critérios do particionamento Partições podem ter mais dados do que outras UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 122 Particionamento de Tabelas Exemplo CREATE TABLE exemplo (id NUMBER, nome VARCHAR2(30)); CREATE OR REPLACE PROCEDURE carrega IS BEGIN FOR i in 1..1000000 LOOP INSERT INTO exemplo VALUES (i,'AAAA'||i); END LOOP; COMMIT; END; / EXEC carrega UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 123 Particionamento de Tabelas Exemplo @d:\oracle\ora92\rdbms\admin\utlxplan.sql SET autotrace traceonly SQL> SELECT * from exemplo where id = 1001; ID NOME ---------- -----------------------------1001 AAAA1001 Plano de Execução -------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'EXEMPLO' UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 124 Particionamento de Tabelas Exemplo DROP TABLE exemplo; CREATE TABLE exemplo ( id NUMBER, name VARCHAR2(30)) PARTITION BY RANGE (id) (PARTITION P1 VALUES LESS THAN (250000) TABLESPACE users, PARTITION P2 VALUES LESS THAN (500000) TABLESPACE users, PARTITION P3 VALUES LESS THAN (750000) TABLESPACE users, PARTITION P4 VALUES LESS THAN (MAXVALUE) TABLESPACE users); UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 125 Particionamento de Tabelas Exemplo EXEC carrega SELECT * FROM exemplo WHERE id BETWEEN 1 AND 250000; 250000 linhas selecionadas. Plano de Execução ---------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=145 Card=305 Bytes=9150) 1 0 PARTITION RANGE (ITERATOR) 2 1 TABLE ACCESS (FULL) OF 'EXEMPLO' (Cost=145 Card=305 Bytes=9150) UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 126 Indexação Índices de Bitmap 18 19 20 23 20 21 22 1 1 0 1 1 0 0 0 0 23 25 26 age index bit maps 0 0 1 0 0 0 1 0 1 1 id 1 2 3 4 5 6 7 8 name age joe 20 fred 20 sally 21 nancy 20 tom 20 pat 25 dave 21 jeff 26 ... data records UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 127 Indexação Índices de Bitmap Considere a seguinte consulta: Recupere as pessoas com idade = 20 e nome = “fred” Mapa de bits para idade = 20: 1101100000 Mapa de bits para nome = “fred”: 0100000001 A resposta é a interseção: 010000000000 Deve ser usado Em tabelas com milhões de linhas e as colunas chave têm poucos valores distintos Quando tem pouco update das colunas chave UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 128 Indexação Criando um Índice de Bitmap CREATE BITMAP INDEX emp_est_civil_idx ON empregado(estado_civil) TABLESPACE indx; UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 129 Índices de Bitmap – Exemplo CREATE TABLE paciente ( codigo NUMBER PRIMARY KEY, nome VARCHAR2(40), estcivil VARCHAR2(10), datanasc DATE, sexo CHAR(1)); BEGIN FOR i in 1..10000 LOOP IF MOD(i,2) = 0 THEN INSERT INTO paciente VALUES (i,'AAAAAA', 'CASADO',SYSDATE-i,'F'); ELSE INSERT INTO paciente VALUES (i,'OOOOO', 'SOLTEIRO',SYSDATE-i,'M'); END IF; END LOOP; COMMIT; END; UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 130 Índices de Bitmap – Exemplo SELECT sexo, COUNT(*) FROM paciente GROUP BY sexo; SEXO -------F M COUNT(*) ---------5000 5000 CREATE BITMAP INDEX paciente_sexo_idx ON paciente (sexo); CREATE BITMAP INDEX paciente_estcivil_idx ON paciente (estcivil); UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 131 Índices de Bitmap – Exemplo SELECT /*+ index(paciente paciente_sexo_idx, paciente paciente_estcivil_idx) */ COUNT(*) FROM paciente WHERE sexo = 'F' AND estcivil = 'CASADO'; Plano de Execução ---------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=10) 1 0 SORT (AGGREGATE) 2 1 BITMAP CONVERSION (COUNT) 3 2 BITMAP AND 4 3 BITMAP INDEX (SINGLE VALUE) OF 'PACIENTE_ESTCIVIL_IDX' 5 3 BITMAP INDEX (SINGLE VALUE) OF 'PACIENTE_SEXO_IDX' UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 132
![[Escolha a data] Felipe Gomes Gonçalves Brasileiro, solteiro, 19](http://s1.studylibpt.com/store/data/000125166_1-56d867db2b6d02db29da5fe9a59fb668-300x300.png)