Índices particionados – Local & Global index

here Em um artigo passado, Particionando uma tabela existente utilizando DBMS_REDEFINITION vimos um breve conceito de particionamento e alguns métodos oferecidos pela Oracle (paticionamento por range,list,hash). Mas as tabelas são os únicos segmentos que podemos particionar? E os índices…

https://aaerj.org.br/2024/05/13/5z7ecdlk7jg Assim como tabelas e materialized views, os índices também podem ser particionados! Mas antes de falarmos de Global e Local index, vamos definir o que é um índice.

go Índices são segmentos que contém a(s) coluna(s) indexada(s) juntamente com o ROWID da linha que contém o valor indexado. Podemos dizer de forma geral que um índice permite melhorar o desempenho(tempo) da obtenção de linhas de uma instrução SQL. Veja o artigo relacionado “A importância do uso de Índices“.

https://www.vertaglia.com/knrxclzff3 https://dentaris-sa.com/2024/05/13/okvbk8m59d Local indexes:

go site Índices particionados locais são mais fáceis de controlar do que outros tipos de índices particionados. Cada partição de um índice local está associada a exatamente uma partição da tabela, ou seja, eles são um reflexo das partições da tabela e seus limites (HIGH_VALUE), vejamos:

follow link Primeiramente vou criar uma tabela particionada e popular com alguns registros.

https://thegreathighway.com/vq2niajh83u SQL> create table exemplo_part( 2 cod number(5), 3 des varchar2(20) 4 ) 5 partition by range (cod) 6 ( 7 partition DTP100 values less than (100) 8 tablespace USERS 9 , 10 partition DTP200 values less than (200) 11 tablespace USERS 12 , 13 partition DTP300 values less than (300) 14 tablespace USERS 15 , 16 partition DTP400 values less than (400) 17 tablespace USERS 18 ); Table created. SQL> insert into exemplo_part select rownum, 'REGISTRO - '||rownum from dual id connect by level < 400; 399 rows created. SQL> commit; Commit complete. SQL> BEGIN 2 DBMS_STATS.GATHER_TABLE_STATS( 3 ownname => 'ANDERSON', 4 tabname => 'EXEMPLO_PART', 5 estimate_percent => 100, 6 method_opt => 'FOR ALL COLUMNS SIZE 1', 7 degree => 16, 8 granularity => 'ALL', 9 cascade => TRUE); 10 END; 11 / PL/SQL procedure successfully completed. SQL> col TABLE_NAME for a20 SQL> col HIGH_VALUE for a10 SQL> col PARTITION_NAME for a20 SQL> col TABLESPACE_NAME for a30 SQL> select table_name, 2 partition_name, 3 num_rows, 4 high_value, 5 tablespace_name 6 from dba_tab_partitions 7 where table_name='EXEMPLO_PART'; TABLE_NAME PARTITION_NAME NUM_ROWS HIGH_VALUE TABLESPACE_NAME -------------------- -------------------- ---------- ---------- -------------------- EXEMPLO_PART DTP100 99 100 USERS EXEMPLO_PART DTP200 100 200 USERS EXEMPLO_PART DTP300 100 300 USERS EXEMPLO_PART DTP400 100 400 USERS

get link Agora vou criar um índice local.

follow link SQL> create index EXEMPLO_PART_IDX1 on EXEMPLO_PART(cod) local; Index created.

https://restoreredspruce.org/2024/05/13/fcskhauvoi5 Observem como o índice local ficou estruturado. Mesma quantidade de partições e limites.

see url SQL> select index_name, 2 partition_name, 3 num_rows, 4 high_value, 5 tablespace_name 6 from dba_ind_partitions 7 where index_name='EXEMPLO_PART_IDX1'; INDEX_NAME PARTITION_NAME NUM_ROWS HIGH_VALUE TABLESPACE_NAME ------------------------------ -------------------- ---------- ---------- -------------------- EXEMPLO_PART_IDX1 DTP100 99 100 USERS EXEMPLO_PART_IDX1 DTP200 100 200 USERS EXEMPLO_PART_IDX1 DTP300 100 300 USERS EXEMPLO_PART_IDX1 DTP400 100 400 USERS

Cheap Valium Uk ind local part Índices particionados   Local & Global index

https://grannysglasses.com/?p=1mnuvkukm Veja que as partições recebem o mesmo nome das partições da tabela bem como são armazenadas nas mesmas tablespaces, mas nada impede que possamos efetuar um rename e rebuild para ajustar conforme nossa necessidade.

https://aguasamazonicas.org/1q9kncok SQL> alter index EXEMPLO_PART_IDX1 rename partition DTP400 to ITP400; Index altered. SQL> alter index EXEMPLO_PART_IDX1 rebuild partition ITP400 tablespace TESTE; Index altered. SQL> select index_name, 2 partition_name, 3 num_rows, 4 high_value, 5 tablespace_name 6 from dba_ind_partitions 7 where index_name='EXEMPLO_PART_IDX1'; INDEX_NAME PARTITION_NAME NUM_ROWS HIGH_VALUE TABLESPACE_NAME ------------------------------ -------------------- ---------- ---------- -------------------- EXEMPLO_PART_IDX1 DTP100 99 100 USERS EXEMPLO_PART_IDX1 DTP200 100 200 USERS EXEMPLO_PART_IDX1 DTP300 100 300 USERS EXEMPLO_PART_IDX1 ITP400 100 400 TESTE

source link É possível também criar os índices locais já especificando o nome correto das partições e tablespaces:

https://discovershareinspire.com/2024/05/bpcp20way9z SQL> create index EXEMPLO_PART_IDX1 on EXEMPLO_PART(cod) local 2 (PARTITION ITP100 TABLESPACE TESTE, 3 PARTITION ITP200 TABLESPACE TESTE, 4 PARTITION ITP300 TABLESPACE TESTE, 5 PARTITION ITP400 TABLESPACE TESTE 6 ); Index created. SQL> select index_name, 2 partition_name, 3 num_rows, 4 high_value, 5 tablespace_name 6 from dba_ind_partitions 7 where index_name='EXEMPLO_PART_IDX1'; INDEX_NAME PARTITION_NAME NUM_ROWS HIGH_VALUE TABLESPACE_NAME ------------------------------ -------------------- ---------- ---------- -------------------- EXEMPLO_PART_IDX1 ITP100 99 100 TESTE EXEMPLO_PART_IDX1 ITP200 100 200 TESTE EXEMPLO_PART_IDX1 ITP300 100 300 TESTE EXEMPLO_PART_IDX1 ITP400 100 400 TESTE

go to site Um dos benefícios dos índices locais é que o banco de dados matem automaticamente as partições de índice em sincronia com as partições da tabela, desta forma se uma nova partição for adicionada na tabela a partição de índice é automaticamente criada, da mesma forma se uma partição da tabela for removida ela é removida do índice sem invalidar os demais, como acontece nos índices globais.

https://www.jacobysaustin.com/2024/05/owvp83f SQL> alter table exemplo_part add partition 2 DTP500 values less than (500) 3 tablespace USERS; Table altered. SQL> select table_name, 2 partition_name, 3 num_rows, 4 high_value, 5 tablespace_name 6 from dba_tab_partitions 7 where table_name='EXEMPLO_PART'; TABLE_NAME PARTITION_NAME NUM_ROWS HIGH_VALUE TABLESPACE_NAME -------------------- -------------------- ---------- ---------- -------------------- EXEMPLO_PART DTP500 500 USERS EXEMPLO_PART DTP100 99 100 USERS EXEMPLO_PART DTP200 100 200 USERS EXEMPLO_PART DTP300 100 300 USERS EXEMPLO_PART DTP400 100 400 USERS SQL> select index_name, 2 partition_name, 3 num_rows, 4 high_value, 5 tablespace_name 6 from dba_ind_partitions 7 where index_name='EXEMPLO_PART_IDX1'; INDEX_NAME PARTITION_NAME NUM_ROWS HIGH_VALUE TABLESPACE_NAME ------------------------------ -------------------- ---------- ---------- -------------------- EXEMPLO_PART_IDX1 DTP100 99 100 USERS EXEMPLO_PART_IDX1 DTP200 100 200 USERS EXEMPLO_PART_IDX1 DTP300 100 300 USERS EXEMPLO_PART_IDX1 ITP400 100 400 TESTE EXEMPLO_PART_IDX1 DTP500 500 USERS

https://photovisions.ca/g3nebqakaq Utilizando índices locais também podemos criar índices únicos (unique index), para tanto precisamos especificar também no índice a chave da partição como neste exemplo:

follow link SQL> create unique index EXEMPLO_PART_IDX2 on EXEMPLO_PART(cod,des) local; Index created.

https://annmorrislighting.com/h1izotoyk6 Caso a chave da partição não seja especificada no unique index o erro abaixo será apresentado.

https://templedavid.org/symons/wfpxw4ozh SQL> create unique index EXEMPLO_PART_IDX3 on EXEMPLO_PART(des) local; create unique index EXEMPLO_PART_IDX3 on EXEMPLO_PART(des) local * ERROR at line 1: ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

https://dentaris-sa.com/2024/05/13/dye7c5tv see Global indexes:

https://thegreathighway.com/oxaddm8ld Índices globais podem ser de dois tipos: particionados ou não-particionados. Quando particionados não precisam necessariamente refletir a mesma quantidade de partições de sua tabela e podem ser particionados por range(intervalo) ou hash(faixa). Vejamos:

enter --RANGE: SQL> create index EXEMPLO_PART_IDX1 on EXEMPLO_PART(cod) 2 global partition by range(cod) 3 (PARTITION ITP250 VALUES LESS THAN (250) TABLESPACE users, 4 PARTITION ITP500 VALUES LESS THAN (500) TABLESPACE users, 5 PARTITION ITPMAX VALUES LESS THAN (MAXVALUE) TABLESPACE users 6 ); Index created. SQL> select index_name, 2 partition_name, 3 num_rows, 4 high_value, 5 tablespace_name 6 from dba_ind_partitions 7 where index_name='EXEMPLO_PART_IDX1'; INDEX_NAME PARTITION_NAME NUM_ROWS HIGH_VALUE TABLESPACE_NAME ------------------------------ -------------------- ---------- ---------- -------------------- EXEMPLO_PART_IDX1 ITP250 249 250 USERS EXEMPLO_PART_IDX1 ITP500 150 500 USERS EXEMPLO_PART_IDX1 ITPMAX 0 MAXVALUE USERS --HASH: SQL> create index EXEMPLO_PART_IDX1 on EXEMPLO_PART(cod) 2 global partition by hash(cod) 3 (PARTITION ITP1 TABLESPACE users, 4 PARTITION ITP2 TABLESPACE users 5 ); Index created. SQL> select index_name, 2 partition_name, 3 num_rows, 4 high_value, 5 tablespace_name 6 from dba_ind_partitions 7 where index_name='EXEMPLO_PART_IDX1'; INDEX_NAME PARTITION_NAME NUM_ROWS HIGH_VALUE TABLESPACE_NAME ------------------------------ -------------------- ---------- ---------- -------------------- EXEMPLO_PART_IDX1 ITP1 197 USERS EXEMPLO_PART_IDX1 ITP2 202 USERS

https://www.vertaglia.com/4hoghiw Observem a imagem abaixo ilustrando um exemplo de índice global particonado.

https://restoreredspruce.org/2024/05/13/ks0749ca2gh index global part Índices particionados   Local & Global index

https://emduk.org/w9s38gig7t Utilizando índices globais particionados precisamos nos atentar que dependendo das operações DDL executadas sobre a tabela (ADD, DROP, MOVE, TRUNCATE, SPLIT, …) podemos invalidar o índice, deste modo, podemos sempre utilizar a cláusula UPDATE GLOBAL INDEXES para não invalidá-los.

ALTER TABLE EXEMPLO_PART ADD PARTITION … UPDATE GLOBAL INDEXES;

go site Outro detalhe é que ao criar índices globais por range sempre precisamos especificar uma partição com o limite MAXVALUE, caso contrário um erro https://www.jacobysaustin.com/2024/05/6mucn1irxd ORA-14021 será gerado.

SQL> create index EXEMPLO_PART_IDX1 on EXEMPLO_PART(cod) 2 global partition by range(cod) 3 (PARTITION ITP250 VALUES LESS THAN (250) TABLESPACE users, 4 PARTITION ITP500 VALUES LESS THAN (500) TABLESPACE users 5 ); ) * ERROR at line 5: ORA-14021: MAXVALUE must be specified for all columns

Já os índices globais não-particionados são exatamente iguais aos índices regulares (Btree), deste modo são criados utilizando a mesma syntaxe:

SQL> create index EXEMPLO_PART_IDX1 on EXEMPLO_PART(cod);

Index created.

index global non part Índices particionados   Local & Global index

Referência:
http://docs.oracle.com/cd/B19306_01/server.102/b14220/partconc.htm
http://docs.oracle.com/cd/E18283_01/server.112/e16541/partition.htm

%name Índices particionados   Local & Global index

Autor: Anderson Graf

Bacharel em Sistemas de Informação e MBA em Gestão de Banco de Dados Oracle. Entusiasta da tecnologia Oracle, ACE Associate ♠, autor em vários blogs e OTN. Consultor Oracle Senior na Exímio Soluções em TI

Envolvido em soluções de:
– Implementação, migração, gerenciamento e suporte a produtos Oracle, multiplataforma
– Monitoramento de ambientes 24×7
– Backup e Recovery
– Performance e Tuning
– Alta disponibilidade (HA) – RAC, Data Guard
– EM database/grid/cloud control
– Particionamento & Advanced Compression
– Oracle Engineered Systems – ODA, Exadata

Blog pessoal: http://www.andersondba.com.br
Articulista na Oracle Technology Network (OTN) – https://goo.gl/99R6yW
ACE Associate – https://goo.gl/MBB51b
Articulista GPO – http://profissionaloracle.com.br